MDS 2012: Part 5–Hierarchies and Collections

In continuing this blog post series on Master Data Services 2012, we will dial in on MDS Hierarchies and Collections. So far in this series we have spent time reviewing important Master Data concepts and MDM architectures. We also looked at configuring MDS. Finally, in the last post we started to dig deeper into MDS by looking at Models, Entities, Attributes and Members.

Series Index

Part 1 – Understanding Master Data

Part 2 – Master Data Management Architectures

Part 3 – Installing Master Data Services

Part 4 – Models, Entities, Attributes & Members 


Hierarchies are tree-structures that are typically used to group members and are also convenient when consolidating or summarizing data at specific points for reporting or analytical purposes. When discussing hierarchies we deal with two specific types: ragged and level-based.

Ragged hierarchies are denoted by the fact that there is no predefine structure to the hierarchy levels and leaf members can exist at any level of the hierarchy. Simply put, the number of levels throughout the hierarchy varies in depth.  This type of hierarchy requires consolidation points which are explicitly created for either analytical or reporting needs.

Level-based hierarchies in contrast to the varying nature of their ragged counterparts have a consistent structure from top to bottom. These hierarchies are represented at each level by a single entity. They are also different in that all leaf members must exist at the same level, meaning that they cannot be assigned to multiple levels within the hierarchy.

While ragged and level-based hierarchies are structurally different they both share a common yet important rule. Leaf members in both types of hierarchies can only appear once in the hierarchy. This is a distinction worth nothing as many people confuse hierarchies with taxonomies. As the MSDN article in the references section points out, taxonomies classify a member by multiple attributes and a leaf member can appear multiple times within a taxonomy. Hierarchies classify members by a single attribute and thus restrict leaf member to appearing only once per hierarchy.

Now that we have an understanding of the core hierarchy types, we will move on to looking how each hierarchy is implement in Master Data Services 2012.

Explicit Hierarchies

Explicit hierarchies are the Master Data Services implementation of a ragged hierarchy. They are created on an entity and must be defined as either mandatory or non-mandatory. Defining an explicit hierarchy as mandatory requires that every leaf member be included in the hierarchy and is an effective tool to ensure completeness and correctness. Non-mandatory is obviously the reverse of a mandatory hierarchy in that leaf members optional participate in the hierarchy.  It should be noted that if you create multiple mandatory explicit hierarchies on an entity, leaf members must be included in each hierarchy.

Finally, before we move on to creating an explicit hierarchy, it needs to be pointed out that by their nature explicit hierarchies are considered manual meaning that you are required to manually maintain them. As such an effort should be made to avoid them whenever possible to reduce the amount of work required to maintain consistency and accuracy within your master data model.

Creating an explicit hierarchy is a straight-forward task that is driven from the Edit Entity page. To navigate to the Edit Entity page start by clicking the System Administration link. Next, choose Manage from the menu and then select Entities. Once on the Entity Maintenance page, select the entity you wish to add an explicit hierarchy to and then click the edit pencil icon. On the Edit Entity page, you will notice an Explicit Hierarchy section if you have enabled explicit hierarchies and collections for the entity. If you have not enabled explicit hierarchies and collection, you must enable the feature before the section dialog will appear.

7-9-2012 9-34-24 AM
Enable Explicit Hierarchies and Collections
7-9-2012 9-23-15 AM
Entity  Explicit Hierarchy Section

From the explicit hierarchy section, you can add a new hierarchy as well as edit or delete an existing hierarchy. When adding a hierarchy you specify a hierarchy name and choose whether the hierarchy is mandatory or non-mandatory. When deciding whether a hierarchy is mandatory or non-mandatory, you should use caution as you cannot change the hierarchy once it is initially defined.

7-9-2012 9-23-56 AM
Create/Edit Explicit Hierarchy

Once you have created an explicit hierarchy, you can create consolidated members for grouping purposes and manage the hierarchy through the Hierarchy Explorer. To navigate to the Hierarchy Explorer, choose Explorer from the MDS homepage and then select the hierarchy from the hierarchies drop-down menu.

Recall that consolidated members are a special type of member, who primary purpose is for grouping leaf members in hierarchies and collection. This special type of member is required because a leaf member cannot have child members.

7-10-2012 9-13-53 PM
Viewing Consolidated Members in Hierarchy Explorer

Consolidated members (and leaf members) can be created directly from within the Hierarchy Explorer. When you have enumerated the required consolidated members, organizing the explicit hierarchy is as simple as either dragging and dropping or cutting and pasting the members into the required tree structure.

Finally, you may be wondering if there is a better way to manage these hierarchies. Indeed there is, we will touch managing MDS hierarchies in a future blog post.

Derived Hierarchies

Level-based hierarchies are implement as derived hierarchies in MDS with each level in the hierarchy defined by an entity. Derived hierarchies often come across as more natural because these hierarchies are based on attribute relationships that are defined within MDS. While you could probably think of many examples of these types of hierarchies, a few common examples are a Category, Subcategory, Product hierarchy or a Geography hierarchy which could have the following levels: Country, State, City, Postal Code.

A couple benefits exist because of the very nature of derived hierarchies. First, since members participate in the hierarchy based on an attribute value these type of hierarchy are often recognized as being significantly more efficient and easier to maintain. Second, you will notice that there is not notion of mandatory or non-mandatory derived hierarchy. Again, because this type of hierarchy is driven by attribute value allowing null values for an attribute makes the hierarchy non-mandatory while defining a business rule to require the attribute value would make the hierarchy mandatory.

The last important aspect of derived hierarchies to point out is their support for recursive hierarchies. A recursive hierarchy exist when an entity has an attribute whose type is the same as the entity. That may seem like a little bit of a brain bender, but these types of hierarchies are quite common and are found in organizational charts when you define and employee and manager relationship.

To create, edit and delete derived hierarchies, we will use the Derived Hierarchy Maintenance page in the System Administration section (System Administration ?> Manage ?> Derived Hierarchies). When creating a new derived hierarchy, select the required model, click the green-plus icon and then specify a name for the hierarchy when prompted. Once you save the hierarchy, you will be presented with a list of all available entities and hierarchies within the model.

7-10-2012 10-03-40 PM
Derived Hierarchy Maintenance
7-10-2012 10-06-27 PM
Create a New Derived Hierarchy

Build the hierarchy from the lowest level, which in the case of a Product hierarchy would start with the product entity. Drag the entity from the left-panel to the center panel which is labeled as Current Levels. As you drag entities from the left, you will noticed that the list of available entities changes. Because derived hierarchies are attribute relationship driven, only entities that are related are available for selection. As you make selections, notice that the preview pane updates to allow you to preview the hierarchy you are building. If you find that you have made an error at some point, you can click the incorrect level in the Current Level section to select it and then click the red-x icon to remove the level.

7-10-2012 10-10-22 PM
Creating Derived Hierarchy
7-10-2012 10-10-53 PM
Manage Hierarchy Levels

For recursive hierarchies, nothing is different for a non-recursive hierarchy in that they are built in the same manner. MDS will actually detect a recursive relationship and prompt you anchor the top of the hierarchy where the attribute value is null.

7-10-2012 10-15-43 PM
Recursive Hierarchy Detection

Once you hierarchy is complete, you can browse the hierarchy in the Hierarchy Explorer we saw previously. Note that you can use the explorer to move members around in the hierarchy. This essentially works by updating the attribute value of the member behind the scenes.


Our final topic is the implementation of collections in MDS. Collections allow you to group both leaf and consolidate members as well as other collections within a single entity. These are flat list and do not contain any hierarchical structure. Often these collection are used create a taxonomies that are available to multiple downstream source systems.

7-10-2012 10-33-30 PM
Collection Details
7-10-2012 10-33-56 PM
Collection Members

When you define a collection, you specify a name, a unique code for the collection and an optional description. You add members to a collection individually and can arrange the members to control their sort order. as well as define weights for each collection member so that downstream subscribing systems can use the value for analytical purposes.


In this blog post we reviewed ragged and level-based hierarchies before looking at the implementation of both Explicit (ragged) and Derived (level-based) hierarchies in Master Data Services. We also briefly discussed MDS collections. Hierarchies and collection are intended to help you organize your master data and make both management and reporting and analytics easier.

In the next post in this series we will jump into the rationale behind business rules in MDS. We will walk-through defining rules and validating members to ensure data quality and consistency in your master data.

Till next time!



One thought on “MDS 2012: Part 5–Hierarchies and Collections

  1. Pingback: Intro to Master Data & MDS Follow-Up | Bluewater SQL

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s