Oracle BI EE 10.1.3.4.1 – Modeling Parent-Child Hierarchies – Using Federation
One common issue that gets highlighted when someone uses BI EE is its inability to leverage Parent Child Hierarchies out of the box. 11g release scheduled sometime later next year is supposed to address this seamlessly. Having said that, being consultants ourselves most of the times we find ourselves in situations wherein we would have to use the parent child hierarchies. I would be covering a technique today that can basically address hierarchical reporting on parent-child hierarchies, in the current release itself.
It is possible to mimic value based hierarchical reporting in BI EE in a number of ways. I will provide 3 high level options here. Though there can be other possibilities they would only be slight variations of the 3 listed below
-
The first option is to flatten the hierarchies and convert the parent-child into a level based hierarchy. This is the approach that Oracle’s BI Applications product called CPM Analytics uses. This is a straight forward approach wherein you have a pl/sql or a sql program which basically converts the hierarchy into level based wherein the number of the levels is pre-determined.
-
The second option is to mimic everything using a combination of Answers UI navigation and Repository based drills.
-
The third option is to provide out of the box drills from the repository using a combination of BI Server Federation and Oracle database hierarchical operators. Here there is no need for setting up custom navigation in Answers.
I would be covering the 3rd option which requires fair bit of understanding of Federation, how BI Server uses the logical table sources and the issues inherent with a parent-child hierarchy. I would try to address them in a simplified manner here. Lets take the Global Schema. I will be using a parent child hierarchy table for the Products dimension (shown below)
As you see, it is a simple parent child hierarchical table. The idea behind the technique is to have one Logical table source for every parent-child level. This can be best illustrated by the diagram below
So effectively we would have n-1 logical table sources for n levels. For drilling from Level 1 to Level 2, we would use the Parent to Child Relation. From Level 2 to Level 3, we make the BI Server to switch to another logical table source wherein Level 2 would now be the parent and Level 3 would be the child. But when we drill, the values of Level 1 would have to be propagated across all the table sources. This is where the crux of the technique lies. So the first step in our technique is to alter the above parent child hierarchy table to contain the Level information as well as Level-0 product information i.e every record in the parent-child table should have the Level Number and should have all the Level-0 products that roll into the parent.
In Oracle 10g, this can be done easily through the sql below. We are doing this to ensure that we get the rolled up numbers for every parent. There can be multiple variations of the script below. But all we need is a level number and all the level-0 children of every record in the parent-child table. All the sub-queries below are not actually necessary.
SELECT LEVEL_1, LEVEL_2, PRODUCT_NAME, PRODUCT_ID, LEVEL_NUM FROM ( SELECT LEVEL_1, CASE WHEN LEVEL_1 = LEVEL_2 THEN LEVEL_3 ELSE LEVEL_2 END LEVEL_2, LEVEL_3 PRODUCT_NAME, LEVEL_3_ID PRODUCT_ID FROM (SELECT LEVEL_1, CASE WHEN INSTR(PATH,'|',1,2) = 0 THEN LEVEL_1 WHEN INSTR(PATH,'|',1,3) = 0 THEN SUBSTR(PATH,INSTR(PATH,'|',1,2) + 1,LENGTH(PATH)) ELSE SUBSTR(PATH,INSTR(PATH,'|',1,2)+1,INSTR(PATH,'|',1,3)-INSTR(PATH,'|',1,2)-1) END LEVEL_2, LEVEL_3 LEVEL_0, PATH LEVEL_1_ID, LEVEL_3_ID, LEVEL_3, LEAF FROM ( SELECT SYS_CONNECT_BY_PATH(PARENT_DSC,'|') PATH, CONNECT_BY_ROOT PARENT_ID LEVEL_1_ID, PRODUCT_ID LEVEL_3_ID, CONNECT_BY_ROOT PARENT_DSC LEVEL_1, PRODUCT_DSC LEVEL_3, CONNECT_BY_ISLEAF LEAF, LEVEL FROM (SELECT TO_NUMBER(A.PRODUCT_ID) PRODUCT_ID, TO_NUMBER(A.PARENT_ID) PARENT_ID, A.PRODUCT_DSC PRODUCT_DSC, B.PRODUCT_DSC PARENT_DSC FROM PRODUCT_CHILD_PARENT A, PRODUCT_CHILD_PARENT B WHERE B.PRODUCT_ID(+) = A.PARENT_ID) CONNECT BY PRIOR PRODUCT_ID = PARENT_ID) A WHERE LEAF = 1)) A, (SELECT DISTINCT PARENT_DSC, LEVEL LEVEL_NUM FROM (SELECT TO_NUMBER(A.PRODUCT_ID) PRODUCT_ID, TO_NUMBER(A.PARENT_ID) PARENT_ID, A.PRODUCT_DSC PRODUCT_DSC, B.PRODUCT_DSC PARENT_DSC FROM PRODUCT_CHILD_PARENT A, PRODUCT_CHILD_PARENT B WHERE B.PRODUCT_ID(+) = A.PARENT_ID) CONNECT BY PRIOR PRODUCT_ID = PARENT_ID START WITH PARENT_DSC = 'Total Product') B WHERE A.LEVEL_1 = B.PARENT_DSC
The idea is to create a table which would hold the output of the above script. Now import this table into the physical layer. Since Product id would no more be unique, make a complex join with the product_id in this table and the fact.
All this gives us is the information required for the first logical table source. Remember when we jump from one logical table source to another we would still be having the filter for Level 1, Level 2 etc. If we leave those columns to be mapped to Parent or child columns, the drills would not work. So, for each additional logical table source we would need an additional Select view within the physical layer. This select view will be a Cartesian product of the filterable columns. Do not worry about the performance much as the Cartesian product would still be producing only valid combinations as we would be filtering them while drilling(but this filter in effect actually does not filter anything from a reporting standpoint). This Cartesian product select view is only for disabling the filters while making the jump. So, for the 2nd logical table source, create a select view in the physical layer using the sql below.
SELECT B.LEVEL_1 LEVEL_1, A.LEVEL_1 LEVEL_2, A.LEVEL_2 LEVEL_3, A.PRODUCT_ID PRODUCT_ID, A.PRODUCT_NAME PRODUCT_NAME, A.LEVEL_NUM LEVEL_NUM FROM (SELECT DISTINCT LEVEL_1 FROM PRODUCT_PARENT_CHILD_TBL1) B, PRODUCT_PARENT_CHILD_TBL1 A
In the same way for the 3rd logical table source we would now need a Cartesian product of 2 tables, one with Level1, Level 2 columns and the other with the source of the first logical table source.
SELECT B.LEVEL_1 LEVEL_1, B.LEVEL_2 LEVEL_2, A.LEVEL_1 LEVEL_3, A.LEVEL_2 LEVEL_4, A.PRODUCT_ID PRODUCT_ID, A.PRODUCT_NAME PRODUCT_NAME, A.LEVEL_NUM LEVEL_NUM FROM PRODUCT_PARENT_CHILD_TBL1 A, (SELECT DISTINCT LEVEL_1, LEVEL_2 FROM PRODUCT_PARENT_CHILD_TBL1) B
Now make a physical complex join between these 2 aliases with the fact table. In the example above, i have stopped with 4 levels. Depending on your requirement and the depth of the hierarchy, the same procedure above can be repeated for more levels.
Once we have the physical sources ready, the next step is to design the Business Model Layer. As discussed above, we would have 3 logical table sources contributing to the product dimension. And each source would have a mapping as shown below
The ordering of the Logical table sources is very important. Generally whenever there are 2 or more dimensional logical table sources that contribute to the same set of columns, the first logical table source would chosen for querying(not always true – It is dependent on the measure as well). So, when we create a report containing LEVEL1 and a fact measure we would be hitting the parent child table (And not the Cartesian Product views). And the other important aspect to this is the filtering on Level Number. When we move from one logical table source to another, as we are negating the effect of the drill filter using Cartesian product, we would have to explicitly apply the level filters for each logical table source as shown below(For level 1 lts the filter would be equated for Number 1, level 2 to number 2 and so on.
Once this is done, just create the hierarchy as shown below.
This will ensure that our BMM is parent-child hierarchy aware for the product dimension. The drills would be seamless for an end user
There can be many more improvements to above technique. For example, instead of the Cartesian Product select view we could just have another table with repeating Level 1/ Level 2 values. But the idea would remain the same.