Learn more about SQL Server tools

mssqltips logo
giveaway
 

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 1


By:   |   Read Comments (2)   |   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 gives a comprehensive overview of all the new features and links back to the original tips for more details.

Solution

User Interface Redesign

One of the more pleasant features is the redesign of the user interface of the MDS website. It has an updated look, sports a tile view at the home page and just looks like something that has been built this century. An advantage is that the website now beautifully renders in most browsers, instead of just Internet Explorer.

MDS Home Page

Unfortunately, not every section of the website has already received its redesign. Some are still in the old layout. Most noticeably is the Explorer, which keeps to be rendered in Silverlight. This means browsers other than Internet Explorer possibly continue to have issues with the Explorer.

MDS Explorer Silverlight

Performance Improvements

A lot of effort has been put into making MDS faster than its predecessors. This means you can create larger models and load data faster into MDS, but also querying MDS might have better performance. The following improvements have been made:

  • The MDS Excel add-in has been improved to reduce data load times and to enable to handle much larger entities.
  • You can enable data compression on an entity in order to reduce compress the data within the entity (and its indexes). This leads to less storage being used, but also less I/O traffic when reading data, but a bit more CPU usage in order to compress or decompress the data. MDS uses only row compression. Sadly page compression isn't used, which would give even more storage savings, especially since data in MDS typically compresses well due to redundancy (e.g. in domain based attributes). When creating new entities, they are by default compressed. You can read more about compression in MDS in the tip Using Data Compression in Master Data Services 2016.
MDS row level compression is enabled by default
  • MDS uses the Dynamic Content Compression feature of IIS. This reduces the size of XML responses which limits network traffic. As with data compression, CPU usage is increased. If you want to disable this feature, you need to modify the MDS web.config file:
<configuration>  
   <system.webServer>  
      <urlCompression doStaticCompression="true" doDynamicCompression="false " />
   </system.webServer>
</configuration>
  • You can create custom indexes on an attribute of an entity. This can be one single attribute, but also a list of attributes which leads to a composite index. You can also mark an index as unique, which can help forcing uniqueness in an entity. Keep in mind MDS already creates quite some indexes on an entity, especially if domain based attributes are used. If you want to know more about custom indexes, please refer to Add a Custom Index in Master Data Services 2016.
adding a custom index in MDS
  • Two maintenance jobs have been added: one for cleaning up the transaction log and one for index maintenance. Both are implemented as SQL Server Agent Jobs. The index maintenance job performs some partition management as well (only relevant if you use multiple versions for your models). If an index is more than 30%, it is rebuilt online.
maintenance jobs in MDS

The transaction log maintenance job will clean up the following types of data: transaction history, validation issues and staging batches. You can configure the number of days for which the log has to be kept for each model. If you don't configure a value for a model, the system default is taken, which is configured using the MDS Configuration Manager. The default is -1, meaning all logs will be kept.

configure log retention in MDS

The blog post Master Data Services 2016 Performance Enhancements by the MDS team details a test scenario where the performance of MDS 2016 is compared with MDS 2014 SP1. Some operations have over 2000% performance improvement!

Improved Security

MDS 2016 brings a lot of changes to the security model.

  • Some roles have been made explicitly, such as the super user role, the model administrator and the entity administrator.
  • Permissions can now be set on a more granular level. While previous versions only had read, update and deny, you can now choose between the permissions create, read, update, delete and deny. If a user is assigned create, update or delete, the read permission is automatically granted.
security matrix in MDS

You can find more info about MDS security in the tip Permission Changes in Master Data Services 2016.

Improved Troubleshooting

MDS introduces a couple of changes in order to improve debugging and troubleshooting. Most noticeably are the new trace capabilities, which are configured in the web.config file. The trace writes by default warnings and activity tracing to log files. You can find these in C:\Program Files\Microsoft SQL Server\130\Master Data Services\WebApplication\Logs. You can find an example of how I used the tracing to troubleshoot a business rule error in the blog post Database Error has Occurred when Publishing Business Rule. You can find more information about trace settings in the MSDN page Tracing (Master Data Services).

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





More SQL Server Solutions











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     



Monday, April 03, 2017 - 2:54:56 AM - Koen Verbeeck Back To Top

@Nick: I totally agree. I hope all the Silverlight components are removed in future versions of MDS.


Thursday, March 30, 2017 - 8:53:19 PM - Nick McDermaid Back To Top

It's very disappointng that all the really useful maintenance components are still in silverlight. This has made this web tool pretty useless as silverlight is not a supported add in in any modern browser (Edge, chrome). It's a shame because MDM is a key component of a true enterprise data platform. I'm just suprised that Microsoft started fresh with Edge but one of it's flagship products (SQL Server) ships with a component that can't be used in it.


Learn more about SQL Server tools