Master Data Services in real life

Master Data Services (MDS) is one of the hidden gems in the Microsoft BI tool stack. As the name suggests, the idea is to use the tool to technically support Master Data Management.

But despite being a good tool, in real life we see that MDS isn’t up to standard compared to the master data management tools offered by other big vendors like IBM and SAP.

This doesn’t mean that you shouldn’t use MDS, which provides great added value in many business intelligence and data warehousing projects.

One of the biggest challenges we face today in data warehouse projects is giving end users the ability to interact with data. If existing data needs to be manipulated by a user, or missing data needs to be added, currently we have two options:

• Create a custom application: this can have a significant impact on the budget and the user requires a different technical profile from the other people in the team.
• Dump all the information in an Excel file: this Excel file then needs to be uploaded and the data in it validated, but there is a risk that the data is not valid and needs to be sent back to the user.

So it would be easier to have an environment where we could:

• create tables in a model
• put an Excel front-end on top of these tables
• work with select lists if 2 tables have a relationship
• set up fine-grained security on this model
• create business rules to perform first-level validations on the data entered/changed
• have an easy way of loading data to and from these tables

All these requirements are met in MDS – it’s a perfect match. What’s more, the simplicity with which the requirements can be set up makes MDS one of the hidden gems in the Microsoft BI tool stack.

To give you an idea of how MDS can be used, below we give some examples of solutions implemented for our customers.

Data quality framework

For one customer we had to provide business users with data that didn’t meet data quality standards. So we created a data flow that would validate the data and give business users the option to review the data that was flagged as invalid. Not only could users review this data, but they could also correct the information with MDS, which wasn’t possible in the source system.

So the actual flow works like this: data is loaded from the source system to a staging environment, where it is validated. The data that is flagged as invalid is then loaded to MDS and a business user is notified of this. The business user opens their Excel and connects to MDS to load the invalid data, including a message about why the record is invalid. If possible, the business user corrects the data and publishes it back to the server, where it is validated again. If there are still issues to be resolved, the user receives another message.

Hierarchy management

In most source systems data is available at the lowest level. Some systems have hierarchies available, while others don’t. But when it comes to reporting, even the systems with hierarchies are not what business users generally want.

So we started managing alternative hierarchies with simple Excel files that were loaded into the system. The major downside of working like this is that the Excel structure is easily changed, causing errors when trying to load the data. Typos or incorrect matching would lead to errors when loading the Excel file. What’s more, it is difficult to apply security to these Excel files to prevent unauthorized access.

The use of MDS solved all these problems. Users were only allowed to select existing values in drop-down lists. Fine-grained security was set up so that users could only view or update fields they were authorized to access. And uploading the data was instantaneous, with a fixed format that end users couldn’t change.

Configuration set-up

MDS can also benefit non-business users. It can be used when an advanced project configuration is required. One of the projects we worked on had several Navision systems as a source. These systems typically have an exact copy of a database for each entity or company within the organization. Instead of creating a source extraction package for each of these databases we chose to make the packages more dynamic and store the necessary configuration information in tables. When a new company needed to be added, several lines were added. To make this maintenance easier a small MDS model was created that allowed users to add a new company and automatically add all the necessary configuration lines. So instead of spending several hours setting up a new company, it took just 2 minutes to add a company to the data warehouse – the time needed to open Excel, connect to MDS, open the company table, add the new company and publish the info to the server.