Oracle BI EE 10.1.3.3.2 – Hyperion Essbase 9.3.1 Value Based Hierachies (Recursive Dimensions) and handling them in BI EE
Posted by Venkatakrishnan J on April 14, 2008
As you would probably know, Hyperion Essbase is one of those Multi-Dimensional data sources which can handle a different types of hierarchies. One of the common hierarchies that one might encounter is the value based hierarchy (in Essbase’s terms it is called as a recursive dimension). A value-based hierarchy is a hierarchy where the member levels are determined on the fly based on the parent-child relationship between 2 columns. For example, if you take the EMP table of the SCOTT schema, you would notice 2 columns EMPNO and MGR where MGR basically would have a reference to EMPNO. In this case, both the manager and employee details are stored in the same table. Manager A for example, himself being an employee, will be reporting to his Manager say B. So, this self-reference can be used to obtain a hierarchy which is generally known as a value based hierarchy. Lets try creating a simple value based hierarchy in Essbase and see how BI EE handles this hierarchy. We shall be creating 2 simple tables to test out the connectivity. One table would have the parent-child relationship and the other would be a simple fact table.
create table ParentChild(Parent Varchar2(2), Child Varchar2(2)); insert into parentchild values ('B', 'D'); insert into parentchild values ('B', 'E'); insert into parentchild values ('A', 'B'); insert into parentchild values ('A', 'C'); commit; create table parentchild_Fact( Child Varchar2(2), SimpleMetric Number); insert into parentchild_fact values('D', 100); insert into parentchild_fact values('E', 100); insert into parentchild_fact values('C', 200); commit;
So, basically the above parentchild table would be giving an hierarchy as shown below
Now, open up integration services and pull in the fact table to create a simple Account Dimension. Also, pull the parentchild table as a dimension in the OLAP Model. Click on the dimension and go to Edit – Properties – Table and click on Physical Joins.
Add a join between the Parent and Child columns and make it recursive.
Once this is done, save the OLAP model and then start creating a MetaOutline. In the metaoutline, just include the Accounts Dimension. Also, include the ParentChild Dimension. While creating a hierarchy for this dimension just include the PARENT column.
Now, build this cube and import this cube into BI EE. After the import into BI EE, you would notice that BI EE automatically would have columns assigned to each level (equivalent to the depth of the hierarchy). Now change the hierarchy type to “Unbalanced” and then start creating a report after creating the BM and Presentation Layers.
But lets check what would happen if we go and delete 2 rows (no third level) or 2 childs D and E from the dimension and rebuild the cube.
delete parentchild where child = 'D';
delete parentchild where child = 'E';
We would not get any data for the 4 th row. So, whenever having value based hierarchies in Essbase and if you are reporting on that using BI EE always ensure that the depth of the hierarchies do not change. If they do, then you would have to import the cube metadata once again into the repository. But still the above feature is very good considering the fact that BI EE does not support value based hierarchies for relational sources.