Master Data Services – SSIS MDM Load

 

Microsoft recently released Master Data Services in SQL Server 2008 R2 CTP. Without getting into the strategic benefits, definitions etc. of MDM(Master Data Management) this tool will be very helpful for any application in automating the setup and maintenance of look up or cross reference tables or utilizing reference data.

 

  

Kirk Haselden has provided a white paper on The What, Why, and How of Master Data Management

 

 

 

Nick Barclay: BI-Lingual has provided an excellent post on getting started with this tool. In his post he provide a walkthrough on how to set up a sample Geography Model and the associated Entities, Attributes and Hierarchies. In addition he provides the TSQL to load them. I have expanded on his work and provided a basic SSIS Package for loading the Model.

 

 

 

 

 MDS_StagingLoadBarclayProject.dtsx

The approach was to use derived column transforms to set up the required metadata and aggregate transform to generate distinct results. Obviously Nick has done all the heavy lifting I thought it would be helpful to have an SSIS Package to compliment his very informative post.

 

 

 

Last I intentionally kept this package basic, they are many opportunities for improvement and making the entire process data driven, which I will explore next.

 

 

 

An additional reference for understanding MDM is David Loshin’s book.

 

Ira Warren Whiteside

Actuality Business Intelligence

 

 

"karo yaa na karo, koshish jaisa kuch nahi hai"

"Do, or do not. There is no try."