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:
- Add a Product Dimension Model
- In MDS web
- Create Product Dimension Entity (simple example)
- Assume it is already in Dimension so just copy and paste in Excel Add-On
- Create Product Category Entity
- Add Product Category Attribute to Product Dimension
- 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.
Click on the icon on the right (see below) to create the new model, and call it DimProduct:
Create the entity with the same name as the model.
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:
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:
It will bring up the following dialog:
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.
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.
2) While on the ProductCategory header row, select Attribute Properties from the Master Data ribbon menu.
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.
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.
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.