SQL Server 2016 CTP 2 released almost a day ago, and I’ve had a chance to install it and play with Master Data Services to see what changes has been made in this product. Master Data Services is a service of SQL Server first released in SQL Server 2008R2, and enhanced in SQL Server 2012. There was no changes in MDS of SQL Server 2014. But fortunately MDS wheels are spinning now, there are some changes in 2016. Changes are not major though they  are evidence of Microsoft investing on this services nowadays.

Changes in MDS of SQL Server 2016 CTP 2 are:

• New Layout for MDS Web interface
• Control on Logging configuration for Models
• Data Compression Option for Entities
• Explicit Hierarchies are Deprecated
• Entity Dependencies tab in Web explorer
• Change in Security Functions
• Detailed level of security on models
• SQL Server Jobs for Maintenance Log and Index
• Display Name for attributes such as Name and Code

Areas that hasn’t been changed are:

• Version Management
• Derived Hierarchies
• Excel Add-in experience
• Entity Relationship
• Business Rules
• Subscription Views
• Entity Based Staging

I personally pleased that subscription views, and entity based staging structure had no changes, as they work really well and they are great features of MDS. However I would like to see some new types of rules in Business Rules at least. Now let’s go more in details into changes;

New Layout for MDS Web interface

This change is the very first change that you will feel after installation and configuration MDS. the old MDS web layout has been changed to a new layout which looks more professional now.

Control on Logging configuration for Models

You now have configuration control on logging for the models.  Models contains entities, and entities contains data. Data usually have a log, and the log file for transactional data grows time by time. So having a control on the retention is really useful feature. in 2016 you can control that with a setting under Model configuration as below image illustrates.

Log Retention Days configuration means (Sourced from MSDN):

The number of days after which the logs will be deleted. The default value is -1 and indicates the log tables will not be cleaned.

If the value is 0, log tables retain only today’s data. Data logs for the previous days are truncated.

If the value is greater than 0, the log data is retained for the number of days specified by the value.

The default value is -1, means logs won’t be deleted. so be careful that you change it appropriately. the log history contains validation history, transactional log history, ans staging batches history.

Data Compression Option for Entities

SQL Row Level Security helps to compress entity data and its index in a very good level to minimize the I/O operation. this would be specially helpful when the data values range are not so wide. There is however slightly increase in the CPU usage with this compression which needs to be considered when there are limitation on CPU.

In the screenshot above you see that Data Compression option is available for the entity.

Explicit Hierarchies are Deprecated

Well, I never used Explicit Hierarchies in my MDS projects, so I’m not so much concerned about deprecation of it. MDS had previously two kind of hierarchies: Explicit, and Derived. Derived hierarchies are good for set one to many relation between entities. explicit hierarchies are also good for that purpose, but they are build for specific purpose of dynamic level hierarchies. In the new version of MDS Explicit will be deprecated. Screenshot below mentioned that clearly.

Entity Dependencies tab in Web explorer

In previous version of MDS entity relationship could be created in Excel Add-in, but there was no way to see it in the Web UI. the new version of MDS have a tab in MDS explorer for it. Unfortunately I couldn’t manage to have a screenshot of how it works, as my Excel Add-In doesn’t work for MDS 2016, and I couldn’t built a relationship so I could illustrate it here. As soon as the problem solves I’ll put screenshot here for better understanding.

Change in Security Functions

Super User function is the new permission in SQL Server 2016 MDS, this permission works as Server Admin. You might think why this permission even exists? actually I had some troubles with prior version of MDS and the story was: In prior version of MDS the server admin access was given only to the user who originally configured MDS, and there was no way to change it! you might imagine that if that person left the company then how that permission should be assigned to someone else. Actually there was no way to change it through UI. It took me some time to figure out I should apply some changes directly in MDS database tables to change that security. Fortunately this new feature in MDS 2016 resolves that issue.

Detailed level of security on models

Detailed level security in prior version of MDS had three modes: Read-only, Update, and Deny. This method of permission limits you from setting create only, or update only securities. The new version of MDS however is much better with having six modes: Admin, Deny, Read, Create, Update, Delete.

Table below shows the mapping between permission in prior version of MDS and the 2016 version of it (source from MDS team blog post):

SQL Server Jobs for Maintenance Log and Index

MDS 2016 works with SQL Server Agent because it has couple of maintenance jobs that are for log and indexes.

Display Name for attributes such as Name and Code

You can define display name for columns in the new version of MDS, and that helps for changing display name of Code and Name which were unchangeable columns in prior versions of MDS.

Reza Rad

Trainer, Consultant, Mentor

Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.