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.

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

    

About these ads

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 vejanaki-lap.in.oracle.com, 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 technology..it would be great if i could get some info on the problems which are common to essbase production support..

    thnks
    russel

  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

    HI,
    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 burrimca@yahoo.co.in

    thanks

  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 Id:gopal.vgr@gmail.com

    thanks,
    venu.

  9. Venkatesh said

    your website is really very good..

    venkatsh

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 151 other followers

%d bloggers like this: