SQL Server Master Data Services Architecture

By: Siddharth Mehta

Overview

Master Data Services (MDS) is a SQL Server based Master Data Management (MDM) solution in the Microsoft technology stack. MDS organizes and manages data through a set of tools and an object model. It is important to study the architecture of MDS to understand how MDS deals with master data management and how it interoperates with the IT ecosystem of an enterprise. We will discuss this in this chapter.

Explanation

Before we start with the discussion of the MDS architecture, it's important to understand the MDM architecture. This will help us understand the rationale behind the design of the MDS architecture. One of the well known MDM architectures is the MDM Hub Architecture. With MDM, there are three types of hub architectures for managing master data: repository, registry, and hybrid. Consider reading this article on MDM Architecture before proceeding ahead with the rest of this tutorial. This will help to develop a detailed understanding of the MDM architecture and best practices.

Master Data Services is composed of the below mentioned components and tools:

• Master Data Services Configuration Manager, a tool you use to create and configure Master Data Services databases and Web applications.
• Master Data Manager, a web application you use to perform administrative tasks, and that users access to update data.
• Master Data Services Web service, which developers can use to extend or develop custom solutions for Master Data Services.

Master Data Services Configuration Manager

MDS Configuration Manager is the first tool that one would use while starting to work with MDS. This tool is used to specify settings for the MDS installation and configuration to start with. The three primary uses of this tool are to create or configure an MDS database, to create a web application and to enable integration with Data Quality Services.

The MDS database is the central repository that stores the entire MDS data. Below is a list of items that is stored and managed within the MDS database:

• Stores the settings, database objects, and data required by MDS.
• Contains staging tables that are used to process data from source systems.
• Provides a schema and database objects to store master data from source systems.
• Supports versioning functionality, including business rule validation and e-mail notifications.
• Provides views for subscribing systems that need to retrieve data from the database.

Master Data Manager Web Application

Master Data Manager is a web application used by administrators and developers. This web application comprises of the following features:

• Explorer - In this area, users can update data and work with MDS data structures.
• Version management - In this area, administrators can validate data, review and reverse transactions, create versioned copies of data, and flag versions of data for subscribing systems.
• Integration management - In this area, administrators can import data from staging tables and create subscription views to be used for subscribing systems.
• System administration - In this area, administrators can create a model and all its objects. They can also create business rules and create and deploy model packages.
• User and Group Permissions - In this area, administrators can grant user permission to functional areas.

Master Data Manager Web Service

Like any other web service, master data manager web service enables developers to programmatically access different features of master data services from applications. The services offered by MDS is classified into different categories which can be read here. A detailed explanation of this web service is beyond the scope of this tutorial. You can read more about using this web service here. In the next chapter we will look at the basics of Master Data Services regarding installation and data organization.

Additional Information

• Success of MDM depends upon how well we understand the business requirements versus the MDM solution architecture. Consider investing more time in studying MDM as well as the MDS architecture from the links shared above, before starting with the implementation.