Dimensional Hierarchies Best Practices

• Each dimension table must be related to exactly one dimension hierarchy. In other words, the relationship between dimension tables and hierarchies is many-to-one.
• The only time when multiple dimension tables share the same hierarchy is in snowflake schemas. The hierarchy then follows the join paths, i.e. the outrigger table provides the higher levels, whilst the table joined to the fact will provide the detailed levels.
• Each hierarchy must have at least two levels:

-          Grand total

-          Detail

• Each hierarchy should have exactly one grand total level.
• No key should be specified at the grand total level.
• Each level except the grand total level must have at least one column associated with it. No column can be associated with more than one level.
• All levels except the grand total level must have a key defined. The key for each level should reference a unique value representative of the level.
• The key for drill-down can be different to the unique level key.
• The key of the lowest (detail) level should be the primary key for the dimension.
• There can be multiple branches within a hierarchy. However, they must all share the same grand total and detail levels.
• Number of elements should be entered for each level in the hierarchy (value of 1 should be entered for grand total). This affects the performance of the system.