SQL Server Master Data Services Overview
By: Siddharth Mehta
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.
- Master Data Management
- MDM Considerations
- MDS Architecture
- MDS Installation and Configuration
- MDS Fundamentals and Terminology
- Developing MDS Model
- Loading data into MDS
- Constructing master data structure
- Developing Business rules
- Editing and Publishing Data
- Best Practices