SQL Zone is brought to you in partnership with:

ETL and Java Developer with 10+ years experience building Operational Reporting and Data Warehouses. Experience with Informatica, DataStage, Oracle, DB2 zOS, DB2 UDB, SQL Server. Shane has posted 11 posts at DZone. View Full User Profile

Microsoft SQLServer 2008 R2 Master Data Services

  • submit to reddit
Published by: McGraw-Hill Companies
ISBN: 978-0-07-175623-5

Reviewer Ratings




Buy it now

One Minute Bottom Line

Master Data Management, MDM, is a frequent topic of frustration and headaches in Business Intelligence teams. In part, it entails the integration and maintenance of conformed dimensions and metadata across multiple application systems. Microsoft SQL Server 2008 R2 Master Data Services is a very good introduction to Microsoft’s solution to part of an enterprise Master Data Management (MDM) strategy. With clear and concise examples, this book will enable even the least technical person to quickly install and configure a Master Data Services deployment quickly and effortlessly.


Master Data Management, MDM, is a very broad topic. At a high-level, it entails managing data and metadata across multiple sources, throughout the data and business modeling process, through the Extract-Transform-Load (ETL) process(es) and into the Reporting layers. Many vendors tout their end-to-end MDM products. To get full benefit, a business typically has to purchase not only the vendor’s MDM integration solution, but also their Data Modeling tool, their ETL Tool as well as their Reporting tool. Enterprise MDM solutions become very expensive, very quickly.

Most businesses have an assortment of tools that frequently do not integrate easily without significant manual intervention. Microsoft SQLServer 2008 R2 Master Data Services is Microsoft’s solution to the integration nightmare confronting companies. This book is an excellent guide to getting up and running with the Master Data Services component of SQL Server 2008 R2. There are many screen-by-screen examples on setting up the application, configuring hierarchies, importing data model definitions.

The primary users interact with Master Data Services via a web browser interface. A word of caution, the tool, Master Data Services, requires a non-developer version of SQL Server 2008 R2 as a backend and the application itself can only run on a 64 bit machine running either Windows Server 2008 or later, Windows Vista Business or higher, or Windows 7 Professional or higher. Often companies that have Microsoft SQL Server will have a test server available that can be used to try it out, but the average home user will probably not have the spare server.

I skimmed the setup and configuration sections which seemed easy to understand and very straight forward. I focused mainly on the sections that described the capabilities and functionality of the tool. Most of the integration into and out of Master Data Services entails reading or generating flat files via SSIS packages into well-defined interfaces. There are views available in the database backend for external applications, such as ETL tools, to pull directly against for re-use. The two main areas where this tool excels are in data model management (and/or business process management) and maintenance of conformed hierarchies. Master Data Services provides a generic structure for maintaining any domain model.

The terms used in the book will be familiar to anyone knowledgeable with business process modeling or logical data modeling; domains, entities, attributes, etc. The authors of this book do a very good job of defining some of the different roles that applications can play in relation to an organization’s data. Three useful ones, in particular, are System of Entry, System of Record and Subscribing Systems. System of Entry (SOE) is a frontline data-entry system. System of Record (SOR) is a system designated as the authoritative system for a subject area; such as customer, account, etc. And Subscribing Systems are downstream systems that consume data from other systems without directly modifying the data. Among the more interesting functionality provided by Master Data Services, the ability to create and maintain hierarchies by non-technical business users is the most beneficial in my opinion. Structured hierarchical data, such as Sales market and Corporate hierarchies (representing corporations and their subsidiaries), represent some of the most difficult and dynamic structures that Business Intelligence and Data Warehouse teams can manage. Often these structures are managed manually by an IT Developer who fulfills requests from a Business Analyst without truly understanding the relationships and/or the domain. Often, the updates have to be manually applied to multiple applications. Master Data Services allows the Business Analyst to centrally create and modify business rules related to enforce integrity in a hierarchy as well as add, move and delete nodes. These conformed hierarchies can then be published to other source systems and applications for re-use. Master Data Services provides support for multiple hierarchy types as well as generic collections for non-structured data. The security model described in the book allows different users to maintain their own domains. One person could maintain the sales market hierarchy while another user can maintain a different domain. Master Data Services is not an ETL, data modeling or reporting tool. It is a generic integration layer for all of these. For companies that lack the purchasing power to buy a completely integrated MDM solution, I would strongly recommend this book and tool for those shops which already have Microsoft SQL Server 2008 in house. While the tool itself does not integrate with third-party solutions, it does provide a generic file interface for import and export as well as database views for other tools to pull from. I would have liked the authors to provide more concrete examples of the types of domains and hierarchies that could potentially benefit from this tool. Also, I found the definition of what constitutes Master Data to be somewhat murky and amorphous. The first chapter provides a decent high-level description of Master Data Management (also called Data Governance). The rest of the book is devoted to the tool’s functionality. The authors could have better detailed the place Master Data Services fit into a Master Data Management strategy by giving a paragraph on the other aspects, such as ETL tools, Reporting tools, Modeling tools and other Source systems. Having a background in Data Warehousing and Data Governance, I found this book easy to follow. This book is not an introduction to Master Data Management, Data Warehousing or Data Integration. As an introduction on Microsoft SQL Server 2008 R2 Master Data Services, I would strongly recommend this book to anyone contemplating on implementing this package as part of their MDM strategy. If you work in IT, are involved in Business Intelligence or Data Warehousing and your company is starting to look at integrated MDM solutions, I strongly recommend reading this book before your business analysts can. This tool or one like it is something Data Stewards and Business Analysts alike would immensely benefit from using. Be the one to bring this functionality to their attention. Don’t get caught unprepared. I plan to recommend both the book and the tool to my employer. In the course of reading the book, I found five or more problematic domains that we could get immediate benefit from by utilizing this or a similar tool.
Published at DZone with permission of its author, Shane Willerton.

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)