By: Koen Verbeeck | Comments | Related: > 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.
- Through business rule extensions you can write your own business rule conditions and actions using T-SQL functions and stored procedures. You can find guidelines on how to create these in the tip Business Rules Extension 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.
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.
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.
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.
- 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.
- 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.
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.
- 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).
Next Steps
- You can find a full overview of all the new MDS features on the MSDN page What's New in Master Data Services (MDS).
- Also check out the Master Data Services team blog, where additional info can be found on the new features.
- You can find more Master Data Services tips in this overview.
- For more SQL Server 2016 tips, you can use this overview.
About the author
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
