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).
(Yep I’m Dutch)
But whatever…if it works….it works 🙂
An example of our list of views (MDS subscription views):
Want to learn more about MDS? Follow Kenneth Nielsen‘s blog: