Report Portal

Analysis Services Parent-Child Dimension Table Naturalizer (Free)

Author: Jon Burchel

Download and read more here.  


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
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

Tags: tool, performance, parent-child


2007-2015 VidasSoft Systems Inc.