On the OTN forum I was asked how you can identify the hierarchy level which the user is currently on (Did he select Year, Quarter,Month etc?).
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
These tables you physically join to your dimension:
Add Dummy joins to your other dimensions:
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:
Be sure to check all your dimension levels!
Till Next Time