We have reached the end of this tutorial and in this last chapter we will
look at some Master Data Services (MDS) best practices.
- 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
- 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.
- 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.
- 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.
- 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.
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
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.
Last Update: 7/18/2016