Oracle BI EE 11g – Vertical Fragmentation on Parent-Child Hierarchies – Drills from Essbase to Oracle Database
If you had looked at our presentation last year in Open World (and also a couple of blog posts prior to that), we had shown how to go about implementing Vertical Fragmentation in BI EE 10g. There were 2 main drawbacks in using Vertical Fragmentation of BI EE 10g. They are
1. The Fragmentation was supported only on Level Based Hierarchies 2. It required that the Essbase hierarchies are not Ragged.
In most cases where Essbase is used, Ragged hierarchies are generally the norm as Essbase does not pose any restrictions. With BI EE 11g, we now have a Value Based (or Parent-Child) hierarchy support for both BI EE and Essbase. Mark had blogged about it here. Also, i had shown an example of how we can alter the aggregation for a parent-child relational hierarchy here. In today's blog post, we shall see how to go about implementing Vertical Fragmentation using Parent-Child Hierarchies in BI EE 11g.
I will be using a simple example that i used in my previous blog post. Following is the hierarchical structure that we have in Essbase.
As you see its a very simple Ragged hierarchy. Now we have a relational source that provides the base level data for node D, E and C i.e we need the ability to drill from the nodes D, E and C to get the underlying Relational hierarchical structure and all without losing the Hierarchical Columns drill capability
One important point to note, in Parent-Child Hierarchies, it is not possible to add a new level from the BMM layer. So modeling this requires understanding how BI EE will fire separate queries and how BI EE combines conforming dimension values. Lets assume we have 2 data sources, each having a common conforming dimension but a different measure (a form of horizontal fragmentation) as shown below
When we create a report containing the conforming dimension & both the facts, we would get something as shown below.
That is, the resultant data will be a full outer join of the confirming dimension values. This is the technique that we will be using to achieve the vertical fragmentation.
We start of by importing our Essbase Cube into the physical layer and then setting the hierarchy as a value based hierarchy.
Then we model our BMM layer by dragging the Essbase database over to the BMM layer. We will have 2 columns in the logical dimension (Parent & Child). The parent will be mapped to the Parent Key of the Essbase Dimension. The Child will be mapped to the Member of the Essbase dimension
Ensure that the measure(or the data column if measures are not flattened) used for Essbase has SUM as the aggregation in the BMM layer.
To enable the drills from Essbase for a parent child hierarchy, we need to basically ensure that we have the following setup in our Relational data source.
1. We need to have the same hierarchical structure of Essbase (in a parent-child format) stored in a relational table(right from the root node). 2. The root node of the hierarchy(in the relational table) will be the same as the dimension name in Essbase.
The above 2 are required in order to get a consistent drill behavior. Any deviation from the above might not result in the right drill behavior. In our case, following is the relational parent child hierarchy that we have.
As you see, SkipRagged is the name of the dimension that we are using in Essbase. In addition you can notice, this parent-child table has the relational drill nodes I, J, K & L. Now include this relational table as Logical Table source to the Logical Dimension. Create the Parent-Child Closure table so that we get the right relational drill behavior.
Create one more Logical Fact table (or a new measure in the existing fact table) for the relational measure. Ensure that all the level assignments are correctly done for the Logical Table Sources.
Now lets create a report containing the Parent-Child hierarchy, Essbase Fact and the Relational Fact
As you see, Essbase has produced NULL values for non-existent members (I, J, K & L). But the relational source has aggregated the data even till the upper levels. Ideally we need to make the upper level relational values to NULL and then sum up the Essbase & Relational Measures to get the correct drills. To do that we define another calculated column in the logical fact table as shown below
The formula basically gives a value of 0 if we are at the parent level and 1 if we are the at the leaf level. This uses the hierarchical functions that i had shown before here.
CASE WHEN ISLEAF ( "ParentChild - Vertical Fragmentation". "DimensionDim" ) THEN 1 ELSE 0 END
Then define another calculated column which will add the relational & essbase values together. The relational measure will be multiplied by the above calculated column to get the right numbers.
Now if we create the report containing this new calculated measure & the hierarchical column, we have the ability to drill from an Essbase source to a relational source, all within a parent-child hierarchy.
Few important points to note here. If you have multiple levels of drill (within the relational after Essbase), then you might not get the + sign once you have drilled on them. Also, even if there is a single attribute name mismatch, you will see them appearing as separate values. So, ensure that the attribute values exactly match before using this.
We can use the Fragmentation capability of BI Server as well to achieve the vertical fragmentation. I will cover that in a future blog post.