SQL Server Master Data Services Overview


An enterprise is formed of a number of organizations that generate data relevant to its own domain. Common examples of such organizations are HR, Finance, Compliance, Security, Sales, Mobility, Training and others. Large scale enterprises would have their own subsidiaries which would be composed of multiple organizations. For example, consider a multinational company that operates out of different geographies under different brand names. Each arm of the enterprise in each geography would have its own HR, Finance, Sales and other organizations. As the scale of the diverse forms of data in a business increases, the need to bind these data under a common umbrella becomes even more compelling.

Individually these data may not have the required relevance. For example, for the finance department to process payroll, they would need employee information from HR to know his/her career level and tenure with the organization. A project management team would need to engage with the training department to maintain skills inventory of the employee. The Sales and Finance department would have to engage with HR to derive the cost and margins for any given business deals. These requirements highlight the need for a common set of master data to be maintained in a centralized repository that can be accessed by every section of the organization. In general, the process of governing as well as managing master data using a set of standard processes, tools and business rules can be termed as master data management (MDM).

Master data services (MDS) is the master data management solution from the Microsoft Business Intelligence (BI) technology stack. In this tutorial, we will start with the discussion of scenarios and considerations for MDM. Having understood the situations where MDM would be fit, we would understand the SQL Server Master Data Services (MDS) architecture that facilitates MDM. Before we proceed with a deep-dive into MDS, we need to understand the installation and configuration of MDS, followed by the fundamental concepts and terminology in MDS.

Building on the foundation we build in these chapters, we will proceed with a business scenario and develop a blank MDS model that caters to the MDM needs of the business. Once we have the model ready, we will look at different methods of loading data into the newly developed model. Having the data at hand, we will address dealing with complicated constructs like hierarchies and collections. Data has to be validated against business rules, and the same needs to be implemented in MDS to ensure valid business data. We will also learn about implementing business rules in MDS. Once the data is ready in MDS, it needs to be opened up for consumption by subscribers. We will look at the editing process of master data by data stewards as a part of data governance, as well as publishing the finalized data to subscribers.

Towards the end of this tutorial we will look at some of the MDS best practices to keep in mind while working with MDS. The agenda of the tutorial is mentioned below. This tutorial is aimed at implementing the approach of MDM in an enterprise using MDS as the tool. Now that we have understood the course of this tutorial, let's get started.

  1. Master Data Management
    • MDM Considerations
    • MDS Architecture
  2. MDS Basics
    • MDS Installation and Configuration
    • MDS Fundamentals and Terminology
  3. Data Modeling
    • Developing MDS Model
    • Loading data into MDS
  4. Data Management
    • Constructing master data structure
    • Developing Business rules
  5. Data Provisioning
    • Editing and Publishing Data
    • Best Practices

Comments For This Article

Thursday, December 1, 2016 - 10:36:23 AM - Sarada Back To Top (44874)

 In  cross database and distributed transactions setup.  Instead of using availability groups, if we use always on with failover cluster, will cross DB transactions and distributed transaction (MSDTC) work. If we implement always on without availability group, will these transactions work. If so which version of SQL server will support and setup we need to configure. Pls suggest.


get free sql tips
agree to terms