OBIEE TRAINING: Hierarchy ( Dimensional Hierarchy) in OBIEE

Hierarchy

Hierarchy: The specification of levels that represents relationship between different attributes within a dimension. For example, one possible hierarchy in the Time dimension is Year → Quarter → Month → Day.

Dimensional Hierarchy in OBIEE

First of all our first task is to thoroughly understand your business model. You have to understand what business model you want to build before you can determine what the physical layer needs to have in it.
A dimension contains two or more logical levels.

To create Dimension Hierarchy follow the steps.

1) click on the Logical table on which u want to create Dimensional Hierarchy. Right click on logical table > create Dimension

2) so we can see the Time Dim has been created under Business Model.

Here from the screen shot we can see that the Time Dim will look like this.

3) Now click on the object( Time Dim) which just been created ,and right click > expand All

4) You will be able to see the 2 levels of Hierarchy i.e. Total & Details

5) Right Click on Time Detail > New Object > Parent Level .Give the appropriate Name and make sure set the number of elements to 3 . (For Example :Month )

6) Right click on Month and select Expand All

7) Drag the Calendar Month Number column from the Time detail to Month level to associate logical column with the level of the hierarchy.

8) Define Calendar Month Number as a logical key. Also Drag the Calendar Month Name to the Month level. It will look like this.

9) Now Create Parent level “Quarter” & “Year”.(Similarly as we created month.) Drag Calendar Quarter Number from Time Detail to the Quarter Level. And Drag the Calendar year to the Year Level. And assign logical level key for both.

(Always make sure that Use of Drill down has been marked. Else you will not be able to drill down in the reports,Also make sure the related columns should be marked.)