SQL Server 2008 R2 – Master Data Services

Use Master Data Services to store your single source of truth...

When building a Data Warehouse, we have to go through a “Data Discovery” phase where the requirements of the Business Intelligence solution are mapped to the actual operational systems so that we can extract transform and load (ETL) the data into the Data Warehouse. This is a significant effort, especially if you have multiple application groups to contend with. Not all systems use appropriate and friendly naming conventions! Usually we end up with a large spreadsheet with all the cross reference information and we proceed to design the DW and build the ETL processes necessary to populate it incrementally. Well, now there is a better way – Master Data Services.

One of the many interesting features of Microsoft SQL Server 2008 R2 is Master Data Services (or MDS). This enhancement allows an organization to create a “Master Data Hub” which provides a “single source of truth” for the enterprise’s numerous data entities and hierarchies. The typical medium to large sized company has many disparate systems, some inherited from mergers or acquisitions, each using its own data source. These data sources can be aligned using a hub-and-spoke architecture to cross-reference the data entities into a central database that can be used for auditing, change tracking and consolidation. It may even help you to build that Data Warehouse you’ve been thinking about.

Discovering the data specifications can be time-consuming but when stored centrally and maintained with version control and change tracking, the business can benefit from this understanding of it mission critical data. When building a Data Warehouse, much of this data discovery needs to be performed but instead of recording this information in an isolated spreadsheet, now the information can be recorded in a centralized database for all to access and contribute to. Through Web Applications, individual application groups can be held accountable for “their” data specifications through ownership, allowing all parts of the organization to contribute to the centralized “Master Data”. Ongoing Data Warehouse and Business Intelligence efforts can then benefit with the Master Data being readily available and up-to-date.

Master Data Services consists of the following components:
• Master Data Services Configuration Manager – used to setup the MDS Database and configure the Web application
• Master Data Manager – ASP.NET application used to manage your master data
• Master Data Services Web Service – used by developers to extend and customize the functionality of Master Data Services

Note that Master Data Services (MDS) is available on only the 64-bit editions of SQL Server 2008 R2 Datacenter, Enterprise and Developer. It is not supported on the 32-bit platform. It requires the .NET Framework 3.5 SP1 or later. For running the Web Applications, IIS is required with ASP.NET and Windows Authentication enabled.

If you run into problems with the 64-Bit install of SQL Server see my blog entry:
http://www.networkworld.com/community/node/69817

Here is a good MSDN Blog on installing MDS:
http://sqlblog.com/blogs/mds_team/archive/2009/12/10/installing-and-configuring-master-data-services-2008-r2-november-ctp.aspx

Good luck!
Brian

• Microsoft
• Storage
• 64-bit
• database
• enterprise data model
• master data hub
• Master Data Services
• MDS
• metadata
• SQL Server 2008 R2