Oracle BI EE 10.1.3.4 & Essbase Connectivity – Conforming Dimensions and Federation – Drill through to a Relational Database
Posted by Venkatakrishnan J on January 21, 2009
I was helping out a customer this week to setup drill through from an Essbase BI EE report to a Relational Database. Though it is straightforward to set this up, i thought it is relevant to blog about it as there are multiple variations of the integration between an Essbase data source and a relational data source. There are basically 2 types of integration which are possible as of now.
1. Creating a report containing a relational measure (for example forecast) and an Essbase measure (for example sales) through a conforming dimension
2. Creating a report wherein drills are enabled(on a conforming dimension) from an Essbase data source to drill down to a relational data source
To set the above 2 integrations, there are certain pre-requisites. I would list them below
1. There should be conforming dimension between an Essbase Cube and the relational database. For example, if Essbase has Sales measure and an Oracle DB has the forecast measure, to establish integration between the 2, we would need a seperate common dimension having the same values in all the levels or at least in one of the levels.
2. The conforming dimension in Essbase should not be ragged in nature. In such cases, the drill through would not work. The main reason for this is that BI EE converts each level into a specific generation.
Lets understand this further through an example. We shall be using the Demo->Basic cube in Essbase. This cube basically stores Actual, Budget and various measures data in multiple dimensions at a Monthly level as shown below.
Assume that we have a database table containing Monthly Week level data as shown below.
Now a typical requirement is to have a report at the month level and drill down to the Week level. It is generally desirable to add a drill through from the parent Essbase report to the relational database so that analysis at a weekly level is possible. In order to achieve this, lets first start with importing the Demo->Basic cube into the repository. Also, import the database table and create an alias for this table. Our intention is to use the same table for dimension as well as the fact. The table and the alias would be joined at the week level.
In the list of pre-requisites above, i had stated that we need a conforming dimension with atleast one level of values to match exactly between the Essbase Outline and the Relational database. In our case, the conforming dimension would be the year dimension along the Month level. As a first step, we need to establish a federation at the dimension level by including the TIME_DRILL table in the sources of the year dimension. Also, change the logical key to be of the Week column instead of all the 3 generations from Essbase.
Also, create another level below the Gen3, Year level.
Ensure that the Gen3, Year and Week columns are properly mapped in the LTS of the TIME_DRILL source. Also, change the level of this source to Gen4, Year.
This will ensure the linkage between the Week and Gen3, Year columns. Include the Alias table of TIME_DRILL as a seperate Fact table in the BMM.
Create a logical join between the TIME_DRILL_FACT and the Year Dimension through the Week Column. And also set the content level of this LTS at the Gen4, Year level.
Now, create 2 reports as shown below. One report would be reporting on Gen3, Year and the Amount measure from Essbase. And the other report would be on WEEK column and Amount Sold measure from the database. The latter would be the target drill through report with the Gen3, Year column prompted. Enable the navigate option in the Gen3, Year column of the parent report to navigate to the child report. This would seamlessly drill from the Essbase report to the relational report.