SQL Server Analysis Services – Different types of Hierarchies

A dimension consists of various attribute members. Attributes are exposed to end users through attribute hierarchies. A hierarchy defines the relative position of attribute members in a dimension. By default, in UDM, every attribute can form its own attribute hierarchy and dimensions are the containers for these hierarchies. Dimension may also contain user hierarchies which act as navigational paths to help the user in exploring the data from various angles. Now we are going to see here the different types of user-defined hierarchies that can be created based on the relationship of attribute members with each other.

Natural Hierarchy – When we create a user-defined hierarchy, we define relationship between hierarchy levels. It helps the SQL Server Analysis Services to create useful aggregations which in turn increases query performance. When levels in a hierarchy are linked in a natural relationship like one-to-one or many-to-one, such hierarchies are known as Natural Hierarchies. Like in a Calendar hierarchy, Day level related to the month level, month level to the Quarter and so on.

Natural hierarchies are also known as Balanced hierarchy. In a Balanced Hierarchy, each attribute is a member property of the attribute one level down. In other words all branches in the hierarchy descend to the same level and each member’s logical parent is the level immediately above the member. For example, the Product Category hierarchy in the product dimension like,

Product Category –> Product Sub Category –> Product

Non-Natural Hierarchy – In some type of hierarchies the natural relationship is lacking between the hierarchy levels and branches of the hierarchy descend to different levels. Such hierarchies are known as Non-Natural or Unbalanced hierarchies. For example, the Marital Status à Gender hierarchy is a non-natural hierarchy as Marital Status and gender attributes do not have a relationship to each other.

Ragged Hierarchies – Are the type of unbalanced hierarchies wherein, the logical parent member of at least one member is not in the level immediately above the member. In this case the hierarchy descends to different levels for different drilldown paths. Take an example of geographic hierarchy. The levels available are Country à State / Province à City. But some countries like Greece do not have provinces thus creating a ragged hierarchy.

In dimension table supporting a ragged hierarchy, the logically missing members can be represented in using nulls or empty strings, or they can contain the same value as their parent to serve as a placeholder.

Parent-Child Hierarchies – When an attribute in a dimension table has the parent attribute which is related using a self-referencing relationship, it is known as Parent-Child Hierarchy. Such hierarchies are constructed from a single parent attribute. Please refer to the DimEmployee dimension schema in the AdventureWorks2008 database. The ParentEmployeeKey column in the table is related with EmployeeKey primary key column with foreign key relationship. This means each record in a table is related with another record in the same table through a parent-child relationship. In this kind of structure the data can be derived using self-join.

When we create a dimension out of such table having self-referencing relation set, Analysis Services recognizes the parent-child hierarchy and accordingly build the dimension. Look at the parent-child icon in the attributes pane of the dimension designer.

The attribute Employee in nothing but the Employee Key whose Name Column is set as Employee Full Name which is a named calculation defined in the data source view. Similarly the attribute Parent Employee is the Parent Employee Key with Full Name set as the Name Column. After compiling the dimension we can use the browser tab to check how the data will be displayed as shown in the following figure.

Regards

Amit Karkhanis

Like us on FaceBook  |  Follow us on Twitter

Join the fastest growing SQL Server group on FaceBook