It's all about Business Intelligence

August 17, 2010

Hierarchies in OBIEE 11g Part 1

Hierarchies in OBIEE 11g – Part 1

It’s extremely exciting to see the breadth and depth of OBIEE 11g features. There are many things which were constrained till previous release, 11g fixed most of them.

Let us start with Hierarchies and see how to implement different types of hierarchies in BI 11g with step by step example. This article is specific to Value Based Hierarchies.


There are different types of hierarchy which exists in an Organization data which gives more insight for our analysis.

1. Level Based Hierarchy:

Basic type of hierarchy which has fixed number of levels. For example Time Dimension hierarchy is a good example for this one.

Year->Quarter -> Month

We have two special cases of level based.

· Ragged Hierarchy

Members of certain levels don’t have child members.

· Skip Level Hierarchy

Level itself will not be present in case of certain hierarchies. In the above diagram if we consider “direct” as a delivery mode under “wholesale” it becomes skip level hierarchy.

1. Value Based Hierarchy

This will be called as parent child hierarchy too. Typical reporting structure in an organization is a good example for this one.

In this article let us see how to create a value based hierarchy step by step.

Believe me creating value based hierarchy in 11g is like walking on ice J

Let us consider employees table which has Manager -> Employee relationship and model the same.


1. Importing the source table(Employees)

2. Creating value based hierarchy in BMM layer

a. Define parent(Manager_ID) & child(Employee_ID) columns

b. Generate parent -child hierarchy table DDL statements

c. Generate parent – child hierarchy table DML statements

d. Execute DDL & DML and create hierarchy table and insert data into it

3. Pull the BMM model into presentation layer

4. Run answers and check the output.

Make sure Employees table has below structure.

1. Importing Employees table into Physical layer

Finally update the row count and test the database connectivity

2. Create value based hierarchy in BMM layer

Drag employee table twice to the BMM (OBIEE requires star model) as shown below

Rename the columns and do the basic cleanup

Right click on the BMM model and join the Employee and Salary Information.

Now right click on Employees logical table and choose for new parent child hierarchy

Choose the member key (by default it will take the primary key) and parent column as shown in the below screenshot.

Then click ok parent- child settings. This is the place where we are going to generate the DDL & DML scripts which we can use to create and populate the hierarchy table which will be used by BI server to report parent child hierarchies.

Click on new hierarchy table (middle one – yellow arrow). If you have already generated hierarchy table then click on the select (red arrow) and choose the table.

After clicking new give the name for the DDL and DML scripts. This wizard will create the SQL scripts.

Give name for the hierarchy table

Click next and verify the scripts

After finishing you can see the details are mapped now

After finishing the wizard you can see the HierarchyTable got imported automatically.

Now go to the scripts location and run the DDL & DML scripts and commit the changes via SQL command prompt

Update the row count and make sure table got created properly

3. Pull the BMM model to presentation layer and create presentation folders.

Make sure that Hierarchy inside the Employees folder is visible.

Now we are done with the metadata definition. Save the RPD and make sure there are no consistency errors.

Start the WLS and BI services and make sure all of them are running fine.

4. Running answers and verifying the hierarchy

Before logging into answers lets enable the logging so that we can check the physical SQL’s that are getting generated while performing drill down’s through hierarchy.

Open the RPD in online mode and navigate to Manage->Identity and set the log level for Administrator.

Check in the changes and close the RPD.

Login to URL http://localhost:9704/analytics using Administrator/Admin123

After logging in since we changed the log level in online mode we have to reload the metadata.

Click on Administration on the top right

Then click on Reload files and Metadata

Then go to our Analysis like below

Click ok ValueBasedHierarchy

Select the Hierarchy column from Employees and the salary measure from the fact.

Then click on results.

We are done and can see the hierarchy information like below.

This output just shows the salary for each employee and their manager information. This result set is not an aggregated salary for each level. Since salary information pertains to each employee salary is not aggregated.

If you check the Nquery log BI server generates only one query for the any number of operations. It’s fully in memory operation.

Let’s continue to see Ragged and Skipped level hierarchy in my next post.

Happy Learning BI J



  1. HI!
    I have do step by step as you say to make “Hierarchy Employee”, but when i run BI Answer to view result, i get error, please tell me step by step to fix this error.

    And this is error :
    Error Details
    Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P
    State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 43119] Query Failed: [nQSError: 17001] Oracle Error code: 942, message: ORA-00942: table or view does not exist at OCI call OCIStmtExecute. [nQSError: 17010] SQL statement preparation failed. (HY000)
    SQL Issued: SELECT s_0, s_1, s_2, s_3, s_4 FROM ( SELECT 0 s_0, “HR_Hierarchy_pre”.”Employees”.”EMP_ID” s_1, CASE WHEN ISLEAF(“HR_Hierarchy_pre”.”Employees”.”EmployeesDim”) THEN 1 ELSE 0 END s_2, IDOF(“HR_Hierarchy_pre”.”Employees”.”EmployeesDim”) s_3, PARENT(“HR_Hierarchy_pre”.”Employees”.”EmployeesDim”) s_4 FROM “HR_Hierarchy_pre” WHERE ISROOT(“HR_Hierarchy_pre”.”Employees”.”EmployeesDim”) ) djm

    Thanks and Regard!

    Comment by Hoang Long — November 11, 2010 @ 9:33 am | Reply

  2. Hi Santhosh,

    Thanks for your post, it is really helpful. I could implement the hierarchy but faced with a small problem:

    The hierarchy seems to expand only one level, but not into the sublevels! This is weird and I tried to see the problem, but couldn’t find a solution.

    Best Regards,

    Comment by Sam — January 21, 2011 @ 7:12 pm | Reply

RSS feed for comments on this post. TrackBack URI

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

Create a free website or blog at

%d bloggers like this: