OBIEE Interview Question- Dimensional Hierarchy

A dimensional hierarchy is created in the BMM layer of a repository to calculate level based measures and to create interactive reports/dashboards with drill down functionality.

A dimension hierarchy can be created manually as well as automatically. Manual process involves right clicking on BMM layer> New Object> Dimension, entering a name; right clickking on this new dimension and create New Level.

Levels are base on the business definition or hierarchy of a dimension, a classic example; year> quarter> Month> week> Day. A hierarchy will have at least two levels, Grand Total Level and Detail Level. For each level we have to define the number of elements. This is always 1 for the Grand Total Level and next levels are the ratio from one level on top to the bottom.

When creating a dimension automatically, we have to right click on the dimension and click Create Dimension. This will automatically create the two levels Grand Total and Detail Level.

We can actually automatically calculate number of elements at each level. The procedure is to open a repository in online mode. Right click on business model or dimension hierarchy and click on estimate levels.