Multiple Fact Tables in BusinessObjects Universe - Modelling Data and Business Layer

Question

This question is about data modeling in BusinessObjects 4.0(1).

Start: Requirement is to make a BO universe. User wants to make queries on top of 5 fact tables becasuse the business answers he is looking for are in the same context (in the context of the 5 fact tables). Another important characteristic is that these 5 fact tables share a lot of common dimensions.

Approach I took: I decided to make ONE universe, which will contain ALL 5 fact tables. Then I ran into following dilema - since fact tables "share" same dimensions, should I use contextes to avoid loops, or should I use aliases? I decided to use aliases. As a result I came up with the following data model. With this data model I know for sure that there are no loops (as it maybe could be the case with contextes).

The next difficulty I ran on was during business layer creation. Since I have 5 fact tables my first idea was to create 5 different folders for each fact table and then put the corresponding dimensions into the same folder (as seen on the picture).

Furthermore, when I tried to query the universe, all was functioning ok when I used ONLY ONE fact table and its corresponding dimensions. However, when I tried to combine objects that belong to different fact tables I got an error message that says something like "because of Cartesian product that your query generates the action was stopped".

Questions:

• Is the approach I took (creating only ONE universe with 5 fact tables) the right approach?
• In dilemma aliases vs contextes, did I took the right approach, when deciding to go with the aliases?
• When I created business layer, and decided to make one separate folder for each fact table and its corresponding dimensions, was that the right modelling approach?
• By using which approach can I combine objects that belong to different fact tables in the SAME report?
• Are you suggesting some other better approach? If yes, can you please elaborate on that? Thanks.
• And finally, can anyone suggest some articles or better tutorials that explain data modeling when one works with multiple fact tables in one universe?

Answer 1

create a universe with 5 fact tables along with dimension tables(with required dimensional information). Make a context with one fact table and required dimensional information (follow the same process for all 5 contexts). So basically you will have 5 contexts.

5 folders for fact tables 1 folder for common dimensions used across all the 5 queries.

Now to have the data from all the 5 fact tables in a single report ask the user to do merge dimension.

This is the only solution. If users does not know how to do merge dimension teach them once

Answer 2

There is no exact formula when to choose for aliases or contexts. Both have their pro and cons.

In your situation though, using aliases leads to a large number of duplicate objects in your BL (e.g. the time dimension). This complicates the universe for your end user, but also for further maintenance, as any changes to these shared dimensions results in potentially having to repeat the same modification several times.

I would use aliases only when

• the number of resulting objects (dimension objects in your BL) is small
• the aliased table has a different meaning than the original table (e.g. table 'person' which can be a supplier or a customer) and you need to include both in the same query

I would not alias the time dimension as this is a commonly shared dimension, and the meaning is the same no matter what table it's linked to.

If you would use contexts, you would actually be able to select from different fact tables (providing you have enabled the option to allow selection from multiple contexts in a query) with the inclusion of common dimensions. Also, this way you wouldn't force the end user to merge different queries together in the reports.

For more information, have a look at the Information Design Tool User Guide. Section 10.13 and 10.14 deal with aliases and contexts respectively.