Master Data Management with SQL Master Data Services

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.

Tips:

  • 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!

About these ads

Tags: , , ,

2 Responses to “Master Data Management with SQL Master Data Services”

  1. Dimensional Modeling Financial Data in SSAS « Garrett Edmondson Says:

    [...] that through Master Data Management. Microsoft’s Master Data Management’s solution is called Master Data Services which handles GL Parent Child Hierarchies very [...]

  2. Dimensional Modeling Financial Data in SSAS pt2 « Garrett Edmondson Says:

    [...] Master Data Management with SQL Master Data Services LD_AddCustomAttr("AdOpt", "1"); LD_AddCustomAttr("Origin", "other"); LD_AddCustomAttr("theme_bg", "ffffff"); LD_AddCustomAttr("theme_text", "333333"); LD_AddCustomAttr("theme_link", "0066cc"); LD_AddCustomAttr("theme_border", "f9f9f9"); LD_AddCustomAttr("theme_url", "114477"); LD_AddCustomAttr("LangId", "1"); LD_AddCustomAttr("Autotag", "business"); LD_AddCustomAttr("Autotag", "technology"); LD_AddSlot("wpcom_below_post"); LD_GetBids(); Share this:TwitterFacebookLike this:LikeBe the first to like this post. [...]

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s


Follow

Get every new post delivered to your Inbox.

Join 56 other followers

%d bloggers like this: