Microsoft SQL Server 2008 R2 Master Data Services Overview

Microsoft SQL Server 2008 R2 Master Data Services

Master Data Services overview

Master Data Services is Microsoft’s Master Data Management product that ships with SQL Server 2008 R2. Much like other parts of SQL Server, such as Analysis Services (SSAS) or Reporting Services (SSRS), MDS doesn’t get installed along with the database engine, but is a separate product in its own right.

Unlike SSAS or SSRS, it’s worth noting that MDS is only available in the Enterprise and Data Centre Editions of SQL Server, and that the server must be 64-bit.

MDS is a product that has grown from acquisition, as it is based on the +EDM product that Microsoft obtained when they bought the Atlanta-based company called Stratature in 2007. A great deal of work has been carried out since the acquisition, including changing the user interface and putting in a web service layer.

At a high level, the new product has the following features:

• Entity maintenance—MDS supports data stewardship by allowing users to add, edit, and delete members. The tool is not specific to a particular industry or area, but instead is generic enough to work across a variety of subject domains.
• Modeling capability—MDS contains interfaces that allow administrative users to create data models to hold entity members.
• Hierarchy management—Relationships between members can be utilized to produce hierarchies that users can alter.
• Version management—Copies of entity data and related metadata can be archived to create an entirely separate version of the data.
• Business rules and workflow—A comprehensive business rules engine is included in order to enforce data quality and assist with data stewardship via workflow. Alerts can be sent to users using e-mail when the business rules encounter a particular condition.
• Security—A granular security model is included, where it is possible, for example, to prevent a given user from accessing certain entities, attributes, and members.

Master Data Services architecture

Technically, Master Data Services consists of the following components:

• SQL Server Database—The database holds the entities such as Customer or Product, whether they are imported from other systems or created in MDS.
• Master Data Manager—A web-based data stewardship and administration portal that amongst many other features allows data stewards to add, edit, and delete entity members.
• Web Service Layer—All calls to the database from the front-end go through a WCF (Windows Communication Foundation) web service. Internet Information Services (IIS) is used to host the web services and the Master Data Manager application.
• Workflow Integration Service—A Windows service that acts as a broker between MDS and SharePoint in order to allow MDS business rules to use SharePoint workflows.
• Configuration Manager—A windows application that allows key settings to be altered by an administrator.

The following diagram shows how the components interact with one another:

MDS SQL Server database

The MDS database uses a mix of components in order to be the master data store and to support the functionality found in the Master Data Manager, including stored procedures, views, and functions.

Separate tables are created both for entities and for their supporting objects, all of which happens on the fly when a new object gets created in Master Data Manager. The data for all entities across all subject areas are stored in the same database, meaning that the database could get quite big if several subject domains are being managed. The tables themselves are created with a code name. For example, on my local installation, the Product entity is not stored in a table called “Product” as you might expect, but in a table called “tbl_2_10_EN”.

Locating entity data
The exact table that contains the data for a particular entity can be found by writing a select statement against the view called viw_SYSTEM_SCHEMA_ENTITY in the mdm schema.

As well as containing a number of standard SQL Server table-valued and scalar functions, the MDS database also contains a handful of .Net CLR (Common Language Runtime)-based functions, which can be found in the mdq schema. The functions utilize the Microsoft.MasterDataServices.DataQuality assembly and are used to assist with data quality and the merging, de-duplication, and survivorship exercises that are often required in a master data management solution.

Some of the actions in MDS, such as the e-mail alerts or loading of large amounts of data, need to happen in an asynchronous manner. Service Broker is utilized to allow users to continue to use the front-end without having to wait for long running processes to complete.

Although strictly outside the MDS database, SQL Server Database Mail, which resides in the system msdb database, is used as the mechanism to send e-mail alerts to subscribing users.

In addition to the tables that hold the master data entities, the MDS database also contains a set of staging tables that should be used when importing data into MDS. Once the staging tables have been populated correctly, the staged data can be loaded into the master data store in a single batch.

Internet Information Services (IIS)

During the initial configuration of MDS, an IIS Web Application will get created within an IIS website. The name of the application that gets created is called “MDS”, although this can be over-ridden if needed.

The Web Application contains a Virtual Directory that points to the physical path of <Drive>:<install location>WebApplication, where the various components of the Master Data Manager application are stored.

The MDS WCF Service called Service.svc is also located in the same directory. The service can be exposed in order to provide a unified access point to any MDS functionality (for example, creating an entity or member, retrieving all entity members) that is needed by other applications. Master Data Manager connects to the WCF service, which then connects to the database, so this is the route that should be taken by other applications, instead of connecting to the database directly.