Jeremy Kashel

Jeremy Kashel's Blog

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:

image

image

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:

image

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:

image

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:

image

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:

image

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:

image

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:

image

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

image

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.

Master Data Services SQL Server 2016

Unless you’ve been living under a rock for the last week or so, you will have noticed that CTP2 for SQL Server 2016 has been released and is now available for download.

I finally got round to downloading the CTP myself yesterday and my first port of call was to take a look at the new version of Master Data Services, as it’s been given a several improvements. Included in this list of improvements is a revamped front end, so I thought it would be worth making a start by exploring the front end improvements in this blog post.

New Look and Feel

The first thing that you will notice is the new look and feel that will greet you in the web UI. Here’s the new look:

image

To recap - here’s the equivalent in SQL Server 2014 MDS:

image

The have been a few changes to the actual Explorer grid interface. One is that you now get a Copy Member button and also there is the ability to export your current view to an Excel MDS Add-In favourites file:

image

Another useful change is that on each entity you now get told who last updated the member and who created the member. In the previous version, MDS logged who last updated the member, but it wasn’t displayed to the end users. I’ve spoken to users who need this feature so its good to see it implemented:

image

Entity Dependencies

If you go into the Explorer, the main grid is still present and looks the same, but there are a few changes to the menu. There used to be a Explorer menu but this is now gone – after all you can get to the same thing via the other menus. There is a new Entity Dependencies menu, which will list all of the entities within the model, as you can see here:

image

After each entity is some brackets, which indicates the number of dependencies that the entity has. If you click on the entity (e.g. the Region entity), then you will get the opportunity on the Entity Dependencies view to drill down on an entity member in order to locate its dependencies at different levels:

image

Hierarchies and Collections

There have been some major changes to MDS hierarchies as covered on the MDS Team blog. In summary, Explicit Hierarchies and collections are now depreciated with the idea that they can be replaced with some of the new features of derived hierarchies. One such feature is the “Unused” node, which shows any members of the Derived Hierarchy that are missing a parent. E.g. in the example below I’ve created a new Area member, but its not been assigned a value for its Big Area attribute:

image

A small but useful change that I’ve noticed is that there is a setting for hierarchies that will auto-collapse a hierarchy branch if you expand a sibling branch:

image

For example, looking back at the screenshot I’ve included above, if North America was expanded, then turning this setting on would mean that that North America would collapse if I expanded the International branch of the hierarchy.

Summary

These UI improvements to the web interface make a welcomed change to a product that is now in its fourth SQL Server version. It’s great to see Microsoft investing in MDS, this will no doubt help increase the uptake of a product that (aside from in SQL 2014) has now seen steady improvement.