Wednesday, November 6, 2013

Dimension table design - when should have Parent Child structure

One of the important parts in OLAP design is to design the dimension table structure, because this design affects the Hierarchies in the cube. While I go through the AdventureWorksDW2012, I noticed that the difference table structure between the DimEmployee and DimGeography dimensions, the difference is DimGeography table not contains Parent Key replationship, but DimEmployee has the parent key relationship.

The reason that I found that, in a hierarchy like Geography would provide balanced hierarchy for the United States. Completely balanced hierarchy means that all the leaf (bottom level) notes would be an equal distance of top level. Some hierarchies are in dimensions can have an unbalanced distribution of leaf nodes relative to the top level. Such hierarchies are called unbalanced hierarchies. Employee structure is an example of unbalanced hierarchy. There are different depths to the hierarchy of the Employee, such as General Manager might have unit managers and administrative assistance, under unit managers might have supervisors, whereas administrative assistant would not have any direct reports. Some hierarchies are typically balanced but are missing a unique characteristic of some members in a level. Such hierarchies are called ragged hierarchies. If you take United States, Country (USA)-->State(Washington)-->City(Seattle), whereas in some countries like Greece only Country --> City, so here State level is missing.

Some example of completely balanced hierarchy: Geography, Date

Some example of unbalanced hierarchies: Organization, Employee

Cheers!

No comments:

Post a Comment