Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

How to standardize attribute values in Master Data Services


By:   |   Last Updated: 2016-04-27   |   Comments (2)   |   Related Tips: 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


next webcast button


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




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

 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

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?


Learn more about SQL Server tools