SQL Server Master Data Services Constructing Hierarchies

By: Siddharth Mehta

Overview

Management of master data is the core of Master Data Services (MDS). In order for the data to be usable, the structure of the data is very important. Data hosted in entities is stored and published in the form of lists. Data often gets aggregated and measured at different levels defined by an organization. For example, an organization may measure its revenues in a rolled up manner using the level Product -> Product Category -> Product SubCategory. Subscribing applications that depend upon MDS for master data, would often need to know the hierarchical structure of these entities. In the absence of this structure, each subscriber may organized these entities independently which can lead to discrepancies in data. In this chapter we will discuss an important MDS construct - Hierarchies.

Explanation

Collections and Hierarchies are two constructs of MDS that enable defining structured information in different ways. Collections are deprecated in SQL Server 2016, so we won't be discussing them. If you are still curious about collections, you can read more about them here.

There are two types of hierarchies in MDS - Explicit and Derived. Explicit hierarchies are marked as deprecated in SQL Server 2016, so we won't be discussing them. If case you still want to learn about them, you can read more here.

We will be performing an exercise to understand derived hierarchies in detail.

Consider three different entities - ProductCategory, ProductSubCategory and Products.

• Let's say the Products entity contains a domain based attribute named SubCategory, and the ProductSubCategory entity contains a domain based attribute named Category.
• Let's say ProductCategory contains data members - Cars, Bikes, Shirts and Shoes.
• The ProductSubCategory entity contains data members - Ferrari, BMW, Mercedes and Lamborghini with category as Cars.
• The Products entity contains data members - Ferrari - A, Ferrari - B, Ferrari - C, and Ferrari - D with subcategory as Ferrari.
• We will assume these entities and data members are already in place. Follow the below steps to create a derived hierarchy.

Create Hierarchy in SQL Server Master Data Services

In Master data manager, click on System Administration and click on Manage menu and select the option Derived Hierarchies.

Click on Add to create a new hierarchy, give an appropriate name and save. Let's call this derived hierarchy ProductsCarHierarchy.

Select this hierarchy and click on the Edit button. Drag and drop entities from the left side to the right side to configure the hierarchy as shown below.

This completes the creation of the derived hierarchy in MDS. When this hierarchy is published, it will be in the form of a view in the MDS database which would be consumed by the subscribers, as shown below. This view will contain version specific information as well as the hierarchical information that can be used by subscribers to create the structure in the subscribing application. Publishing data will be discussed in detail in the upcoming chapters.

We have learned about storing data as well as structure in MDS. Before this data can be shared with subscribers, this data needs to be validated against business rules. In the next chapter we will learn about business rules in MDS.

Additional Information

• There is a variation of a derived hierarchy known as Derived Hierarchies with Explicit Caps. Consider reading more about the this here.