Friday, June 1, 2012

Data Warehouse Basic Concepts:Part 2

Dimensions

Dimensions are used to provide context to facts. They can also be drilled down upon. Dimensions can either be orthogonal to each other or related via parent-child relationships.

When there are dimensions related via parent-child relationships, Dimension Hierarchies are formed.

Dimension Hierarchies

There are many aspects of dimension hierarchies.

Level-Based vs Value-Based.
  1. Level-based: clearly defined parent-child levels.
  2. Value-based: parent-child relationships within values at the same level (e.g. people relationships).
Balanced vs Unbalanced
  1. Balanced: all branches descend to the same depth.
  2. Unbalanced: some branches stop having children at certain depth where other branches continue having children.
Ragged
  1. Null values can appear in the middle of a hierarchy, with child values continuing beyond. i.e. it is possible to skip one level.
Simple vs Multiple Path vs Parallel Path
  1. Simple: dimension hierarchies do not cross (i.e. levels & values overlap).
  2. Multiple Path: dimension hierarchies cross, but criteria for analysis is same.
  3. Parallel Path: dimension hierarchies cross, and criteria for analysis can be different (e.g. city in business unit vs location hierarchies). 
Other Notes
  1. Each child dimension value can only have one parent.
  2. Dimension hierarchy data are usually normalized in source data and staging area, but flattened into a single dimension table in the data warehouse.
  3. Create a root node at the top that represents "ALL".
References
  1. www.informationweek.com/news/software/bi/showArticle.jhtml
  2. www.executionmih.com/olap/data-warehouse-olap-hierarchy.php
  3. andyblg.wordpress.com/2010/01/29/oracle-olap-hierarchies-and-levels/

No comments:

Post a Comment