Hierarchy ( Dimensional Hierarchy) in OBIEE

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.
Dim hie 1Here 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 )
DIm h 2
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.
Dim h31
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.
DIm h 5
DIm h 6
(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.)
DIm h 7
Here you can see that from Quarter we have created Parent level ” Year”.  So final Hierarchy will look like this…
Final DIm h

No comments:

Post a Comment

Popular Posts