Oracle BI EE 10.1.3.4.1 – Multi Hierarchy reporting – Shared Parents and Shared Childs

In the last blog entry, i had shown an approach using fragmentation to model multiple hierarchies having common and uncommon parents. Though it can be used in most cases, it has certain drawbacks atleast as of the 10.1.3.4.1 release. One of the main drawbacks is the fact that, once you use it for the date dimension, the date dimension can no more be used for AGO and ToDate functions. The main reason for that is the fact that BI EE does not support the use of AGO and ToDate on horizontally fragmented (a logical table with multiple sources) logical tables. If you try to use that you would get an error shown below

In most cases, especially for the Time dimension, you would want to use the Ago and ToDate functions and hence we would be in a position wherein we have an approach to model multiple hierarchies with shared parents but unfortunately unable to use them due to the above drawback. In order to overcome this for the time dimension, lets look at another approach using Session variables and the Index Col function. We shall use the same use case that we saw in the last blog entry. The idea is to use a common column for the level of both the hierarchies. This common column would vary based on a session variable. And of course, this session variable would be set from the dashboards/answers.

So, as a first step lets create a couple of common columns, one for the ID and the other for the actual drill name.

The Level2 ID and Level2 Name are the columns that we would be using for our Level2. Now create a session variable which would have the values of 0 or 1 ( 0 – Month Hierarchy and 1 – Week Hierarchy). So, if the session variable points to 0, then it would be using the Month Hierarchy drill path and if it is 1 then it would be using the Week Hierarchy drill path. Ensure that this variable can be set by any user.

Now, for the 2 new columns that we created use the formula(change the ID to Name depending on the column) as shown below.

INDEXCOL( VALUEOF(NQ_SESSION."Hierarchy Selector"),
"SH Choose".TIMES.CALENDAR_MONTH_ID,
"SH Choose".TIMES.CALENDAR_QUARTER_ID)

Basically what we are doing is, we are using the INDEXCOL function to dynamically determine our 2nd level column based on a session variable. Now, setup the hierarchy as shown below

For every report that you create (those that require the Hierarchy selector), ensure that the session variable is set through a presentation variable as shown below.

Also, create a dashboard prompt which would basically set the presentation variable (Hierarchy in my case). Now one can dynamically switch the hierarchies and at the same time can use the AGO and ToDate functions as well.

The advantage of these techniques is the fact that they are inter-operable depending on the requirement. Also, the same approach can be used for switching Essbase and relational sources as well. I would try to cover some use cases later.