Blog Post

Microsoft Master Data Services in SQL Server 2012


Microsoft SQL Server Master Data Services (MDS) is a Master Data Management (MDM) product from Microsoft.  Master Data Services is the rebranding of the Stratature MDM product titled +EDM, which Microsoft acquired in June 2007.  Master Data Services is architecturally similar to +EDM, with increased integration with other Microsoft applications as well as some new features.  Master Data Services first shipped with Microsoft SQL Server 2008 R2.  The next version of Master Data Services is in Microsoft SQL Server 2012, which has major changes to the analytical and transactional capabilities, as well as better integration with other products such as Data Quality Services (DQS) (see SQL Server 2012 (“Denali”): Master Data Services).

So what exactly is MDM?

MDM, or master data management, can be described as the technology, tools, and processes required to create and maintain consistent and accurate lists of master data.  MDM is not just a technological problem.  In many cases, fundamental changes to business process will be required to maintain clean master data, and some of the most difficult MDM issues are more political than technical.  MDM includes both creating and maintaining master data.  Investing a lot of time, money, and effort in creating a clean, consistent set of master data is a wasted effort unless the solution includes tools and processes to keep the master data clean and consistent as it is updated and expanded.  The end results is you have “golden” records that provide you with a single version of the truth in one location.

MDM can also be described by the way that master data interacts with other data.  For example, in transaction systems, master data is almost always involved with transactional data.  A customer buys a product.  A vendor sells a part, and a partner delivers a crate of materials to a location.  An employee is hierarchically related to their manager, who reports up through a manager (another employee).  A product may be a part of multiple hierarchies describing their placement within a store.  This relationship between master data and transactional data may be fundamentally viewed as a noun/verb relationship.  Transactional data capture the verbs, such as sale, delivery, purchase, email, and revocation; master data are the nouns.  This is the same relationship data-warehouse facts and dimensions share.

So what is Master Data Services?

Master Data Services (MDS) helps organizations standardize and streamline the business data customers use across their organization to make critical business decisions.  MDS is a Master Data Management (MDM) application built from platform components which may be deployed as an application or extended by use of the platform components to consistently define and manage the critical data entities of an organization.  MDS is an any-domain hub that supports but is not limited to domains such as product, customer, location, cost center, equipment, employee, and vendor.

Using MDS, customers can manage critical data assets by enabling proactive stewardship, enforcing data quality rules, defining workflows around data changes, notifying impacted parties, managing hierarchies, and sharing the authoritative source with all impacted systems.

MDS has many features such as modeling, hierarchies, business rules, workflows, security, auditing, versioning, and integration.  SQL Server Integration Services (SSIS) is frequently used along with MDS to help build the MDS entities.

So what is DQS?

Before adding more data to MDS, you can now “clean” the data by performing such tasks as confirming that you aren’t adding duplicate records.  The MDS Add-in for Excel uses SQL Server Data Quality Services (DQS) to compare two sources of data: the data from MDS and the data from another system or spreadsheet.  DQS provides suggestions for updating your data, along with the percent of confidence that the changes are correct.

Main scenarios for using MDS:

Operational Data Management – Central data records management and consumption sourced by other operational systems.  For example, propagating a correct customer master to many internal systems all from different vendors.  The main purpose of Operational Data Management is pushing data out from MDS.

Data Warehouse / Data Marts Management – Enable business users to manage the dimensions and hierarchies of DW / Data Marts (BI scenarios).  For example, building a customer dimension in the data warehouse that uses as its source many customer lists from multiple internal sources (i.e. ERP, CRM, etc).  All these separate systems usually can’t import a master list.  So they use their own lists and updates are fed into MDS.  The main purpose of Data Warehouse / Data Marts Management is pulling data into MDS.

Data Solutions – Provides storage and management of the objects and metadata used as the application knowledge (Object mappings, Reference Data / managed object files, Metadata management / data dictionary).  For example, managing a table containing information on mapping objects between different systems that is used by an ETL process to make transformation decisions.

More Info:.

Master Data Services in SQL Server 2012

Introduction to Master Data Services

Video Empowering IW / Data Experts to Efficiently Manage Important Data with the New Master Data Services Add-In for Excel

Video Successfully implement Master Data Services (MDS)

Video Enterprise Information Management (EIM): Bringing Together SSIS, DQS, and MDS

MDS 2012: Part 1 Understanding Master Data

The What, Why, and How of Master Data Management

Master Data Management (MDM) Hub Architecture

What Can SQL Server Master Data Services Do For You?


You rated this post out of 5. Change rating




You rated this post out of 5. Change rating