Master Data Services (MDS) is a Master Data Management tool that was introduced with SQL Server 2008R2. I recently used MDS as part of DW solution. It allowed data stewards to manage various attributes that controlled financial calculations in a Parent/Child Hierarchy.
I recently took some of the lessons learned presented them to my local SQL Server User Group. The demonstration (MDS Demo.zip) that I put together came mostly from ‘Microsoft SQL Server 2008 R2 Master Data Services’ which was written by the Microsoft Master Data Services team (http://sqlblog.com/blogs/mds_team/default.aspx). It provides 1 hour long introduction to MDS and some example scripts that I put together. All the examples come from the book.
- MDS is separate install to SQL Server 2008R2
- Don’t install MDS with the .msi that came with your R2 CD. Download the latest CU instead
- Have a service account ready and pay careful attention to all the installation steps. I am not an IIS guru so it took me a few tries to get it right😉
***’Denali’s improvements —
“After you set up and configure MDS, make sure you install the MDS Add-in for Excel. This will change the way you work with your data, and at the same time, it won’t change a thing. What I mean by that is this: If you have a slew of people at your company working in separate Excel sheets, now is your chance to start putting that stuff into MDS and getting your users to all work with the same data. The magic is that they can all keep working in Excel, and you won’t have to train anyone on any new tools. Well, they still have to click some new buttons, but really all they have to do is get data, edit data, and publish that data back to the database. That way, they can share data with all of their friends, spend less time trying to reconcile mismatched data, and have more time to head to the pub early” http://www.mdsuser.com/index.html
This is HUGE because, to be honest, the current MDS UI can get a little cumbersome when preforming some tasks. Additionally, most really innovative analysis in an organization occurs when a BA dumps report data into his own excel which has some custom attributes that are not present in any source system. He then manipulates the data and presents it to mangers. With Denali you can conform those attributes to the enterprise level by adding them to the Data Warehouse Dimensions through MDS and Excel.
You can tell when you doing Data Warehousing correctly when you realize that you need a MDM solution!