How to standardize attribute values in Master Data Services

By:   |   Comments (2)   |   Related: > Master Data Services


In any Master Data Services implementation, master data is organized in the form of a Model, Entities and Attributes which is similar to a Database, Tables and Fields. Inevitably, any model has attributes which should be standard and reusable with a fixed set of valid values. Allowing free form data entry for such attributes can inadvertently corrupt the master data. To avoid this situation, the attributes have to be modeled in such a way that it allows a standardized valid set of values for the attributes. In this tip we would look at how to achieve this.


In MDS attributes can be of three types - Free Form, Domain Based and File. Free form attributes allow four different data types - Text, Number, DateTime, and Link. The restrictions that can be placed on such attributes are based on the data types. For example, if the requirement is to allow only a list of countries where the company's operations exist, such restrictions can't be enforced by data types. To deal with such requirements, domain attributes have to be used.

Consider an example of a Human Resources data model. Let's say we need to create an Employees entity with the name of the employee, employee code, gender and country. Let's also assume that we want the gender to have values - Male (M) or Female (F), and the valid countries are India (IN), United States (US) and United Kingdom (UK).

In order to model this requirement, follow the steps mentioned below:

Step 1

This tip assumes you have a blank model named Human Resources. Log on to the MDS web application, click on System Administration > Manage > Entities. Select the Human Resources model and click on the Add Entity button. This should bring up a screen as shown in the below screenshot. Create three different entities - Employees, GenderList and CountryList. The Employees entity will hold the employees master data. GenderList and CountryList will hold the list of valid values for Genders and Countries respectively.

Create Entity

Step 2

Navigate to the home page and open the Explorer menu. From the Explorer screen, open Entities > GenderList. Click on the Add Member button and add values as shown in the below screenshot.

Gender Entry

Step 3

Repeat Step 2 for the CountryList entity and add members as shown in the below screenshot.

Country Entry

Step 4

Navigate to System Administration > Manage > Entities, and select the Employees entity. Click on the Edit button and it should open up a screen as shown in the below screenshot. When a new entity is created, Code and Name are the two attributes added by default. We can use the Name attribute for the name of the employee, and code for the employeecode. We need to add the gender and country attributes to the employee entity.

Edit Employees

Step 5

Click on the Add Lead Attribute button, and this should bring up a screen as shown in the below screenshot. Create a new domain-based attribute named Gender, and in the entity select GenderList and save this attribute. Create another domain based attribute named Country based on CountryList entity and save.

Domain Attributes

Step 6

Navigate to the Home page and open the Explorer again. Select Entities > Employees, and click on the Add Member button to create a new Employee entry. You should be able to see the Name and Code attributes allow free form data entry, but gender and country are presented as dropdown lists from the members we added in the earlier steps.

Add Employees
Next Steps
  • Try out the different options available for free-form and domain-based attributes to organize, restrict and format data as required.
  • Consider expanding this exercise and try to create an entity (say Managers, for example) which has a domain based attribute based on the Employees entity.
  • Check out these other Master Data Services Tips

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Siddharth Mehta Siddharth Mehta is an Associate Manager with Accenture in the Avanade Division focusing on Business Intelligence.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Monday, November 27, 2017 - 5:21:27 PM - Grant Back To Top (73302)

 there is no Create code values automatically checkbox on the entity attribute page in SQL 2016 MDS


Tuesday, July 5, 2016 - 6:17:43 PM - Neel Back To Top (41821)

Thanks Siddharth,

In addition, I was wondering whether there's a way to standardize Reference data across sources? For example, merge "MLE" from Source A and "MALE" from Source B into 'M' for the standardized value in GenderList?

get free sql tips
agree to terms