How to create Time Series Level Based Hierachy in 11G

Dimensions are the key to navigate the Business Intelligence system and the Business Model Layer plays a vital role in the creation of hierarchies, drill downs etc. Logical dimension hierarchies are categories of attributes by which business is defined in Periods,Market which are the few of the common used dimensions in the Business Model.

Now we are going to see how to create a Time Series Level Based Hierarchy

1. In the Business Model Layer right click on the Dimension Time and choose the second option and in that “Dimension with Hierarchy“.

2. Now delete all the other columns and only keep the  Time Total

3.Now click on the Time Dimension and you can see 3 option out of which choose “Time”

4. Now right click on Time Total and choose Child Level and create the Level of hierarchies you want to set following the same process.

5. Now the next task is the important task. We have to now set the key at each level where we want to drill down to at each and particular level

6. For that click on the logical level and in the Keys section. Type in the name of the key and choose the column the key is associated with and tick on both the “Use for display” and “Chronological Key”

The question arises what is this chronological key ??(This is a commonly asked interview question)

For defining a dimension to be a Time dimension, we need to have a chronological key. Chronological key is the key which is uniquely identifies the data at particular level whereas logical key is the key which is used to define the unique elements in each logical level.
Logical Level can have more than one key. When that is the case, specify the key that is primary of that level.
All other dimensions doesn’t care about the order of the values in it.
e.g. In Region_Dim the values are north, south, west and east. Here nobody wants to see whether north comes first or south comes first. i.e. no order is required here.

In the case of Time Dimension there needs to be a particular order for all the values present in it.
e.g. 2010 is earliest and 2004 is older. Dec-10 is earliest and jan-10 is older. i.e. the values in the time dimension needs to follow a particular sorting order. So the chronological key is the key which tells the obiee that the data is incrementing based on the chronological column.

For defining a dimension to be a Time dimension, we need to have a chronological Key.

7. After setting the keys at each level, the hierarchy will look something like this.

7. After doing these steps pull the Time Dimension to one of the folder in the Presentation Layer and after which when the column is pulled in the Analytics you get the output.