Posts Tagged ‘Master Data Management’

Master Data Services 2012

January 18, 2012

As I mentioned in a pervious post Master Data Services for SQL 2012 is a huge improvement over the SQL Server 2008 R2 version. The biggest difference is the ability for users to manage entities directly in Excel!

This is important because now Data Stewards can perform most of their Master Data Management activities in excel.

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. An MDM project generally includes an evaluation and restructuring of internal business processes along with the implementation of MDM technology. The result of a successful MDM solution is reliable, centralized data that can be analyzed, resulting in better business decisions.

With the right training, most business users should be able to implement a Master Data Services solution. In addition, you can use MDS to manage any domain; it’s not specific to managing lists of customers, products, or accounts. When MDS is first installed, it does not include the structure for any domains—you define the domains you need by creating models for them.

Other Master Data Services features include hierarchies, granular security, transactions, data versioning, and business rules.

Master Data Services includes the following components and tools:

  • Master Data Services Configuration Manager, a tool you use to create and configure Master Data Services databases and web applications.
  • Master Data Manager, a web application you use to perform administrative tasks (like creating a model or business rule), and that users access to update data.
  • MDSModelDeploy.exe, a tool you use to create packages of your model objects and data so you can deploy them to other environments.
  • Master Data Services web service, which developers can use to extend or develop custom solutions for Master Data Services.
  • Master Data Services Add-in for Excel, which you use to manage data and create new entities and attributes.

For a summary of MDS resources, see the SQL Server Master Data Services Portal.

**Note: You can upgrade to MDS 2012 without upgrading the SQL Server 2008 R2 instance.

To install MDS follow the MSDN product information

MDS Samples Installation

**To Deploy the Sample packages you will need to use MDSModelDeploy.exe

3. List the MDS web services to connect to if you are unsure of the service name. The default being MDS1 as shown by this command.

MDSModelDeploy.exe listservices


4. Deploy the package by pointing to the package file, and connecting to that service listed from step #3. Provide a model name for the model that you want to deploy.

MDSModelDeploy.exe deploynew -package “C:\Program Files\Microsoft SQL Server\110\Master Data Services\Samples\Packages\customer_en.pkg” -model CustomerSample -service MDS1

MDSModelDeploy.exe deploynew -package “C:\Program Files\Microsoft SQL Server\110\Master Data Services\Samples\Packages\chartofaccounts_en.pkg” -model ChartOfAccountsSample -service MDS1

MDSModelDeploy.exe deploynew -package “C:\Program Files\Microsoft SQL Server\110\Master Data Services\Samples\Packages\product_en.pkg” -model ProductsSample -service MDS1

Once the samples are installed then install the Excel plug-in and enjoy


Tech-Ed: Managing Master Data with MDS and Microsoft Excel


Transforming Source Keys to Real Keys

October 3, 2011

Thomas Kejser a SQL Server Regional Director has recently made several great posts about

Transforming Source Keys to Real Keys Part1

Transforming Source Keys to Real Keys – Part 2

fixing Zombie and Dolly keys !  

It is a good approach to starting with Master Data Manangement.  

His Physically Placing the Maps in the Architecture post brings it altogether.

Master Data Management with SQL Master Data Services

September 22, 2011

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 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 ( 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”

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!