Comparison between Parent-Child and User Hierarchies in Dimensions

In SQL Server Analysis Services 2008, hierarchies are important because they are critical to optimising dimensions, and thus improving the performance of the solution as a whole. Hierarchies are collections of levels, which allow users to browse cubes in a way that they will find easy to understand.

Some hierarchies are natural; this means that the attributes are intuitively related to one another. There is a clear relationship from the top of the hierarchy to the bottom. An example of this would be date: year, quarter and month follow from each other, and in part, define each other. SSAS 2008 stores the information about natural hierarchies in hierarchy stores, and these are aggregated. Other hierarchies are unnatural; this means that the attributes are not clearly related. An example of this might be geography; we may have country -> state -> city, but it is not clear where Province might sit.

By clarifying the relationships in the data, then this will help to optimise performance. Attribute relationships help performance by building indexes, and reduce query time since it is not necessary to go through the key attribute. When the dimensions are built, the engine has an increased search space of possibilities since it does not know how attributes are related to each other. The increased search space obviously adversely impacts query time. By defining attribute relationships, the overall search space is reduced since indexes are built in accordance with the attribute relationships. Further, aggregations can be used, and re-used, where attributes are related. By tagging certain attributes as having aggregations, then it is possible to optimise the performance at query time, since the aggregations are pre-calculated.

There are different types of hierarchies: Parent-Child and User-defined Hierarchies. User-defined Hierarchies allow users to create hierarchies with their own levels, and relationships between the attributes. On the other hand, Parent-Child hierarchies use a special attribute, called a parent attribute, to determine how members relate to each other. This configuration depends on table columns. A parent attribute is a self-referencing relationship, or self-join, with the main dimension table; one is the parent column, and the other is the member key column.

Next, I have tabulated the main points between Parent-Child and User Hierarchies in dimensions:

Parent-Child Hierarchy

User Hierarchy


The hierarchy depends on two table columns:

A member key

A parent column

Collection of levels based on attributes.

Hierarchy Construction

Hierarchy is constructed in two ways:

Examine underlying dimension table data

Examine parent-child relationships between records in the table.

Grouping and Ordering attributes

Name determination

NamingTemplate property

Attribute Names


MemberswithData property set to NonLeafDataVisible or NonLeafDataHidden

Attribute Names

Hierarchical position of member

KeyColumns and RootMemberIf property of the parent

Attribute Ordering

Position of member in level

OrderBy attribute; this is considered to be an unbalanced hierarchy

Attribute Ordering; the hierarchy can be balanced / unbalanced / ragged

Data Type

The Parent and Child must have the same data type

Flexible; no restriction to have the same data type.

The implementation of hierarchies will help to optimise the performance of the SSAS 2008 solution as a whole, so it is worth taking some time over it. Once again, the SQL Server Best Practices guide has some very useful information.

Leave a Reply