Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Sep 29, 2010

Creating Master Data Services Objects

By Arshad Ali

Master Data Services (MDS) is a master data management platform that allows you to create a centralized hub for your master data. Arshad Ali explores the different components of MDS followed by a step-by-step of how to create master data services' objects.


Master Data Services (MDS) is a master data management platform that allows you to create a centralized hub for your master data. Keeping master data in an external centralized system as a single authorative source helps to remove several master data challenges. This single authorative master data source acts as a System of Record by integrating master data from all the upstream sources and as a System of Entry by providing consistent master data to all the downstream applications. In this article, I am going to show you how to get started on Master Data Services, understand different components of it and create master data services' objects.

How to go ahead...

MDS objects creation is quite straightforward, the only important thing that you need to plan in advance is what master data you want to manage with Master Data Services; start with a small set of master data, which has solid business justification, i.e. plan for incremental adoption. These are some of the steps which we normally follow while working with it:

  • Decide which data you want to manage – As I said before, the first thing that we need to do is to decide which master data we want to manage with Master Data Services.
  • Build a model – Next we need to create different MDS objects for our master data; first, we create a model for master data, which is nothing but a top-level container for master data. Then we create entities inside the model that resembles a table of relational database. Then we create different attributes (analogous to column of relational table) and attribute groups for entities.
  • Load data – Next we load data into the different entities of the created model. There are three ways to load data to an MDS model. You can use Master Data Manager UI to load it manually, you can consume web services to programmatically load data and the third way is to automate the process of integrating data into the MDS, which I will cover in greater detail in my next article on MDS Data Integration.
  • Create rules – You can create different business rules to validate the master data coming to MDS; if any member violates this rule then it is considered invalid and MDS does not let it go to downstream applications unless you fix it.
  • Establish Notifications and SharePoint Workflows – You can set up MDS to receive notification from it if invalid data comes into the model. Not only this, MDS is integrated with SharePoint as well; this means you can set up to launch a SharePoint workflow if invalid gets into the model.
  • Declare Data Security – Next, you need to secure your master data; MDS allows role-based security for securing both MDS objects and data that it contains. I will be talking more about security in my next article.
  • Create refresh processes – And now you need to define and setup a refresh process for your master data. A model can be either in “Open” or “Locked” status/mode where it will be available to accept master data changes; once all entries and data validation is done, the model is switched to “Committed” mode where it cannot be modified further and will be available to downstream applications for consumption.
  • Share the data – And last you need to create subscription views through which the downstream applications will consume master data from MDS store.

Let’s get going...

Launch Master Data Manager UI and you will see these all options, depending on your permissions on Master Data Services. System Administration allows you to create different MDS objects, click on it to move ahead.

Create MDS Objects
Figure 1 - Create MDS Objects

To create a model, go to the Manage menu and the select Models as shown below:

Create Model 1
Figure 2 - Create Model 1

Next, you will see the Model Maintenance screen where you need to click on the “+” sign (Add Model) as shown below:

Create Model
Figure 3 - Create Model 2

In the next screen, specify the name of the model that you want to create, as shown below. You can select, by default, to create an entity inside the model with the same name, you can even specify to create an explicit hierarchy with the same name and include all leaf members in the mandatory hierarchy, finally click on the Save icon to save your changes.

Create Model 3
Figure 4 - Create Model 3

Once a model is created, you would create one or more entities inside it, which will actually hold the master data. Go to the Manage menu again and click on Entities, this time as shown below:

Create Entity 1
Figure 5 - Create Entity 1

On the Entity Maintenance screen, select the model from the combo-box in which you would like to create an entity (you can also see the list of entities created so far in the selected model) and then click on the “+” sign (Add Entity) as shown below:

Create Entity 2
Figure 6 - Create Entity 2

On the Add Entity screen, specify the name of the entity being created and select whether you want to enable explicit hierarchies and collections for the entity or not and finally click on the Save Entity button to create the entity as shown below:

Create Entity 3
Figure 7 - Create Entity 3

After entity creation, the Entity Maintenance screen will appear again with all the entities created so far in the selected model. For example, you can see below that I have created three entities - Product, ProductSubCategory and ProductCategory:

Create Entity 4
Figure 8 - Create Entity 4

You can create as many as entities you require or can modify the existing entities as per your need. While creating an entity, you will notice two attributes i.e. Code and Name are added by default, Code attribute is a unique attribute and behaves like a primary key of a relational table.

Add Attribute 1
Figure 9 - Add Attribute 1

To add additional attributes, click the “+” sign (Add leaf attribute) as highlighted above and you will then see the Add Attribute screen as shown below. An attribute can be either of Free-form type, Domain-based or File. The Free-form attribute is something that accepts free form data that could be characters, numbers, dates etc. or a combination of these. Domain-based attribute is very much like a foreign key, which means the value for this attribute is populated from any other related entity. For example, in Product entity, the attribute ProductSubCategory will get its value from the ProductSubCategory entity. The File type attribute is used to store documents, files, images etc.

Next you need to specify the name of the attribute and its other related information as shown below:

Add Attribute 2
Figure 10 - Add Attribute 2

Attribute group (a securable object) allows creating a group of logically related attributes for consumption by a specific downstream application. To create an “Attribute Group” go to the Manage menu bar and click on Attribute Groups and a screen similar to the one shown below will appear. Select the model and then the entity for which you want to create an attribute group and finally click on the “+” sign (Add Attribute Group).

Add Attribute Group 1
Figure 11 - Add Attribute Group 1

Next you need to select available attributes of the selected entity to be part of the attribute group as shown below:

Add Attribute Group 2
Figure 12 - Add Attribute Group 2


Master Data Services (MDS) is a master data management platform, which allows you to create a centralized hub for your master data that behaves like a single authorative source for your master data. In this article, I discussed how to get started on Master Data Services, understand different components of it and create master data services' objects, which is quite straightforward. In my next article I will be talking about MDS data integration and security.


MSDN: The What, Why, and How of Master Data Management

MSDN: Operations (Master Data Services)

» See All Articles by Columnist Arshad Ali

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM