Business Intelligence – Oracle

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.




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

NVL(to_char(MGR), 'EMP' ) MGR,


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).


9 Responses to “Hyperion Essbase 9.3.1 – Loading Valued Based Hierarchies (Parent-Child) using Admin Services and Rules Files”

  1. Sudhir said

    hi Venkatakrishnan,
    are the RDBMS details in tsnnames.ora named SH. i believe i enter RDBMS username and password when Retrieve/OK is clicked. I am connecting to Oracle 10g, i get error ‘Unable to connect with SQL Database Server’. in above example the essbase server name is, is the dbms server name same.

  2. Sunny said

    Hey ,
    nice post , but I have the same question , not sure how data loading happens for SQL data sources.

  3. saurabh soni said

    This error comes up no matter whether it is a tns error or sql format error. The connection name is the one used to configure this source (i.e. tns).
    Usually the retrieving sql is pasted as it is in the select area, while the sql needs to be broken down. lets say we have a sql like
    Select sales from table A where salesperson = ‘A’;
    then ‘sales’ needs to be in the “Select” windows , ‘table A’ in the “From” window & the “salesperson=’A'” in the where window.

    Hope this helps…

  4. russel said

    hi venkat,

    i am new to the hyperion/essbase would be great if i could get some info on the problems which are common to essbase production support..


  5. venkateswararao said

    excellent what u r given slides

  6. Chet said

    Hi Venkat, Is this feature available in 9.3.0? If so how do I get the Open SQL Data Source dialog box to open up?

  7. praveen said

    i’m praveen
    when i tried to open SQL in Essbase there is an error message shows ” Data Base outline is Empty and
    There are no data sources defined. Please create one to continue”

    Please tell me the solution ASAP to my ID


  8. venugopal.S said

    Hi venkat,
    this is venugopal, u r blog is very good.
    iam woring in hyperion essbase,i created outline and loaded data also, but when i was retreiving data from excel sheet, its showing error is your rows (65536)are exceeded.How to increase the row size?

    Plz help me this issue, ASAP. sene me the solution to my


  9. Venkatesh said

    your website is really very good..


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: