Combining Data from Multiple data Providers 2

[himanshugupta]

Hi All:
I am facing a peculiar problem with BO. I have data coming from 5 different data providers (all are worksheets...), some of the common dimensions are emp# , department, business line etc.

After getting the data I have linked the common dimensions. However,if I create a simple single column table say using department from Q1 then it only shows departments coming from Q1 and not from all the queries... So, if I now want to count employees from all queries by department I am not able to do so...

Is there some way in Business Objects to specify that whenever I use common dimensions it should include data from all queries and not just the ones used in the table? I tried using 'forall', 'in' etc. but even that doesn't work...

Thanks in advance!!!
Himanshu.

 

[blom0344]

Hello Himanshu,

Linking dimensions from different data-providers basically should result in the effect of full outer joins between the sources. However this will only function if you drag in objects from the multiple sources into a table. If you only use objects from one source , you are not telling BO that you want something from another source (same applies to SQL, if you use objects from one table, it won't use anything more than that table)

What you actually want is a union-query (as described in the pdf manual in "building a combined query".)
That's the way to even force incompatible objects into a single column in your report.
However, this works only with universes as data-provider.
So a possible approach is to include all 5 department-columns into the table from all data-providers and use "hide" on the 4 redundant columns to keep one column with departments

 

[chogben]

You could try creating a vba data provider which queries the data on the report, merges it appropriately and then throws it back out... I've found this to be a fairly complex solution (from BO point of view) but is very flexible.