Business Intelligence – Oracle

Archive for June 2nd, 2008

Hyperion Essbase 9.3.1 – Loading Valued Based Hierarchies (Parent-Child) using Admin Services and Rules Files

Posted by Venkatakrishnan J on June 2, 2008

For the past couple of months i have been primarily working with Hyperion Essbase and the BI EE connector to Essbase. There are a lots of things that i want to write about which i would be doing in the coming week or so. As i had blogged here, there are many ways of loading data into Hyperion Essbase. They are

1.   Essbase Administration Services
2.   Essbase Integration Services
3.   Hyperion Data Integration Management
4.   Oracle Data Integrator

The most commonly/widely used ones are the first 2. I had already shown you here on how to go about loading Value based hierarchies using EIS(Integration Services). Lets see how to go about doing the same using Administration Services(EAS). EAS uses the concept of Rules Files to load data into an Essbase database. In our example, lets load the simple EMP – MGR parent child hierarchy from the EMP table of the SCOTT schema into a sample Essbase database. So as a first step, log into the Essbase database using EAS. And then open the outline.

    

Now open the Data Prep Editor from File -> Editors menu. You should be getting something like the screenshot below

    

Basically data prep editor is the editor that we would be using to create a rules file. Just like EIS, this editor also allows data load directly from a relational database. In our case, go to File -> Open SQL. Ensure that you are assigning the database to the SQL. Once that is done, enter the SQL given below.

SELECT
NVL(to_CHAR(MGR), 'EMP' ) MGR,
EMPNO,
ENAME, '+' PROPERTY
FROM
EMP

    

    

In the above sql, we are doing an NVL in order to make Essbase understand that we are creating child members for EMP dimension. So, basically all our members would roll up to EMP (which would be same as the EMP dimension). If you closely look at the second screenshot the root is actually in the middle of the result set. We shall see what impact this has on the final dimension later. Once this is imported, go to View and set this rules file as a file with Dimension build fields.

    

Then assign an outline to the rules file.

    

Then go to the Dimension Build Settings and set the 2 properties as shown below. The Allow Property changes will allow us to set the aggregation (‘+’ in our sql above) of the members and the Parent Child references will automatically create the hierarchy based on the parent child relationship in the sql.

    

Then go to the field properties and assign the dimension, Parent, child, alias and property properties for the each of the fields.

    

    

Now validate the rules file. Then save it. Lets load the dimension using this rules file.

    

    

Now if you see, the data is not loaded properly. 7902 and 7698 should actually come under KING (7839). But that has not happened here. So, what this means is that Essbase rules file expects all the parents to be loaded first and then the children.  But in the case above, since we did not have a proper ordering of the data, the 2 children 7902 and 7698 got loaded before their parent KING (7839). Now, in order to rectify this go the SQL of the rules file and change it to the one shown below

SELECT
NVL(to_char(MGR), 'EMP' ) MGR,
EMPNO,
ENAME,
'+' PROPERTY
FROM
EMP
CONNECT BY PRIOR EMPNO=MGR
START WITH MGR IS NULL

    

Now the above will give us the data in proper format (parents will get loaded first since the connect by clause internally starts with the root).

    

Advertisements

Posted in All Posts, Hyperion Essbase | 9 Comments »