Oracle BI EE 10.1.3.4.1 – Multi Hierarchy reporting – Shared Parents and Shared Children - Fragmentation

One of the drawbacks of BI EE as of the 10.1.3.4.1 release, is the fact that one cannot do multi hierarchy reporting wherein parents and the Children are the same across hierarchies. For example, lets take a use case below

If you look at the above example, we basically have 2 hierarchies. One is Year rolling down to Quarters and Quarters rolling down to Days. The other is Year rolling down to Month and Months rolling down to Days. So in effect we have 2 hierarchies having the same root members as well as the child members. What would be ideal is to have something like a hierarchy selector in built into BI EE (like what you get when you setup navigate drills). But unfortunately, this is not currently available/supported for designing within the repository.

The question is how do we approach such a requirement within BI EE. One approach that we would be seeing today is to build the hierarchy selector ourselves and let BI Server determine the drill path using the selected value in the prompts. This is a pretty common approach but is worth mentioning here as it opens up a lot of other fragmentation use cases that i would be covering later. To demonstrate this approach i would be using the SH schema. The main advantage of this approach is the fact that, you do not even have to have same sharing columns across hierarchies.

To start with lets look at the Date dimension that we have.

As a first step lets add a logical column that would basically act as our hierarchy selector prompt. This column would have 2 hardcoded values (the Hierarchy names) i.e. Quarter Hierarchy and Month Hierarchy. For now, in the TIMES logical table source column mapping, remove the mapping for Calendar Quarter related columns. And hardcode the Hierarchy Column with “Month Hierarchy”.

Also add a fragmentation filter to the logical table source on the Month Hierarchy as shown below.

Now duplicate the above logical table source. This new logical table source would have the “Quarter Hierarchy” hardcoded. Also it would have a mapping for the quarter hierarchy related columns. Ensure that there is a fragmentation set for the Quarter Hierarchy as well.

Now, add 2 more logical columns, which would basically be used as a Level 2 column name and id(it would have a mapping for both Quarter as well as Month). This mapping would have to be done for the corresponding columns in the both the hierarchies.

Now add a hierarchy to the above Time dimension as shown below

Now, you should be able to choose the hierarchy and drills would be available according to the chosen hierarchy.