More about Dimensional Hierarchy

After learning basics of BI, ETL and concepts of facts and dimensions. We would now learn about “Dimension Hierarchies”

What is Dimension Hierarchies?

Dimension Hierarchy is the way all the attributes in a Dimension table are connected to each other by position.

By Analogy,

As Dimensions are key to navigating data warehouse/business Intelligence system,

Hierarchies are key to navigating dimensions

Types of Dimension Hierarchies:

1.   Fixed Depth Positional Hierarchies

• Have fixed number of levels
• Series of many to one relationship
• Here each hierarchy level should appear as separate positional attributes in a dimension table
• Set of Attributes should have meaningful name

Pros:

a.       Easiest to understand

b.      Predictable and fast query performance

Cons:

a.       Cannot be used when hierarchy is not a series of many to one relationship

2    Slightly Ragged / Variable Depth Hierarchies

Don’t have fixed number of level, but the range in depth is small

Pros:

a.    Consistent parent child relationship

Cons:

a.       Inconsistent at logical level

3     Ragged/Variable Depth Hierarchies

     This can be best explained by an example as above. Here we have atleast one member whos parent belongs to a hierarchy that is more than one level above the child.

This can be divided in 2 categories:

1. Ragged/Variable Depth Hierarchies with Hierarchy Bridge Tables

It has a specially constructed bridge table .This table contains a row for every possible path in the ragged hierarchy and enables all forms of hierarchy transversal to be accomplished with standard SQL rather than using special language extension which is not possible otherwise.

2. Ragged/Variable Depth Hierarchies with PathString Attributes

Bridge Table Creation can be avoided just by using a PathString attribute in the dimension. It contains a specially encoded text String which contains the complete path from the supreme node to the node described by the particular dimension row.

1 of the above 2 method for Variable Depth Hierarchies is selected based on Technical and organisational needs.

We will discuss some more interesting topic in next blog. Stay tuned.

References:

http://www.informationweek.com/software/information-management/kimball-university-maintaining-dimension-hierarchies/d/d-id/1073326?page_number=1

http://technet.microsoft.com/en-us/library/aa198068(v=sql.80).aspx

http://pic.dhe.ibm.com/infocenter/dataarch/v8r1/index.jsp?topic=%2Fcom.ibm.datatools.dimensional.ui.doc%2Ftopics%2Fc_dm_hierarchy_ragged.html

The Datawarehouse Tool Lit - Ralph Kimball, Margy Ross