Recently I did some research to become more familiar with the new features of Master Data Services in SQL Server 2012, as well as what’s discontinued and deprecated. Below are my notes compiled from reading a variety of sources.
New MDS 2012 Features
New Excel Add-In
- A new Excel Add-In is introduced, which provides an optional alternative to working in the Web Interface. The Add-In can be downloaded here: http://www.microsoft.com/en-gb/download/details.aspx?id=35581
- Works on Excel 2007 forward. (Exception: To use the “Send Query” functionality on the Master Data ribbon, this requires Outlook 2010.)
New Shortcut Query Files
- After you have loaded MDS data into an Excel worksheet, you can use the “Save As Query” menu item on the Master Data ribbon. This will save a Shortcut Query File to be reused. There’s also a “Manage Queries” option to load, rename, or delete previously saved queries.
- Shortcut Query Files have the following advantages: (1) Saves time and improves accuracy when reloading data for a particular set of data on a regular basis, especially if you've placed filters on the set data. (2) Allows you to share the query with a coworker without having to email the data itself in the workbook – this is particularly helpful if it’s sensitive data. (3) By posting the Shortcut Query File to a SharePoint Document Library, data stewards may utilize it as a starting point within their own Excel workbook. This improves accuracy, consistency, and efficiency.
- File format of a Shortcut Query File is *.mdsqx XML format which can be imported and exported for ease in sharing between coworkers. If you execute the *.mdsqx file, it uses an MDSQueryOpener program (which opens Excel, runs the query, and renders the data).
Redesign of the Web Interface
- Master Data Manager now uses Silverlight 5.
- Advantages of the redesigned interface include: (1) It is faster. (2) Less page refreshes are required. (3) Adding, deleting & moving records is quicker. (4) The layouts are more consistent from screen to screen.
- Additional filtering capabilities have been added to the Web Interface (note: the Web UI offers a few more filtering capabilities than the Excel Add-In).
New Integration with Data Quality Services
- Purpose of the integration with DQS is to ensure no duplicate records exist. Integration is currently limited to this particular function.
- This functionality works only in the Excel Add-In. You can use the “Match Data” button on the Master Data ribbon (or if you set up a staging ETL process using SSIS). It’s not supported in the Web Interface with this release.
- Three requirements to use the DQS matching functionality: (1) The integration with DQS needs to be enabled in the MDS Configuration Manager – the Data Quality section of the Master Data ribbon is not visible until enabled. (2) A matching policy needs to exist within the DQS Knowledgebase. (3) Both MDS and DQS_MAIN need to exist on the same SQL Server instance.
Restructured Entity-Based Staging Structure
- Rather than using shared staging tables for use with ETL processing, each entity now has its own group of staging tables including stg.name_leaf, stg.name_consolidated, and stg.name_relationship.
- Advantages of having separate staging tables per entity: (1) The ETL runs more efficiently. (2) Security can be set up per individual table. (3) Members and attributes may be loaded in single batches.
- The stored procedures which populate your model from the staging tables support inserts, updates, and deletes. This is a HUGE improvement in the 2012 version, considering the 2008 R2 staging tables could not handle updates to existing attributes.
Simplified Security Model
- Derived hierarchies will inherit its permissions from the model. Security for a derived hierarchy can no longer be set explicitly.
- Explicit hierarchies will inherit its permissions from the entity. Security for an explicit hierarchy can no longer be set explicitly.
- Attribute Groups now have a new Attribute Group Maintenance page to assign update permissions. Read-only permissions can no longer be set.
New Deployment Command-Line Tool
- A new command-line tool called “MDSModelDeploy” is introduced. This allows deployment of not only the model structure, but the data as well. (Note: the Web Interface only supports deployment of the structure, not the data.)
- After deployment, the following items must be manually updated: (1) User-defined metadata, (2) File attributes, (3) User and Group permissions.
New Option to Create Code Values Automatically
- A new option exists to have Code Values created automatically when a new member is added to an entity. In the previous version of MDS, this could be accomplished via a business rule. However, this new option under Manage>Entities is much more convenient because it takes effect as soon as the member is created.
- Collections now may have a weight assigned to each item.
- If you add the MDS Web Interface to a page with a SharePoint portal, you can add “&hosted=true” as a query parameter to the URL string. This reduces the space required to display Master Data Manager.
- Installation of Master Data Services is now part of SQL Server.
- Master Data Services is available in the Business Intelligence or Enterprise editions.
Discontinued MDS 2012 Features
- One side-effect of the Web Interface improvements (see the Redesign of Web Interface section above) is that batch updates are no longer supported. Of course, doing updates one by one is not efficient if you have a large number of members to update. Your alternatives for handling a large number of updates is the Excel Add-In or a staging ETL process.
- The new staging process does not support maintenance of Collections, nor does the Excel Add-In. Collections may only be maintained via the Web Interface.
- Business Rules to generate Code Values are no longer supported. Alternatively, you will want to use the new feature when the entity is set up (see the New Option to Create Code Values Automatically section above).
- The ability to explicitly secure hierarchies and attribute groups has been eliminated. (See the Simplified Security Model section above.)
- Attribute Groups can no longer have read-only permissions (update permissions only).
- The Web Interface no longer displays an “Export to Excel” button. Instead of pushing the data from MDS to Excel, you may use the Excel Add-In to pull the data from MDS to Excel.
- Users (i.e., a typical data steward) no longer have the ability to reverse their own transactions. Reversal of an MDS transaction now requires an administrator.
- Annotations cannot be deleted. Rather, annotations are now retained permanently.
- Powershell cmdlets are no longer available in this release.
Deprecated MDS 2012 Features
- The consolidated staging table approach as used with SQL Server 2008 R2 will be removed from a future version. This includes tblStgMember, tblStgMemberAttribute, and tblStgRelationship plus the udpStagingSweep stored procedure. If you go with Upgrade Choice #2 (see Upgrade Considerations below), you can still use the 2008 R2 staging processes at this point in time. However, they will be likely removed in a future release.
- The Metadata model will likely be removed in a future release. Although you can see it, it shouldn’t be used for anything.
When you are ready to upgrade MDS from SQL Server 2008 R2 to SQL Server 2012, you need to make a decision if you are ready to upgrade the database engine or not. There are two approaches:
- Upgrade the Web Application and the MDS database to SQL Server 2012.
- Upgrade the Web Application, but leave the MDS database at SQL Server 2008 R2. With this choice, the database schema is updated to support new features, and you can use the new Web Interface. You may continue using the 2008 R2 processes (such as the consolidated staging format, or the management of Collections via the staging table structure) as-is until you are ready to redesign them. A downside to this approach is that you don’t see all staging processes displayed within the Web Interface. It does buy you some time to redesign though, which is helpful if you have a lot of MDS SSIS packages.
Finding More Information
MSDN – Upgrade Master Data Services
Jeremy Kashel’s Blog – Master Data Services SQL Server 2012 Vs 2008 R2
Ross Mistry and Stacia Misner - Introducing Microsoft SQL Server 2012