By: Koen Verbeeck | Comments (9) | Related: > Master Data Services
Problem
SQL Server 2016 comes with many brand new features for Master Data Services (MDS). One of those new features is the ability to model a many-to-many relationship in a derived hierarchy. In this tip, we'll go over the necessary steps you need to take to set-up such a hierarchy.
Solution
A many-to-many relationship occurs when a member of an entity can have a relationship with one more attributes of another entity, and the other way around. A typical example are joint checking accounts. A person can have one more checking accounts, but since a checking account can be shared between multiple persons, a checking account can belong to one or more persons. In a database, such a relation is typically modeled using a bridge table. Such a bridge stores all the relationships between the two entities. In MDS, we'll do the exact same thing.
As example in this tip, I have multiple users who are responsible for one or more sales regions. A region though can belong to one or more users as well. I created a simple entity to hold the users using the MDS Excel add-in:
Next I created an entity to hold the sales regions:
The bridge table itself will consist of two attributes: User and Region, which are both domain based attributes using the entities we just created. In order for the domain based attributes to work properly, the name attribute of the two entities needs to be populated. Let's create the bridge entity:
The next step is to add the domain based attribute for User:
Repeat the same steps to add the Region attribute:
Now we can enter the data for the bridge table using the dropdown menus. As you can see below, a certain user can belong to multiple regions and a region can hold multiple users.
The hard work has been done. Now we only need to create a hierarchy on top of these entities.
The first step is to drag the User entity to the leaf level of the editor.
MDS will create a hierarchy with a single level, which you can preview at the right. MDS will also automatically scan for relationships with other entities. It has found that Region is related to User, through the bridge entity (as indicated between brackets).
You can now drag the Region entity above the User entity in the editor (at Drop Parent Here). A parent level is added to the hierarchy. In the preview, you can verify that the many-to-many relationship is handled correctly by MDS.
You can also create subscription views on top of a many-to-many derived hierarchy. You can configure the view to have a fixed number of derived levels. In that case, the hierarchy will be flattened.
The view returns the data in the following format:
The other option is to create the subscription view as a parent-child relationship.
The format of the view is a bit different now: the regions are returned with ROOT as their parent, while the users can be returned multiple times, each time with a single region as parent. This means data can be duplicated, so be careful when loading this data into another system.
Conclusion
Creating a derived hierarchy on top of a many-to-many relationship is straight forward. You need an entity with domain based attributes for both entities. This entity will function as a bridge table. When creating the derived hierarchy in the editor, MDS will automatically use this bridge entity to determine the relationships.
Next Steps
- You can follow the guidelines in this tip to try it out yourself.
- For more information about derived hierarchies: SQL Server Master Data Services Constructing Hierarchies.
- For an explanation of Domain Based Attributes, check out How to standardize attribute values in Master Data Services.
- You can find more Master Data Services tips in this overview.
- For more SQL Server 2016 tips, you can use this overview.
About the author
Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on MSSQLTips.com. Koen has been awarded the Microsoft MVP data platform award for many years.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
