BUSINESS INSIGHTS

Sep 06, 2016

Evaluating SQL Server 2016 Master Data Services for Enterprise MDM

Leo Furlong Posted by Leo Furlong

Master data management (MDM), the creation of a single reference point for the whole of a company’s critical information, is increasingly becoming a necessity. Master data governance (MDG) helps ensure the safety of those assets across the organization. While master data management may not be the most exciting of data management solution principles, when coupled with master data governance it can create major business value for an organization

evaluate-sql-server-mds.jpg
Microsoft’s Master Data Services (MDS) in SQL Server 2016 can be an excellent technology platform to implement MDM and MDG solutions. Originally released with Microsoft SQL Server 2008 R2, MDS has come a long way over the years. We recently finished a project with an enterprise retail customer who was evaluating MDS as a platform and home for master and reference data needing migration from a legacy mainframe environment.  We evaluated MDS across several different candidate data models and here is what we found.

What Worked

  1. Rapid data model creation and data loading We were able to quickly create data models and load data using several different methods including batch loads via Extract, Transform and Load (ETL), bulk imports through the Excel add-in, and singular imports through the application interface.
  2. Data integrity enforceable using multiple mechanisms There are many ways to enforce data integrity in the models.  We were able to use a combination of data types, domain-based attributes and filters, synched entities, and a robust business-rules matrix to enforce data integrity.  Notification emails were sent to model administrators when the data was out of line.
  3. Ability to track data history in the application and easily view it MDS provides the capability to see the history for all members of any entity within models.  All history is stored by default, but you can configure MDS to purge history after a configurable window to enhance performance of large models. 
  4. Robust security framework We were able to work in multiple roles including those of Data Owners, Data Stewards, and Data Subscribers.  For each role, we were able to specify distinct permission sets on models, entities, attributes, and even member slices (only able to see data in a certain department as an example). 
  5. Price!  The price is fantastic – it’s FREE when using MDS in an existing SQL Server 2016 Enterprise Edition instance.  You’ll want to analyze that the extra overhead of MDS on your servers isn’t going to push it over the edge.

What Was Clunky

  1. Notifications can be overwhelming While notification emails from MDS is a great feature, many groups found the volume of emails overwhelming, especially in DEV or QA where the Data Stewards are working through the data, cleaning it up, and executing new and existing business rules repeatedly.  We found that it was best to limit the audience of notifications to those who really need to take action on the data.  
  2. Approvals feel awkward Approvals via changesets are a new feature of MDS in SQL Server 2016.  The capability allows the creation of a light workflow approval process within MDS.  The feature is much appreciated and of value, but it definitely feels a little awkward to work through and it takes some getting used to.  We’ll spare you the finer details, but we ultimately decided to use these features in certain scenarios and appreciated them once we mastered them.

What’s Missing

Going into this proof of concept, we informed the retailer that Master Data Services lacked certain capabilities regarding data quality integration, golden records management and advanced workflow creation, as we detail below:

  1. Truly Integrated Data Quality The ability to perform data cleansing, address, email, and contact verification via built-in web service connectors right in the MDM console – not via another application. 
  2. Golden Records Management The capability to match, de-duplicate, and harmonize records from multiple source applications with MDM to create a single truth to your master data. 
  3. Advanced Workflows The ability to create advanced, multi-step, and/or sophisticated workflows that incorporate multiple actors in order to complete data entry, update, or cleansing operations. 

Microsoft does offer some of these functions through its Data Quality Services (DQS) product.  While some of DQS’s capabilities are integrated into Master Data Services, some are standalone. 

At BlueGranite we recommend evaluating Profisee when a customer needs a full-featured master data management solution.  Profisee is a bolt-on MDM solution that leverages MDS as a backend platform and it provides a very rich and fully capable MDM, data quality, and workflow capability.

MDS was a success!

Overall, the evaluation successfully proved SQL Server 2016 Master Data Services’ business value and the company should be moving forward with implementation.  This customer is aware of what MDS is missing but, for the data models in scope, the evaluation proved that the missing capabilities weren’t an issue. 

With the releases of SQL Server 2016, Master Data Services is better than ever.  There are several new features that make MDS worth reevaluating or even implementing if you haven’t looked in a while.  Some of the new features are:

  • Advanced hierarchies including Recursive and Many-to-Many hierarchies
  • Domain Based Filtering – the ability to have chained domain-based attributes
  • Entity synching for sharing entities between models
  • Approval workflows via a new feature called “changesets”
  • Improved user interface (UI) and overall application performance – including redesigned UIs, data compressions, enhanced logging options, purging soft deletes, and faster data imports
  • More robust security capabilities
  • Business rules UI is simpler and easier to use
  • Ability to create custom indexes

We’d love to help you evaluate whether Master Data Services can benefit your organization. Contact us and we'll help you get started with your own evaluation of SQL Server MDS for your enterprise master data management needs.  

New Call-to-action
Leo Furlong

About The Author

Leo Furlong

Leo is a Solution Architect at BlueGranite. He is an experienced Data and Analytics Architect with extensive experience implementing Digital Transformation Strategy, using on-premise and Azure cloud technologies. Leo is also comfortable in Management Consulting roles around Data and Analytics or leading and contributing toward deliverables across the entire project lifecycle.

Latest Posts

New Call-to-action