[OBIEE 10g] Identifying The Hierarchy Level

 

OBIEE identifying the hierarchy level

To identify the hierarchy level which the user is currently on.

One way of doing this is "using" the aggregate awareness availability of a column. First you create a "dummy" aggregate view for each hierarchy:

• year_level: select distinct d_year, 4 AS DIM_LEVEL from d_date
• quarter_level: select distinct d_quarter, 3 AS DIM_LEVEL from d_date
• etc.

These tables you physically join to your dimension:

Add Dummy joins to your other dimensions:

Set the expression to 1=1

In your fact table you add an extra column named dim_date_level:

Next add the sources to your fact table:

Add an inner join on your core fact table to your original dimension:

On each level source map to the column:

and set the aggregation content level:

set the aggregation level for the level column to MIN:

Test your Report: