Master Data Services Many to Many Relationships

Many to Many Relationships (M2M) are possible in the current version of Master Data Services, but they come with a few workarounds. Thankfully, many to many hierarchies are one of the many new features that can be found in Master Data Services 2016. This post will contrast the M2M approach in the current version of MDS, before showing how its greatly improved in the current MDS 2016 CTP.

Customer Example – MDS 2014

The simple example that I’m going to show is Customers and Bank Accounts. That is, a customer can have more than one bank account, but a bank account can also have more than one customer, e.g. a joint account or a business account.

First, to show the MDS 2014 approach, there are a few entities to look at, namely Customer and Account:

Then there is an other entity that relates to both Customer and Account, called CustomerAccount, which essentially acts as a bridge table, allowing the M2M relationship to naturally exist in the data:

Moving away from the entities, I’ve created two derived hierarchies so that we can look at the data by account or by customer. So here we can see, for example, that Bob is keeping his own personal account, as well as the account with his wife…hmmm:

Or looking at this another way, we can see the inverted version of the hierarchy, where we see the Customers as the top level, with some accounts belonging to more than one customer:

As you may have spotted, there’s an issue with this approach. Whilst we do see the name of the Customer in the first hierarchy, its pointing at a member in the CustomerAccount entity, which is why the code is different each time. Enter SQL Server 2016…

Master Data Services 2016

MDS 2016 does a far better job at visualising the M2M relationship. You still need the link entity, but it will allow you to show the actual Customers and Accounts with their correct codes.

I’ve used the exact same entity setup as MDS 2014, so lets begin by looking at how we can build a hierarchy that shows customers by accounts. When creating a new Derived Hierarchy, we can see that the initial available levels list is just the same as it would be in MDS 2014:

The first step is to drag over the entity called Customer to the Current levels section. Once we perform this action, the Available Entities pane looks very different in SQL 2016 that in would in the current version. Now MDS is allowing us to pick the Account entity, but via another entity. Hence it says “mapped via” on the left hand side:

Once we finished the hierarchy build, and explore the hierarchy, we can see the same data as before, but this time its the actual correct customer codes that are displayed:

The derived hierarchy by Customer also displays the correct customer and account codes:

Summary

A derived hierarchy in Master Data Services 2016 now allows the correct display of many to many relationships between two entities. This is one of many new features in MDS 2016, offering quite a number of improvements over the SQL Server 2014 version.