• Share
  • Share
Solve business problems and effectively manage data warehouse dimensions using Master Data Services

SQL Server Master Data Services series

Achieve effective data management: Solve business problems and effectively manage data warehouse dimensions using Master Data Services.

Steve Moss | July 24, 2015

In the previous post, I outlined some of the major functionalities of Master Data Services (MDS) that can be used to solve real-world business problems. Here, I’ll continue this by working through a more detailed example of how to use MDS to solve a common problem: How to add a hierarchy/grouping that is not in the source system to a Dimension in a Data Warehouse and manage it with MDS.

(For those who read my first post, this is a demonstration of the first three things that you’re missing out on if you’re not using MDS: 1) the ability to use Excel to insert and edit data, 2) the ability in Excel for Data Stewards or Business Partners to add Entities or Attributes, or change the metadata and datatypes, and 3) the ability to expose data, metadata, and change history so that you can export it and sync up other systems.)

Real-world scenario: MDS in action

We’re going to use Master Data Services to add and maintain a category or grouping that doesn’t exist in the upstream system, but is needed in a Data Warehouse (DW). For example, it is currently just maintained in a spreadsheet outside of either the upstream systems or the DW.

The steps we’ll take:

  1. Add a Product Dimension Model
    1. In MDS web
  2. Create Product Dimension Entity (simple example)
    1. Assume it is already in Dimension so just copy and paste in Excel Add-On
  3. Create Product Category Entity
  4. Add Product Category Attribute to Product Dimension
  5. Assign Product Categories Manually

The first thing you need to do is create a Model. This can only be done in the MDS web, not the Excel Add-In. One way to think about it is just setting up the container for the Entities and Attributes. The set up doesn’t require any code.

Use the web MDS UI to create a model for the Excel Add-In

Click on the icon on the right (see below) to create the new model, and call it DimProduct:

Adding a new model in SQL MDS

Create the entity with the same name as the model.

Naming new model in SQL MDS

Now that the entity is created we can return to the MDS Excel Add-In and refresh it. The DimProduct model you created will show up:

DimProduct model from web MDS can be found in the Excel Add-In

Now that your model is set up, things get interesting: Putting your current dimension data into MDS using the Excel Add-In without any code.

You just have to connect to the model that you just created, then download the dimension data from your data warehouse (or just type it in). Then select Create Entity on the Master Data menu in Excel:

Creating the Entity in Excel

It will bring up the following dialog:

Dialog box for creating an Entity in Excel

Besides looking different in Excel with the metadata, you have actually put the data in Master Data Services where the data and changes/transactions and metadata are going to be logged and versioned. Without MDS, you’re missing out on the easy and efficient way.

So far we have set up the dimension that we already have in our DW in MDS, including inputting the data.

Without Master Data Services, you wouldn’t have the ability for users to edit the data, including the Product Description. For example, if Product data comes from an upstream system but there was an entry error that is throwing results off, it is possible to edit.

For this scenario, we are assuming that the upstream data should be edited, but we are just adding a grouping category to it.

Back to our business problem. In this scenario, the Product dimension was already in the data warehouse, but our business partner wants to add a new level to the dimension: a Product Category that is not in the upstream system.

To add a Product Category, create a new Product Category entity by using the Create Entity in MDS.

Entity with connection/meta data info on first line in MDS explorer

Now, add the Product Category to the Product model. This is done in two steps:

1) Type ProductCategory in the header row of Product model.

Creating a Product Category in Excel

2) While on the ProductCategory header row, select Attribute Properties from the Master Data ribbon menu.

Naming the new Product Category in dialog box
Adding the Product Category to the new Product Model
Selecting Attribute Properties from the MDS ribbon menu
Attribute Properties type: select Constrained List
Attribute Properties dialog box
Product Category header in Excel with drop down menu
Editing and publishing the Product Category row changes
Publishing box for Product Category changes

Master Data Services provides users with a simple and efficient way to manage data and solve business problems. My third post in this series will continue with this data management problem, and outline a solution using MDS.

Computer screen with lines of code

Looking for more engineering tips?

Our engineers have a whole lot to say about custom software. They’re in the trenches every day, building, breaking, re-building, and sharing their hard-won wisdom along the way. Find their latest and greatest discoveries on Slalom’s new software engineering blog.

Read our engineering blog
Steve Moss Slalom Consulting

Steve Moss is a data and business intelligence consultant in Slalom Portland’s Information Management and Analytics practice. Steve is passionate about data quality, the performance of data systems, and delivering enterprise solutions for data architecture.


Start a conversation

What’s on your mind? Let’s explore the possibilities.