Overview: A discussion of how hierarchy types in Master Data Services (MDS) relate to SQL Server Analysis Services (SSAS) hierarchy types.

Quick Overview of Hierarchies

A hierarchy is a tree structure which groups similar members together. This facilitates aggregating & summarizing data. Here is a logical representation of a hierarchical structure:

Master Data Services provides a mechanism to associate any type of data into groups & hierarchy levels as needed by an organization. This is useful when the data needs to be analyzed in ways that aren’t stored in the company’s source system(s) anywhere.

Although the possibilities are endless, some very intuitive uses for hierarchies include:

Year > Quarter > Month > Day

Country > State > City > Zip Code

Company > Division > Department

Summary of How MDS Hierarchies Translate to SSAS Hierarchies

The MDS hierarchies relate to SSAS hierarchies as follows:

The remainder of this entry discusses the hierarchy types in a bit more detail.

Hierarchies in Master Data Services

There types of hierarchies in MDS include:

Derived Hierarchy. A derived hierarchy is formed automatically by relationships in the data, with no maintenance of the level assignments required by the data steward. (A recursive hierarchy, such as Manager –> Employee type of recursion, is a specific type of derived hierarchy.)
Explicit Hierarchy. An explicit hierarchy uses consolidated members to group data (as opposed to levels). The task of maintaining how data is assigned throughout the hierarchy is done manually by the data steward.
Derived Hierarchy with Explicit Caps. This is a hybrid approach. Level 1 underneath the Root would be from a derived hierarchy, and everything else underneath is an explicit hierarchy. For simplicity, this entry focuses on items 1 and 2 only: Derived and Explicit Hierarchies.

MDS Hierarchy Type	Definition	How Maintained	Ragged Levels Permitted	Control Over Sorting	Usage of Consolidated Attributes	Support For Recursion
Derived	Natural hierarchy formed from relationships in the data	Automatically (domain-based attributes)	No (requires fixed # of levels)	No (by code only)	No	Yes
Explicit	A hierarchy which is created explicitly for grouping members	Manually (drag & drop)	Yes	Yes (drag & drop order of members)	Yes	No

A derived hierarchy would be useful in a situation such as: Country > State > City > Zip Code. However, you may consider an explicit hierarchy instead when your needs are subject to change and don’t always formulate consistent levels, such as when you have varying territory levels or varying salesperson levels.

Hierarchies in Analysis Services

The types of hierarchies in SSAS are:

User-Defined Hierarchy. A user-defined hierarchy is created to organize hierarchical structures and provide the end user with navigation paths in the cube. A user-defined hierarchy should have accompanying attribute relationships. These attribute relationships would mirror the Derived Hierarchy relationships in MDS.
Parent-Child Hierarchy. A parent-child hierarchy is formed by a single parent attribute which has a self-referencing relationship. This is equivalent to an explicit hierarchy in MDS.
Attribute Hierarchy. An attribute hierarchy is just the individual attributes in a dimension. Because it stores the Leaf Level and an All Level, it’s considered a two level hierarchy. There’s no direct comparison to this concept in MDS.

Pulling It All Together

Following is how I tend to manage the promotion of data: from MDS, to a relational DW, then into an SSAS database.

In the current SQL Server 2008 R2 version, you need to set up user-defined hierarchies and parent-child hierarchies in the SSAS Dimension Designer, completely independently from what was done in MDS. Be sure that the attribute relationships in SSAS are the same as the derived hierarchy relationships in MDS.

I’m sure we can expect some further integration of MDS and SSAS in the future.

Finding More Information

MSDN: Hierarchies (Master Data Services)

Technet: Creating User-Defined Hierarchies

Technet: Defining a Parent-Child Hierarchy