What is Master Data Services

If you work in the Microsoft Business Intelligence stack, you may have heard of a product called Master Data Services or MDS.

Master Data Services (MDS) is the SQL Server solution for master data management.
I use this product quite frequently at various clients.  Why?

Many organizations have data residing in several departments.  Sometimes the data is different.  The spelling may be different, the address may be different, etc.

MDS allows a centralized location to manage strategic business data to be consumed in various internal applications and reports and dashboards.

MDS has a web front end which can be installed with the SQL Server install discs.  It must be configured on a server by specifying the database to run the web and store the data.

Here are the settings for the database:

And web configurations:

And here's the web piece used by the internal clients:

The top level is called a Model, similar to a Database.  Below that, are Entities, which are similar to Tables.  And Attributes are similar to Fields.

You can create Hierarchies and Collections as well:

How do you get data into MDS?  Four ways.

First, after your Model, Entity and Attribute are created, you can enter data on the web.

Second, there happens to be a plug in for Microsoft Excel, which allows connection to the MDS server, where you can view Entities, change data, Publish the changes.  And another nice feature, you can simply create the Entity from an existing Excel file.  I prefer this method for simplicity.

Third, there are Stored Procedures with the MDS database in SQL Server.  Basically you load your data into Staging tables, call the appropriate Stored Procedure, and it uploads or delete or modifies the data based on which procedure you call.  This is useful for automating in an SSIS package for example.

Fourth, you can do batch imports under Integration Management, however, I've never used this option:

How do you get data out of the MDS Database.  I typically use a View.  Which you can create in the Web console under Integration Management, under Create View:

The views appear in SQL Server in the MDS database, with a prefix of "mdm".

The best business case for using MDS is to allow the Business to control their data by adding records on demand, through a variety of consoles, in a centralized location and removing the dependency on the IT staff to run their business.

For example, what day did the month close?  Well, it closed on August 2.  So the Accounting person can log on to the website or through Excel, find their Entity, and enter a record entry in the ClosedMonth, which gets read by the Business Intelligence app through the view.

I really like this app and from what I've seen, the 2016 version has some great improvements.  Looking forward to that~!

Thanks for reading.