The use and misuse of Master Data Services (MDS) in SQL Server

After watching the SQL Hangout (Hangout #27 Master Data Management?), something told me to also shed light on other ways to use and misuse MDS.

Let start with the definition of MDS (by Microsoft):

Master Data Services (MDS) is the SQL Server solution for master data management. Master data management (MDM) describes the efforts made by an organization to discover and define non-transactional lists of data, with the goal of compiling maintainable master lists…The result of a successful MDM solution is reliable, centralized data that can be analyzed, resulting in better business decisions.

So…master data….check! You may immediatly think about a single version of the truth regarding customers, products, etc.
But in practice I found out that thanks to its brilliant (spoiler: a bit slow in 2012, but should be fixed in 2016) Excel add-in, business users can easily upload and maintain fairly static datasets. The web interface is horrible, you really need to get used to the way it’s been arranged. I still have figured out how the versioning-system works btw, but just create a subscription view and then use it as a source for your DWH. Beats building SSIS packages for each and every Excel-sheet the business user wants to ‘upload’.

Business users of my current client are use MDS for:
Master Data (list of branches, clusters, geo-locations, etc.)
Hierarchies (GL account, e.g. Profit & Loss or Balance sheet)
Targets for SSRS reporting (e.g. min/max target values per branch for a certain KPI)
Figures which are uploaded by the business, like:
* Actuals (e.g. illness rates, number of FTE’s, etc.)
* Budgets (e.g. P&L, etc.)
* Forecasts (e.g. P&L, etc.)

MDS wasn’t build for this and it isn’t fast when you want to publish upload or retrieve 295.356 records (via the MDS Excel Add-In).

MDS - Many many records
(Yep I’m Dutch)

But whatever…if it works….it works 🙂

An example of our list of views (MDS subscription views):

Use and misuse of Master Data Services 2012 - MDS

Want to learn more about MDS? Follow Kenneth Nielsen‘s blog:

5 thoughts on “The use and misuse of Master Data Services (MDS) in SQL Server

  1. Hi Clint, thank you for the mention of my blog.
    In regards to the performance issues, I promise you that it will be significant in SQL 2016 – and even now you can improve the performance by installing the newest Excel Add-in and the latest CU for MDS on your server.
    We had the same problems when the entities became very large, the end users complained about the performance, but after the upgrade it turned out OK – actually you can experience a factor 4-5 in performance just by installing the latest Excel Add-In.

    Best regards

  2. You’re pointing at some ‘misuse’ of MDS. Could you add some reflection in here? If MDS clearly isn’t built for these solutions, are we missing solutions in the MS stack? Are there other drawbacks to the misuse of MDS? Is everything ‘just ok’? Or is it just not clear enough what the possible effects are?

    • Well MDS should primairly be used to store fairly static master data (e.g. list of products, etc.) when you read the product description. At my client, business users publish new forecast records weekly/monthly for example and it has nothing to do with actual ‘master data’ or MDM.

      It totally replaces the need for some kind of user-friendly Excel import/export-tool. Before I started misusing MDS for it, I would be building an SSIS package for each file which needs to be uploaded (or upload it manually via the Import Export Wizard for example). So in this case, yes we do miss a solution in the MSBI stack for it.

      Regarding the number of records, how many records would a large dimension (products, customers, etc.) have? Now we have several 100k+ entities (‘tables’ in MDS) and you notice that it wasn’t built for such workloads. Try opening/retrieving it in Excel and it takes well over a minute 😉

      For now, MDS is running fine. I don’t see any drawbacks right now, except for the annoying GUI which you’ll have to get used to.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s