1

this is bugging me since yesterday and I cannot work it out, hope you can help:

I have a report in Business Objects (I use WEBI interface) which has data being pulled from 3 different universes: universes 1 and 2 have sold qty information per customer number, universe 3 query is just a list of customer numbers and their relevant region. All 3 universes have same customer numbers therefore I merged on that field.

The first task which I have been able to achieve is: I created a table that checks if there is sold qty for a particular customer in universe 1 then this qty is shown for this customer, if not, show qty for this customer from universe 2.

What I am struggling with is: I need to add the customer region field from the third universe into the same report looking up the customer number and returning the customer region field, however the problem is - there ARE some customer numbers that are missing from the third universe and on adding that field I lose this sold qty entries for this customer from the table completely. However I would like to still see them in a report with "Null" values for the region.

I have searched for a similar solution in different sources across internet, however I couldn't find anything similar(having 3 universes in same report) as all solutions offered cover only 2 universes, which I could have replicated myself if needed.

Is this achievable?

Sandra Rossi
  • 11,934
  • 5
  • 22
  • 48
Raugmor
  • 566
  • 5
  • 12
  • Which version of BO are you using? BI4? Also, could you please list the objects you are trying to display together and their types (measure, dimension or detail). – emilys Nov 23 '15 at 15:07
  • @emilys "about" tab shows 14.0.4, Googling that shows support pack 4. regarding objects: both sale qty are measures, Customer numbers are dimensions, however i have merged them and am using custom Detail type variable based on the merged dimension to give me the all customer numbers regardless of either it appears only in one of the two universes. – Raugmor Nov 23 '15 at 15:55
  • So i am trying to add to the existing 2 columns (Customer number, Sold Qty) a new column from the 3rd universe which i want to return customer group for(also dimension type) without losing entries from either of the first two queries where customer does not exist in 3rd universe. – Raugmor Nov 23 '15 at 15:55

1 Answers1

3

Merging dimensions functions as a "join" between the data providers involved. Whether it acts as an "outer join" (roughly speaking) or an "inner join" within a block depends on the types of objects you are combining.

Imagine you have two data providers, DP1 and DP2. They can be from different universes or from the same; what matters is that there is a common dimension which can be merged between them.

DP1 selects dimension "Customer Number", along with other objects. DP2 selects dimension "Customer Number", dimension "Customer Region", and measure "Quantity Sold". Dimension "Customer Number" is the common dimension in the two data providers and will be merged, but DP2 does not contain all of the values which are present in DP1 (in the interest of simplifying the example, let's say DP1 does contain all of the values in DP2).

Including the merged dimension "Customer Number" and "Quantity Sold" in the same block will return all of the customer numbers in both data providers, with blank values for "Quantity Sold" for missing values in DP2. This is the equivalent of an outer join, and whether it is a left, right, or full outer join depends on other options, which are well described here:

http://www.dagira.com/2010/06/19/what-does-extend-merged-dimensions-really-do/

Including the merged dimension "Customer Number" and "Customer Region" in the same block will restrict customer numbers to only those found in DP2. This is the equivalent of an inner join, and can present other limitations like incompatible objects. You may need a detail object in your example, if you can adapt the universe; some more useful explanations are here:

https://michaelwelter.wordpress.com/2011/04/18/tips-for-merging-dimensions/

Therefore I think this problem is not related to having three universes in the same document, but rather the types of objects you are selecting from each.

emilys
  • 189
  • 1
  • 13
  • thank you for the reply, it didn't answer my question exactly, however reading through the second link gave me an idea to try a slightly different approach which solved the problem. Again, thank you for your time. – Raugmor Nov 26 '15 at 16:28