Oracle BI EE 10.1.3.4.1 & Essbase Connectivity – Repository Design – Enabling drills on Evaluate Columns

One of the common questions that generally come up in the BI EE and Essbase integration is, how do we enable drills on Evaluate based columns. I have covered in detail here with regard to the usage of Evaluate based MDX functions. As of the current release in BI EE, most of the reporting requirements on Essbase sources cannot be met out of the box without using Evaluate. Though Evaluate functions can be very useful to solve many reporting requirements, the major flipside in using them though is the fact that one cannot use repository based drills out of the box. Today we shall look at a technique that would enable drills on such Evaluate based columns.

For illustration purposes, i would use the Demo->Basic cube. The idea behind this technique is in making the BI Server to jump across multiple logical table sources while doing the drills. For example, lets take a look at the report below.

As you see, its a report containing all the members of the Markets dimension, Generation 2 of Year dimension and the corresponding Sales. The All Markets column has been obtained using the Evaluate function below

EVALUATE('%1.dimension.members',
"localhost"."Demo".""."Basic"."Gen1,Market")

The All Markets column contains all the members from all the generations of the Markets Dimension. How do we make sure that clicking on a member at any level automatically takes us to the lowermost level without using Navigation and of course without getting any errors? In order to do this, the first step is to create a hierarchy as shown below

Essentially what we are doing is, whenever we click on any member in the All Markets column, it will drill down to the Level-0 column. Ensure that All Markets column is part of the drill key. Now, go to the logical table source of the Markets dimension and remove all the mappings for all other columns except the All Markets column.

The next step is in determining the number of generations that contribute to the new column. For example, you might have an evaluate function which would produce a subset of Generation 2 alone. In that case you would need only one extra logical table source. In our case above, the All Members column produces members from all the generations. So, we would need 3 additional logical table sources as shown below.

Each logical table source would have a corresponding mapping to the All Members column. This is shown below.

Ensure that each logical table source has the corresponding level assigned to ensure that BI Server does that jump. So, whenever anyone clicks on any member in the All Markets column, the BI Server would do an in-memory union on the remaining 2 logical sources. But since one member can be found in only one source, there would not be any duplicates in your report. When you click on say Market Member, it would take you to the Gen3, Market column with proper filters applied on the corresponding All Markets mapping column in that logical table source. From then on it would be normal drills for the end user. For example, if we click on the East member (which is actually in Gen2), we would get the below

In the same way one can drill on any member at any level. An example screenshot below after drilling on the Market member(Gen1).

This technique can be used for any kind of member returning evaluate function. We shall see more use cases in the future blog entries.