Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Create a many-to-many derived hierarchy in SQL Server Master Data Services


By:   |   Read Comments (5)   |   Related Tips: More > Master Data Services

Attend these FREE MSSQLTips webcasts >> click to register


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:

create user entity in Master Data Services

Next I created an entity to hold the sales regions:

create region entity in Master Data Services

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:

create bridge entity in Master Data Services

The next step is to add the domain based attribute for User:

create DBA user in Master Data Services

Repeat the same steps to add the Region attribute:

create DBA region in Master Data Services

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.

populate bridge table in Master Data Services

The hard work has been done. Now we only need to create a hierarchy on top of these entities.

add derived hierarchy in Master Data Services

The first step is to drag the User entity to the leaf level of the editor.

create derived hierarchy leaf level in Master Data Services

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).

create derived hierarchy leaf level preview in Master Data Services

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.

create derived hierarchy add parent level in Master Data Services

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.

create subscription view with derived levels in Master Data Services

The view returns the data in the following format:

subscription view with derived levels in SQL Server Management Studio

The other option is to create the subscription view as a parent-child relationship.

create subscription view as parent child in Master Data Services

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.

create subscription view as parent child in SQL Server Management Studio

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


Last Update:


signup button

next tip button



About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a BI professional, specializing in the Microsoft BI stack with a particular love for SSIS.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Tuesday, August 29, 2017 - 1:50:26 AM - BIlal Back To Top

I created M2M drived hirarchy and then apply hirarchy on attribute to filter records on the basis of hirarchy.

 


Monday, August 28, 2017 - 3:04:25 AM - Koen Verbeeck Back To Top

 Are you trying to create attribute filtering or an M2M relationship between two attributes? I'm not sure you can combine the two.


Saturday, August 26, 2017 - 5:42:35 AM - BIlal Back To Top

 I handled the same many to many relation in MDS and then created derived hirarchy, But when you apply this many to many derived hirarchy to any attribute then you will get an error below

Errors

  • 200117 : The attribute cannot be updated. The attribute filter is not compatible with one or more current attribute values. First member code: 11, version: VERSION_1, @ErrorNumber = 50000, @ErrorProcedure = "udpAttributeSave", line 618.

 


Tuesday, April 04, 2017 - 9:09:29 AM - Koen Verbeeck Back To Top

This is a new SQL Server 2016 feature.
As far as I know this is not possible in earlier versions of MDS.


Tuesday, April 04, 2017 - 8:40:08 AM - RT Back To Top

 Hi,

I'm trying to achieve the same thing in SQL Server 2012. Is there any alternative to this approach in previous versions of MDS.

 

Thanks

RT


Learn more about SQL Server tools