SQL Server Master Data Services Best Practices

By: Siddharth Mehta

Overview

We have reached the end of this tutorial and in this last chapter we will look at some Master Data Services (MDS) best practices.

Explanation

1. Data modeling in Master Data Services or any Master Data Management (MDM) solution in general should be driven by the conceptual data model instead of subscribing systems. If one develops the MDM model by collecting the requirements of subscribing systems, and building those models in MDS, it won't help the purpose of MDM in the long run. Modeling data this way would make MDS just an extended reference repository instead of a solution that is purposed with managing the master data as well as organizational structures.
2. Data should be managed as discretely as possible. For example, an employees address might be stored as a single field in a source system. But while sourcing the data in MDS, it might make sense to separate this single attribute in multiple attributes and storing each part of address in respective fields like AddressLine1, AddressLine2, City, State, Country, Zip, etc. This allows accurate and detailed level data management.
3. The model in MDS is a reflection of the businesses viewpoint of organizational structure and business entities. It is recommended to get a vote of confidence from the business leads. Subscribers should not be made the key stakeholders of the MDM requirements as subscriber requirements should not drive the MDM data model. Subscribers should be the key stakeholder in the publishing process of MDM solution to ensure that the correct interface and process is in place such that subscribers can efficiently consume data from MDS.
4. Generally change tracking is not required for each and every entity and attribute. If your source system is a data warehouse employing Type 2 dimensions, you may frequently encounter data changes for which change tracking is a must for certain attributes. Use change tracking selectively to track history.
5. Consider using data quality tools for data cleansing using organizational knowledge base and rules. This has the potential to lessen the cleansing you may need to do in your ETL jobs while sourcing data from source systems into your staging tables. The logic to cleanse this data might already exist in Data Quality Services (DQS), so consider leveraging this logic if available.
   

Summary

We started this tutorial with the topic of Master Data Management concepts as well as the basics of Master Data Services architecture. After learning the MDS terminology, we installed MDS and sample models, followed by development of basic database objects. After populating these objects with master data using a manual as well as a staging table driven approach, we looked at advanced options like hierarchies and business rules. Finally we versioned and published the master data and studied the schema of subscription views. Towards the end of this tutorial, we looked at some of the MDS best practices.

MDM is a vast subject and MDS is the Microsoft solution to implement this. The success of a MDM solution depends on the way the solution is implemented using the right constructs for the right requirements. A custom built MDM solution is equivalent to new product development, which requires extensive effort and resources for development, training, adoption as well as maintenance. MDS is a part of SQL Server Enterprise Edition and allows implementing an MDM solution using industry standards with advanced data constructs.

I suggest implementing MDS with a modest scale in case you have never considered an MDM solution. With increase in the scale of master data, you can consider expanding the scale of the MDS implementation. In order to realize the potential of MDM and MDS as something more than just a look-up / reference repository, one needs to understand the business significance of MDM in an enterprise. I hope throughout this tutorial, you were able to see different aspects of a MDM solution using MDS as the implementation tool, and you found a reasonable use of MDS in your day to day practice area.