How to standardize attribute values in Master Data Services


By:   |   Updated: 2016-04-27   |   Comments (2)   |   Related: More > Master Data Services

Problem

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.

Solution

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


Last Updated: 2016-04-27


get scripts

next tip button



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

View all my tips
Related Resources





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 05, 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?



download


Recommended Reading

Business Rules Extension in Master Data Services 2016

Resolving SQL Server Master Data Services (MDS) Patching Errors

SQL Server Master Data Services (MDS) Tips and Tricks

Migrate SQL Server Master Data Services (MDS) model objects and data

Master Data Services Versions and Views for SQL Server





get free sql tips
agree to terms


Learn more about SQL Server tools