What's New in Master Data Services (MDS)
This topic summarizes the changes and updates in the SQL Server 2017 release of Master Data Services.
For an overview of how you organize data in Master Data Services, see Master Data Services Overview.
To install Master Data Services, set up the database and Website, and deploy the sample models, see Master Data Services Overview (MDS).
To download SQL Server 2016 (13.x), go to Evaluation Center.
Have an Azure account? Then go Here to spin up a Virtual Machine with SQL Server 2017 already installed.
Performance improvements enable you to create larger models, load data more efficiently, and get better overall performance. This includes improvement the performance of the add-in for Microsoft Excel has been improved to decrease data load times and enable the add-in to handle larger entities.
For more information about the add-in for Microsoft Excel, see Master Data Services Add-in for Microsoft Excel.
The following feature improvements are included.
There is data compression on the entity level, which by default is enabled. When data compression is enabled, all the entity related tables and indexes are compressed with SQL Row Level compression. This significantly reduces the disk I/O when reading or updating the master data, especially when the master data has millions of rows and/or has a lot of NULL value columns.
Because there is a slight increase in the CPU usage on the SQL Server engine side, if you have CPU bound on the server you can turn off data compression by editing the entity.
The Dynamic Content Compression IIS feature is enabled, by default. This significantly reduces the size of the xml response and saves the network I/O, though CPU usage is increased. If you have CPU bound on the server, you can turn off data compression by adding the following setting to the Master Data Services Web.config file.
<configuration> \<system.webServer> <urlCompression doStaticCompression="true" doDynamicCompression="false " /> \</system.webServer> </configuration>
For more information, see URL Compression
The following new SQL Server Agent jobs do index and log maintenance.
By default the MDS_MDM_Sample_Index_Maintenance job runs weekly. You can modify the schedule. You can also manually run the job at any time by using the udpDefragmentation stored procedure. It is recommended that you run the stored procedure each time a large volume of master data is inserted or updated, or after a new version is created from the existing version.
An index with more than 30% fragmentation is rebuilt online. During the rebuild, the performance is affected on the CRUD operation on the same table. If performance degradation is a concern, it is recommended that you run the store procedure during off business hours. For more information about index fragmentation, see Reorganize and Rebuild Indexes.
For more information, see this post on the Master Data Services Blog, Performance and Scale Improvement in SQL Server 2016.
The new Super User function permission gives a user or group the same permissions as the Server Admin in the previous release of Master Data Services. The Super User permission can be assigned to multiple users and groups. In the previous release, the user who originally installed Master Data Services was the server admin, and it was difficult to transfer this permission to another user or a group. For more information, see Functional Area Permissions (Master Data Services).
A user can now explicitly be assigned the Admin permission at the model level. This means that if the user is later assigned permissions in the model subtree, such as the entity level, he will not lose this Admin permission.
In this release of Master Data Services, we're providing more levels of permissions by introducing the following new permissions: Read, Create, Update and Delete. For example, a user that has only the Update permission can now update the master data without creating or deleting the data. When you give a user the Create, Update or Delete permission, the user is automatically assigned the Read permission. You can also combine the Read, Create, Update and Delete permissions.
When you upgrade to SQL Server 2016 (13.x)Master Data Services, old permissions are converted to new permissions as shown in the following table.
|Permission in previous release||New permission|
|User who originally installs Master Data Services has the Server Admin permission.||User has the Super User function permission|
|User has Update permissions at the model level and no permissions in the model subtree, and so is implicitly a model admin.||User has explicit Admin permissions at the model level.|
|User has Read-only permissions.||User has Read access permissions.|
|User has Update permissions.||User has all four access permissions: Create, Update, Delete, and Read.|
|User has Deny permissions||User has Deny permissions|
For more information about permissions, see Security (Master Data Services).
Improved Transaction Log Maintenance
You can now clean transaction logs at pre-determined intervals or on a schedule, using the System settings and at the model level. For a MDS system with lot of data changes and ETL processes these tables can grow exponentially and lead to performance degradation and storage space issues.
The following types of data can be removed from the logs.
Transaction history older than a specified number of days.
Validation issues history older than a specified number of days.
Staging batches that ran before a specified number of days.
You can configure the frequency that data is removed from the transaction logs, using the System settings and at the model level. For more information, see System Settings (Master Data Services)and Create a Model (Master Data Services). For more information about transactions, see Transactions (Master Data Services).
The SQL Server Agent job, MDS_MDM_Sample_Log_Maintenace, triggers cleanup of the transaction logs and runs every night. You can use SQL Server Agent to modify the schedule for this job.
You can also call stored procedures to clean the transaction logs. For more information, see Transactions (Master Data Services).
In SQL Server 2016 (13.x)Master Data Services, features have been added to improve debugging and make it easier to troubleshoot issues. For more information, see Tracing (Master Data Services).
Improvements in manageability help to lower maintenance costs and positively impact your return on investment (ROI). These improvements include transaction log maintenance and improvements to security, as well as the following new features.
Using attribute names that are longer than 50 characters.
Renaming and hiding Name and Code attributes.
For more information, see the following topics.
Business Rule Improvements
Manage Business Rules (MDS Add-in for Excel)
In the Master Data Services Add-in for Excel you can manage Business Rules, such as creating and editing business rules. Business rules are used to validate data.
Business Rules Extension
You can apply user-defined SQL scripts as an extension of business rule conditions and actions. SQL functions can be used as a condition. SQL stored procedures can be used as an action. For more information, see Business Rules Extension (Master Data Services).
Business Rule Management Experience Redesigned
The business rule management experience in MDS has been completely redesigned to improve the experience. For more information about this feature, see Business Rules (Master Data Services).
Business Rule Management Functionality Removed from the MDS Add-in for Excel
Business Rule management functionality has been removed from the MDS Add-in for Excel because we redesigned the experience.
New Business Rule Conditions
Seven new business rule conditions were added to provide a complete set of conditions. For more information, see Business Rule Conditions (Master Data Services).
Derived Hierarchy Improvements
Many-to-Many Relationships in Derived Hierarchies
You can now create a Derived Hierarchy that displays many-to-many relationships. A many-to-many relationship between two entities may be modeled through the use of a third entity that provides a mapping between them. The mapping entity is an entity that has two or more domain-based attributes referencing other entities.
For example, entity M has a domain-based attribute that references A and a domain-based attribute that references B. You can create a hierarchy from A to B using the mapping entity.
For more information, see Show Many-to-Many Relationships in Derived Hierarchies (Master Data Services)
Edit Many-to-Many Relationships in Derived Hierarchies
You can edit the many-to-many relationship by modifying the mapping entity members. For more information, see Show Many-to-Many Relationships in Derived Hierarchies (Master Data Services).
Derived Hierarchy Management Experience Improved
The derived hierarchy management experience in MDS has been improved. For more information about this feature, see Create a Derived Hierarchy (Master Data Services).
Business Rule management functionality has been removed from the MDS Add-in for Excel because we redesigned the experience.
You can create a nonclustered index on one attribute (single index) or on a list of attributes (composite index), in an entity, to help improve the query performance. For more information, see Custom Index (Master Data Services).
For a domain-based attribute, for a leaf member, you can use a filter parent attribute to constrain the allowed values for the domain-based attribute. For more information, see Create a Domain-Based Attribute (Master Data Services).
Entity and Member Improvements
Entity Sync Relationship
You can share entity data between different models by creating an entity sync relationship. For more information, see Entity Sync Relationship (Master Data Services).
Purge Soft Deleted Members
You can now purge (permanently delete) all soft-deleted members in a model version. Deleting a member only deactivates, or soft-deletes, the member. For more information, see Purge Version Members (Master Data Services).
Improvements for Managing Changes
Member Revision History
A member revision history is recorded when a member is changed. You can rollback a revision history, as well as view and annotate revisions. Using the Log Retention Days property, you can specify how long historical data is retained. For more information, see Member Revision History (Master Data Services).
If you try to publish data that has been changed by another user, the publish will fail with a conflict error. To resolve this error, you can perform merge conflicts and republish the changes. For more information, see Merge Conflicts (Master Data Services) and Merge Conflicts (MDS Add-in for Excel).
You can use change sets to save pending changes to an entity, and you can view and modify pending changes. If the entity requires approval for changes, you must save the pending changes into a change set and submit for approval by the administrator. For more information, see Changesets (Master Data Services).
Change Set Email and Management
In this release, you can now view and manage all changes by model and version. You can also receive email notifications each time a change set status changes for an entity that requires approval. For more information, see Manage Changesets (Master Data Services) and Notifications (Master Data Services).
View and Manage Revision History
You can view and manage revision history, by entity and by member. If you have update permissions, you can roll back a member to a previous version. For more information, see Member Revision History (Master Data Services).
Tool and Sample Improvements
Save or Open Query Files in MDS Add-in for Excel
From the Entity Explorer page, you can click Excel to save the shortcut query files. Or you can open the query file stored on your computer, in the MDS Add-in for Excel. The saved file can be opened using QueryOpener application. For more information, see Shortcut Query Files (MDS Add-in for Excel).
The query file contains the filters and hierarchy information from the explorer page.
Sample Model Deployment Packages Updated
The sample packages were updated to support new scenarios. For more information, see SQL Server Samples: Model Deployment Packages (MDS).
- UserVoice: Have suggestions for improving SQL Server?
- Set up and upgrade: MSDN Forums
- SQL Server Data Tools: MSDN Forums
- Transact-SQL: MSDN Forums
- SQL Server Security: MSDN Forums
- DBA Stack Exchange (tag sql-server): Ask SQL Server questions
- Stack Overflow (tag sql-server): Answers to SQL development questions
- Reddit: General discussion about SQL Server
- Microsoft SQL Server License Terms and Information
- Support options for business users
- Contact Microsoft
- Additional SQL Server help and feedback
Contribute to SQL documentation
Did you know that you could edit the content yourself? If you do so, not only will our documentation improve, but you'll also be credited as a contributor to the page.