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

By:   |   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:

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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Koen Verbeeck 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



Comments For This Article




Tuesday, May 28, 2019 - 6:12:16 AM - Koen Verbeeck Back To Top (81232)

Hi Munmun,

I haven't used RLS myself a lot in MDS (typically it's an all or nothing approach). You can try the book Master Data Services, 2nd edition.

https://blogs.lessthandot.com/index.php/datamgmt/master-data-services-second-edition-review/

It's about SQL Server 2012, but the security concepts should be largely the same.

Regards,
Koen


Thursday, May 16, 2019 - 10:10:50 AM - [email protected] Back To Top (80103)

Hi,

I tried the same approch written in the link.

https://www.mssqltips.com/sqlservertip/4676/how-to-use-attribute-filters-in-master-data-services-2016/

Now only one step is left, how I can let users see only IT department (referring the example in the link) from the dropdown list, in explorer view/excel. My requirement is to let users enter data only for a specific department not for all department.

Its kind of imposing row level security. Whch I am trying to implement but failing. I tried following steps:

1) Give the {IT Department} node of  derived hierarchy read only access and deny access on all other nodes (Sales, Engineering).

(Security > Manage Users >user>Hierarchy Members)

2) As there are 3 entites data present in the derived hierarchy, give all three entites Read only access.

4) On the original entity,(where I would like to enter data for IT Department and the corresponding managers) , I give read update and create access.

5) I did not give any access on the model.

Please suggest me some guidence on row level security of MDS.

Regards,

Munmun


Thursday, May 16, 2019 - 3:29:19 AM - Koen Verbeeck Back To Top (80098)

Hi,

you can try attribute filtering, but I'm not sure it will work with M2M relationships.

https://www.mssqltips.com/sqlservertip/4676/how-to-use-attribute-filters-in-master-data-services-2016/


Wednesday, May 15, 2019 - 10:57:58 AM - [email protected] Back To Top (80090)

Thanks, Very Nice and helpful Article.

I have a question, If after making hierarchy I would like to see users for only one region, Is it possible to achive? Lets say I have another attribute in Region table named 'IsActive' set to 1 for a specific region, and 0 for others. And this IsActive value is changeable as per the bussiness requirement. So, keeping only 1 region in the region table won't be the solution, I need to make the hierarchy on all the regions but need to show only Actve region based on the IsActive column value, which will further filter user lists as well.


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

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 (65520)

 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 (65389)

 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 4, 2017 - 9:09:29 AM - Koen Verbeeck Back To Top (54220)

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


Tuesday, April 4, 2017 - 8:40:08 AM - RT Back To Top (54219)

 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















get free sql tips
agree to terms