Dimensional Hierarchies and "LEVELX"

[krisnstarr]

Hi, my first post, long time lurker up until now

I am revisiting an old Oracle DW with a view to move it from Oracle V9 to V11.

The dimensions all have structured hierarchies, for example the PERIOD dimension is (in parent -> child order)

YEAR
QUARTER
MONTH
WEEK
DATE

which is fine, but what I am confused about is each level record sits alone in the DW, and has an extra column called LEVELX. This levelx column is in fact stamped with the level, unsurprisingly. Also each level only contains the information it needs to decribe itself, e.g. MONTH will have null values in DATE and WEEK.

I have found mention of this elsewhere, e.g.
OBIEE Blog
Ora Dba DW guide



Am I completely misunderstanding things or, is this not in fact a flattened snowflake architecture? surely yuo could remove the LEVELX column, remove all rows that were not at the lowest level, and define the hierarchy with Oracle's "create dimension" command? The oracle dbms_dimension.validate dimension prefers this, and gives NOT NULL errors to the parent
"LEVELX" rows.

e.g. a dimension table like this.

ID DATE WEEK MONTH QUARTER YEAR YEARMONTHWEEK YEARMONTH YEARQUARTER
---------- ------------ ---------- ---------- ----------- ------ --------------------- ------------- ----------------
6112773 01-AUG-10 1 8 3 2010 20100801 201008 20103
7112774 02-AUG-10 1 8 3 2010 20100801 201008 20103
8112775 03-AUG-10 1 8 3 2010 20100801 201008 20103


Thanks for reading

Mr confused

[krisnstarr]

hmm it's possibly purely for aggregation purposes? that makes sense.

[EDIT]
Also, the FACT table only has a key linking to the lowest level of the date hierarchy, instead of a key linking each level e.g. WEEK,MONTH,QUARTER,YEAR.
To me this seems proof that what I have my hands on here is a snowflaked dimension. The Business Objects universe also links this way. (see pic)





Can anyone give me there thoughts on this, as I am thinking of (a) adding in keys to the fact for every level (b) redefining some of those levels