SQL SERVER – Enhancing Reference Data in Master Data Services – Notes from the Field #089

In a previous post, I talked about adding reference data to Master Data Services and how it can enhance your Enterprise Data Warehouse. Through the Master Data Services add-in for Excel, I added reference data into a Master Data Services model. This model was created with data types that were inferred from the given values that were imported into the model. All of the data types were of basic types like strings or numeric.

In this post, I’m going to update the data model we created in the previous posts and add domain values to the column’s data types. Domain values are a way to restrict a column’s values to a defined list. This also provides a drop down for the column that restricts any free form text.

The steps are:

• Create two entities that will be used for the domain values
• Update the Customers entity to use the newly created entity

All of these step will be performed within Excel using the Master Data Services add-in for Excel.

Using the Demo model, created in the last post, we will update the MaritalStatus column with domain values.

Step 1 – Create Additional Entities

We will start with the MaritalStatus column of the Customers entity and create an additional entity called MaritalStatus. This entity will be used for the domain values in the MaritalStatus column of the Customers entity.

1. Create an Excel worksheet with the reference data for MaritalStatus and provide a header for each of the columns in the data set. In this example we use Code and Name.

Figure 1 – MaritalStatus Excel worksheet data

2. Select the data including the headers and click the Create Entity button from the Master Data tab in Excel.
3. Enter the data for the following items:
   1. My data has headers: Checked
   2. Model: Demo
   3. Version: VERSION_1
   4. New entity name: MaritalStatus
   5. Code: Code
   6. Name (optional): Name

Figure 2 – Create Entity Dialog

4. Click OK.

Step 2 – Update the Customers Entity

1. Connect to the Customers Entity.

If you need help connecting to the Customer entity follow the below steps:

1. From the Master Data tab click the Connect button and connect to the MDS instance with the Demo model.

Figure 3 – Connecting to MDS instance

1. Select the Demo model and double click on the Customers Entity.

Figure 4 – The Master Data Explorer dialog

2. Select the any cell in the MaritalStatus column of the Customer Entity.
3. Click the Attribute Properties button.
4. Change the Attribute type to Constrained list (Domain-based) and select the MartialStatus entity for Populate the Attribute with values from:

Figure 5 – Attribute Properties Dialog

Notice that the column has a different look with a constrained list and if a cell is selected, a drop down with the available values is now present. This keeps the users from adding values outside of the list.

Figure 6 – MaritalStatus as a Constrained List

Creating Additional Entities

Adding domain values to the other columns would require the same process.

Note: In the current dataset Education and Occupation does not have code values. One method to solve this creates an entity where the code and name contain the same values, see Figure 7.

Figure 7 – Education Entity

Summary

In the previous articles on Master Data Services we added reference data to a Master Data Service model. This data can be maintained through the web interface and Excel add-in by the subject matter experts (SME). By creating a robust model the SMEs are better able to create and maintain quality data.

Hope this helps.