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 |
|
|
Definition |
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 |
|
Display |
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.