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, or Analytical Management – Enable business users to manage the dimensions and hierarchies of DW / Data Marts (BI scenarios) for use in generating reports.  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?

Master Data Management in SQL Server 2012: Use case of managing Data warehouse Dimensions with Master Data Services

Master Data Services and Data Quality Services

Cleanse and Match Master Data by Using EIM

Tutorial: Enterprise Information Management using SSIS, MDS, and DQS Together

Master Data Management: Bringing Order to Chaos

Integrating Master Data Management and BI (part I)

Organizational Approaches to Master Data Management

About James Serra

James currently works for Microsoft specializing in big data and data warehousing using the Analytics Platform System (APS), a Massively Parallel Processing (MPP) architecture. Previously he was an independent consultant working as a Data Warehouse/Business Intelligence/MDM architect and developer, specializing in the Microsoft BI stack. He is a SQL Server MVP with over 25 years of IT experience.
This entry was posted in DQS, MDS/MDM, SQL Server 2012, SQLServerPedia Syndication. Bookmark the permalink.

7 Responses to Microsoft Master Data Services in SQL Server 2012

  1. Pingback: Master Data Management (MDM) Hub Architecture | James Serra's Blog

  2. Tino Tsuro says:

    Hi James,

    Thanks for the info. It seems to me as if there is some overlap between MDS and DQS. For example, I think one can deduplicate data in DQS and well as MDS (I have only done it it DQS). I think the same applies to data validation rules, they can be found in both technologies.

    Is this an overlap or do the two technologies leverage each other’s capabilities. If there is some overlap are there any guidlines on when to use either of the two technologies.

    Thanks,

    Tino

  3. Pingback: Profisee Master Data Maestro - SQL Server Blog - SQL Server - Telligent

  4. Pingback: Profisee Master Data Maestro | James Serra's Blog

  5. Pingback: Data cleansing in SSIS, DQS, and MDS - SQL Server - SQL Server - Toad World

  6. Pingback: Data cleansing in SSIS, DQS, and MDS | James Serra's Blog

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>