Analysis Services Parent-Child Dimension Table Naturalizer (Free)

Author: Jon Burchel

Download and read more here.  

Overview

Parent/Child dimensions, where in each member references the ID of its parent member rather than being assigned a specific level of a hierarchy, can cause poor performance in Analysis Services databases. One way to deal with this problem is to convert a poorly performing P/C dimension table to a natural one. Instead of a structure wherein each member ID column corresponds to its parent ID column, which must be traversed recursively until the root of the hierarchy tree is reached in order to determine the full tree structure, a converted table contains one column for each level of the hierarchy in addition to the member ID, and each ancestor ID of a member is specified in the column associated with its level in the hierarchy. This prevents the requirement for traversal of the hierarchy tree in order to identify each member's location in the hierarchy.

NOTE: As of Aug 11 the tool now supports converting CustomRollupColumn and UnaryOperatorColumn from the original PC dimension (although in some cases, these features can degrade performance themselves, and it is preferable to use SCOPE calculations to implement similar functionality in 2005).

Example Hierarchy

Here is a simple example of a hierarchy structure:

• President (Employee ID = 1)
  • Vice President (Employee ID = 2)
    • Manager (Employee ID = 3)
      • Salesperson 1 (Employee ID = 4)
      • Salesperson 2 (Employee ID = 5)

Parent Child Representation of Hierarchy

Here is the associated P/C dimension table that might represent this hierarchy, where each member has an ID column and a ParentID column that references its parent member:

Employee ID	Manager ID	MemberData
1	NULL	President
2	1	Vice President
3	2	Manager
4	3	Salesperson 1
5	3	Salesperson 2

Natural Representation of Hierarchy

Here is an example of a natural dimension table for the same hierarchy (where each level of the hierarchy is represented explicitly in the table rather than simply referencing its parent member):

Employee ID	Level1 ID	Level2 ID	Level3 ID	Level4 ID	Level1 Data	Level2 Data	Level3 Data	Level4 Data
1	1	NULL	NULL	NULL	President	NULL	NULL	NULL
2	1	2	NULL	NULL	President	Vice President	NULL	NULL
3	1	2	3	NULL	President	Vice President	Manager	NULL
4	1	2	3	4	President	Vice President	Manager	Salesperson 1
5	1	2	3	5	President	Vice President	Manager	Salesperson 2