Hierarchies (Level-based hierarchy) in OBIEE 11G

A hierarchy is a cascaded series of many-to-one relationships and consists of different levels.
Example, a region hierarchy is defined with the levels Region, State, and City.

In the Business Model and Mapping layer, a dimension object represents a hierarchical organization of logical columns (attributes). One or more logical dimension tables can be associated with at most one dimension object. Common dimensions might be time periods, products, markets, customers, suppliers, promotion conditions, raw materials, manufacturing plants, transportation methods, media types, and time of day. Note that dimensions exist in the Business Model and Mapping (logical) layer and in the Presentation layer.

There are two types of Logical Dimensions:

1.       Dimensions with Level-based hierarchies (structure hierarchies).

2.       Dimensions with Parent-child hierarchies (value hierarchies).

Level-based hierarchies are those in which members are of several types, and members of the same type occur only at a single level.

In Parent-child hierarchies, members all have the same type. Oracle Business Intelligence also supports a special type of level-based dimension, called a Time dimension, which provides special functionality for modeling time series data.

--------------------------------------------

Level-based hierarchies are those in which members are of several types, and members of the same type occur only at a single level.

A very important new feature of the BI EE 11g is the ability to support ragged & skipped hierarchies in a normal level based hierarchy. Though it does look straight-forward in terms of functionality and also the setup, enabling these 2 options basically changes the underlying SQL Queries generated.

To illustrate this, I will be using a very simple hierarchy as shown below:

 

Ragged Hierarchy feature is also dependent on the data stored in the backend table. There should be NULLs in the levels when there is no data. Usually in BI Apps, we repeat the value till the base Level if the hierarchy stops at a level. In 10g this would have been tricky, as OBIEE would have expected each leaf member to be at the same (not to be as in the above case, node C) level, and if they weren’t, you’d need to fudge the data a bit, for example by adding dummy child members to node C. So that, each leaf was at the same level. 

Skip Level Hierarchy is another approach in defining the hierarchy as shown above. Here node F is directly linked with Parent node A, but in between there it should be parent to the node F and child to the parent node A as per the earlier version like 10g. For that again we added dummy node in between node A and node F. So that, each leaf was at the same level. 

OBIEE 11g can handle this though with the new ragged and skip-level support for level-based hierarchies. It does this by detecting NULL s in either leaf levels (for ragged hierarchies) or other levels (for skip-level

hierarchies) and uses this to modify how the new hierarchical column type in Analysis handles the missing levels.

• Step 1: Have dataset as specified below to understand these above said hierarchies. 

• Step 2: Import this table in physical layer of repository and have aliases and physical joins for further development.

• Step 3: Build the complex joins in Business Model and Mapping layer between Fact and Dimensions. Create Level-Based hierarchy on Logical Dimension (Ex: Dim Ragged & Skipped Hierarchy) as mentioned below,

•  Step 4: Create hierarchy as specified by the requirement. Here it is mentioned in this below hierarchy:

 

•  Step 5: check the property Ragged Hierarchy check box in the properties of newly created above said dimensional hierarchy.

• Step 6: Drag and drop the dimension and fact tables in to Presentation layer. Remove the detail level for the hierarchical column, which is not necessary to display.
• Step 7: Create a sample report with the Hierarchical Column, which is marked as ragged hierarchy, It shows output as below ,

 Note: As per the existing dataset, Ragged Hierarchy will not permit to show the further drill dataset, if it contains NULL value. This will be achieved with the Skip Level property of Level based hierarchy. It can consider the leaf nodes even though it contains NULL values in between.

•  Go to Step 5: Instead of selecting Ragged, now choose the check box Skipped Levels as shown below,

  

Choose the same criteria in Analytics, to identify the working nature of Skip levels and the output is as shown below,

Here It will consider the leaf nodes irrespective of NULL values in between, which the earlier does not consider. In general, these two types of hierarchies will be used at once simultaneously to avoid faults in getting reports.

x----------------------------------x------------------------------------x