Dimensional Hierarchy

Dimensional Hierarchy is used to have an option for end users to drill down the columns. Dimensional Hierarchy is built in BMM layer. It is the best practice to have hierarchy built for every dimension table. A time dimension hierarchy helps to to build time series functions (to date, ago). As per my knowledge a hierarchy can be built on columns from single table. OBIEE 10.x does not support ragged hierarchies. Following is the procedure to create country>state>city hierarchy.

Procedure: RC>BMM name>New object>dimension>name-dim_dept.

RC on Dim_dept>new object>logical level

First level in a hierarchy is named ALL and grant total is checked.

RC on ALL(first level)>new object>child level>name-country

RC on country> new object> child level> name- state.

RC on state > new object

Last level is named detail(recommended).

Then drag and drop the related columns from the table to respective levels. Ex: dept_country>country

RULE OF THUMB> last level should have a primary key of the table.

RC on dept_country> new logical key> check, similar for all other levels.

Then double click on each level(country) > go to key tab> check respective field and check drill down.

For last level uncheck drill down. >> Procedure end.

For all the hierarchies build it is important to mention each in the content of respective Fact table for granularity. Go to fact table > RC on source>content>select last level(detail) for all hierarchies unless we are using aggregate table and most economical concept.