Master Data Management with SQL Server: A Primer – Part 2

In my previous post I wrote about the need for master data management (MDM), and what SQL Server offers as a solution. In this post I’ll explore SQL Server’s MDM offering with a simple example, to get to know the product.

SQL Server Master Data Services (MDS) was launched with SQL Server 2008 R2. It was Microsoft’s entry into the MDM market, what most people considered to be a half-baked product, which Microsoft had acquired from Stratature in 2007. SQL Server 2012 saw the offering mature into something use-worthy, while SQL Server 2014 did not add anything new to MDS. As opposed to other SQL Server offerings such as Analysis Services and Reporting Services, MDS does not have its own Windows service. Rather, it requires a web application to be setup along with a database on the Database Engine.

MDS is a 64-bit only offering and comes only with Enterprise and Business Intelligence editions of SQL Server (and of course with Developer Edition).

Installing MDS

It’s always a good idea that you have the pre-requisites for MDS (especially that of the web application) installed first. There is a comprehensive list of Windows features that need to be enabled.

Installing MDS is quite straightforward since it appears in the SQL Server setup wizard, and you just have to tick the check box for it to install. Once the SQL Server setup is done, you need to open up the Master Data Services Configuration Manager, and if it looks like this your installation is fine:

Master Data Services Configuration Manager
Master Data Services Configuration Manager

The next steps are configuring the MDS database and web application. These are very straightforward steps and can be done in a few minutes, by selecting the Database Configuration and Web Configuration options (see in image above).

You also will have to install the latest version of Silverlight.



Using MDS

To use MDS, you start by navigating to the web application (known as the Master Data Manager) that you had setup. What you would see is something like this:

Master Data Services Master Data Manager
Master Data Services Master Data Manager

The UI is built using… wait for it… Silverlight, so there’s nothing much from the intuitive usability side here. The application looks and feels like an alpha software. But hey, it works. The top portion is for information workers. The bottom portion is for administration; to setup the entities and other objects, security configuration and managing data import and export options.

Let’s get our fingers dirty. We will start by building master data for a single entity, Employee, to demonstrate the use of MDS and to get to know the product. We could get deeper, later. When starting on a proper MDM project, there would be a lot of business related policies and processes that need to be into place. For this post’s sake let’s just skip over them to the technology part.

Models and Entities

Models are the top most in the MDS hierarchy. A model holds one or more entities. More often models are created per entity, along with some “supporting” entities. So in our case, we will create an Employee model with an Employee entity, along with entities such as Department and City. Employees will belong to a Department, while living in a certain City. This is how we would do this in MDS:

In the video, you see that first we create a model for the domain at hand, which is Employee. And then we uncheck the checkbox named Create entity with same name as model. Even though we do need an Employee entity, we do not create it at this point. And, there is a reason for it. Whenever you create an entity, a staging table is created on the database with the same name as the entity. However it is preferred that the name of the staging table  follow the standard of <model name>_<entity name>, just in case two entities from two different models share the same name. So, by unchecking the said checkbox, we are telling MDS, that we will be creating the Employee entity later (with our own naming convention for the staging table).

We then create three entities; Employee, Department and City. We give a custom name to each entity’s associated staging table, in the <model name>_<entity name> format.

We then add attributes as follows:

City Employee
Province First Name
Country Last Name
Birth Date
Address Line 1
Address Line 2
Address Line 3

You would have noticed that each entity has a Code and Name as default attributes. You would have also noticed that when creating the entities we chose the option to Create Code values automatically. Automatic codes are ideal in scenarios where you have not decided what the code of an entity is going to look like; Mergers of two organizations with two entirely different structure of employee codes.

While all attributes that were created for both entities were free-form, the City attribute of Employee was of the domain-based type using the City entity, meaning that only values from the City entity can be used in the City attribute.

Finally, we created an attribute group. An attribute group, groups up attributes to be displayed on its own tab, making it easier for the business user or data steward to work with values in the Master Data Manager. In the video we created an attribute group called Address containing Address Line 1, Address Line 2, Address Line 3 and City. So when a user is say, adding a new employee, and there are lots of attributes, the grid gets longer and longer. An attribute group ensures that related attributes can be found together and in their own tabs.

Master Data Services - Master Data Manager - With all attributes
Master Data Services – Master Data Manager – With all attributes


Master Data Services - Master Data Manager - Add member with Address attribute group selected
Master Data Services – Master Data Manager – With Address Attribute Group


In reality we have done something akin to building a relational database using a simple GUI, a sort of database design for dummies. But then, that is how it is supposed to be: This is data steward territory. We have defined a model and added entities to it. There’s more, such as hierarchies, collections and business rules, which are topics for another day. However, I hope this post gave you some insight to get started on MDM with MDS.

My next post on this topic would focus on how to import data into MDS.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s