Learn more about SQL Server tools

 

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

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

What's New in Master Data Services MDS 2016 - Part 2


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

Problem

SQL Server 2016 introduced many new features and improvements for all its products, and Master Data Services (MDS) is no exception. In the past year, over a dozen tips have been written about the changes for MDS. This tip - which continues from part 1 - gives a comprehensive overview of all the new features and links back to the original tips for more details.

Solution

Business Rule Improvements

The whole business rule engine in MDS has had a redesign. First they added the functionality in the Excel add-in as well, but it was removed later on (the documentation is a bit confusing on this point). Here are the most important changes:

  • The editor for business rules has received a brand new layout. It's more intuitive and easy to use compared with the previous versions of MDS.
business rule redesign in Master Data Services 2016
business rule extensions in Master Data Services 2016
  • There are new business rule conditions, such as "does not start with", "does not end with", "does not contain", "does not contain the pattern", "does not contain the subset", "has not changed" and "is not between".

Many 2 Many Derived Hierarchies

You can now create a derived hierarchy that is a many 2 many relationship. You do this by adding the two entities that relate to each other to a bridge table, both as domain based attributes. Through the bridge table, MDS knows how to map the members of the two entities against each other. You can learn more about those hierarchies in the tip Create a Many 2 Many Derived Hierarchy in Master Data Services 2016.

many 2 many derived hierarchy in Master Data Services 2016

Entity Improvements

You can now sync an entity from one model to another. This reduces the maintenance costs for redundant entities. The original entity becomes the master entity. If you make a change, it will be synced down to the other entity. The syncing can either be scheduled, or called manually. More information can be found in the tip How to sync an entity in SQL Server 2016 Master Data Services.

entity sync in Master Data Services 2016

Another new feature is the ability to purge soft-deleted members using the Explorer. In previous versions, this functionality was absent and you had to purge members using the staging tables. However, using the user interface means you are purging ALL soft-deleted members. If you only want to purge specific members, the staging tables are a better choice. The tip Purge an Entity in Master Data Services 2016 explains the process in more detail.

purge entity in Master Data Services 2016

Handling Changes

A lot of features have been added to MDS 2016 to make it easier for you to detect and manage changes.

  • With member revision history, all changes are logged at the member level instead of the attribute level. This means you can have a full slowly changing dimension type 2 like view of your master data. You can create different types of subscription views on top of entities with the member revision history log. For example, you can show only current data or historical data, or you can choose to give the full SCD Type 2 view on all of the changes. Keep in mind the transaction log history can be cleaned by the maintenance jobs (mentioned in the performance section). The member revision history is now the default and is supposed to replace the old Attribution transaction log, which is deprecated. You can find more information in the tips Member Revision History in Master Data Services 2016 - Part 1 and Part 2.
subscription views on top of member revision history in Master Data Services 2016
  • It can happen that you make changes to members of an entity while another use is making changes as well to the same members. In that case, conflicts can arise when values have been overwritten. MDS 2016 now makes it possible to easily merge conflicts. The tip How to Merge a Conflict in SQL Server 2016 Master Data Services explains this process in more detail.
merge conflicts in Master Data Services 2016
  • Important new functionality are change sets. You can configure an entity so that each change needs approval (even if an administrator makes a change). When someone makes changes to members of an entity, the changes are bundled into a change set. This change set then has to be published for approval. An entity administrator has the option to either approve or reject the change set. With this feature, you have more control over changes that are being made against your master data. Find more about change sets in the tips Approval Workflow in SQL Server Master Data Services 2016 using Change Sets - Part 1 and Part 2.
change sets in Master Data Services 2016

Various

There are a couple of smaller changes as well:

  • Use attribute names longer than 50 characters
  • Assign display names to all attributes. This means you can rename the system attributes Code and Name. The documentation says you can also hide these two attributes, but I have seen no indication that it's actually possible.
  • If you have large domain based attributes, it can be hard to find the member you wish to select in the dropdown menu. With the new feature Attribute Filters you can pre-filter the domain based attribute using a related domain based attribute. You can find more information in the tip How to use Attribute Filters in Master Data Services 2016.
attribute filters in Master Data Services 2016
  • The sample models of MDS have been updated. They now use the new features as well, so you can use them to research all of the changes in MDS 2016.
  • You can save the current view in the MDS Explorer to an MDS query file. You can open this query file in the Excel add-in (where you can also import/export queries). More details can be found in the MSDN page Shortcut Query Files (MDS Add-in for Excel).
saving query file in Master Data Services 2016
Next Steps


Last Update:






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 


Get free SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools