SQL Server Business Intelligence Master Data Management

By: Siddharth Mehta

Overview

Before the data moves from OLTP / Staging area to the Data warehouse, it passes through the ETL layer where the data is staged, cleansed and even transformed if required. We saw in the last chapter that master data is an important factor while designing the execution sequence of ETL packages. In the bigger picture of the BI solution, master data may be stored in an external centralized Master Data Management (MDM) solution. Master data may be used by multiple transactional systems across the enterprise and each system maintaining their own version of master data is neither error-free nor an efficient way to handle master data. If an enterprise is not managing master data in an efficient manner, the BI solution should seek to include MDM as a part of the solution. We would discuss the architecture of MDM at a higher level and discuss how it would fit in the present business scenario.

Explanation

Before we start with the details of SQL Server Master Data Services, its important to know that MDM is a vast subject. Consider reading this article to develop some background of MDM. In case if you intend to develop in-depth knowledge on MDS, this article can be a good place to start your learning. In case if you just want to have an introduction of MDS and how it may fit in out business scenario, below is an explanation of the same.

Let's say that AdventureWorks maintains a list of standardized country codes for each country. The same should be used by any applications used by the company. A mapping for each country and country code is maintained in MDS. And we need to use the same while loading the data in the data warehouse from the staging area.

Before trying to understand how we would use MDS for the above scenario, let's understand some points about MDS to quickly develop our understanding about MDS. These points are:

• Master Data Services is a domain independent, master data management technology to support master data management efforts of an organization.
• Master Data Services stores data in the data model that is organized by entities, attributes and members.
• Master Data Services Configuration Manager is a tool you use to create and configure Master Data Services databases and web applications.
• Master Data Manager is a web application you use to perform administrative tasks (like creating a model or business rule), and that users access to update data.
• MDSModelDeploy.exe is a tool you use to create packages of your model objects and data so you can deploy them to other environments.
• Master Data Services web service can be used developers to extend or develop custom solutions for Master Data Services.
• Master Data Services Add-in for Excel can be used to manage data and create new entities and attributes.

A list of country codes maintained in MDS in it's simplest form may look like below. Data stewards and data governance team may maintain this kind of master data using MDS.

Once the data is ready to be used by subscribing systems (like our ETL packages), the same would be published using subscription views in MDS as shown in the below screenshot. ETL packages can query the view and find the corresponding mapping for the country codes master data.

Additional Information

• Consider reading MDS tips for more information on MDS.