Introduction to Master Data Services in SQL Server 2008

By: Tim Cullen | Updated: 2010-06-04 | Comments (2) | Related: > Master Data Services

Problem

So, between all of my company's recent growth and more acquisitions looming in the horizon, I feel like the company's data is getting out of hand. There are numerous databases housing the same information and it's getting quite difficult to keep everything in line. I've heard from a number of departments who want a more centralized approach to handling customer data, but I don't know where to begin. Can you steer me in the right direction?

You just ran into one of the biggest headaches for DBA's. If we've seen it once we've seen it a thousand times, data starts out in a spreadsheet in each department and then each department wants their own database. Then "it" happens, a client orders product and the order is shipped to the wrong location. The salesperson said he updated the shipping address in the database, but the Shipping Department shipped the product to an address from their database that hasn't been updated in months.

Solution

What we're talking about here is Master Data Management and there are a number of ways to handle it. Microsoft has a new answer to Master Data Management...Master Data Services, thanks to the acquisition of Stratature. Master Data Services will be released in SQL Server 2008 R2, but before we dive into the innards of Master Data Services, let's talk about the principles of Master Data Management.

What Is Master Data Management?

In simple terms, Master Data Management (MDM) is a process through which consistent, clean, up-to-date data is managed within an organization; however, "simple terms" does not mean "simple to implement". The fact of the matter is that Master Data Management is a process that starts out small and progresses as lessons are learned by the company. A number of factors are involved when evaluating data for consideration as "master data". It's probably easier to sift through your data and decide what isn't master data. One of the first steps in implementing a Master Data Management System is to decide that a Master Data Management process is actually needed!! In most cases the answer will be yes, but implementation of a MDM can be quite costly, both in terms of development as well as personnel hours, so it would be prudent to spend time justifying the need.

Who Should Be Involved With Implementation of a MDM?

Other than the DBA, personnel who understand the data and its sources should be involved in the planning and implementation process. They are considered data stewards. Other people include management and important stakeholders in the data, since there are financial and personnel considerations when developing and implementing a master data management program.

What Are the Important Buzzwords With Master Data Services?

Most of the "buzzwords" you'll hear when dealing with Master Data Services are used in other data modeling techniques:

Phrase	Explanation
Model	A model is a grouping of logically-related data items. It is analogous to a database in a Relational Database Management System and a cube in SQL Server Analysis Services
Entity	An entity is a container for master data and identifies definitions for its members. It is analogous to a table in a RDBMS and a dimension in Analysis Services
Members	A member is an instance of an entity. It is analogous to a table record in a RDBMS and a dimension member in Analysis Services
Attributes	An attribute is a data value associated with a member. It is analogous to a column in a RDBMS and an attribute in Analysis Services
Hierarchies	An hierarchy is a data value associated with a member and can in be in the form of free text or associated with another entity (domain-based attribute). It is analogous to a parent-child relationship in a RDBMS and a metaphor or hierarchy in Analysis Services

So What's A Good Way to Get Started At Reviewing Our Data?

Perhaps the first place to look is for tables that are stored in multiple databases. Examples may include, but are not necessarily limited to, information on state and territories, zip codes, and domain-based information like colors, sizes, and contact types. The next bit of data to review could be the essential data required for identifying your customers, as well as the data required to get your products to clients, including contact names, company addresses, and phone numbers.

Are There Any Limitations For Installing and Using Microsoft's Master Data Services?

Before you get too excited about using Master Data Services there are some limitations for installing and using it. First, Master Data Services will only be available for SQL Server 2008 R2 DataCenter, Enterprise, and Developer editions. Second, it will only be available for x64 versions of SQL Server. Lastly, the minimum framework it requires is the .NET Framework 3.5 SP1.

Next Steps

It may be worth your time to either survey departments in your company or log instances where data was inconsistent in your organization. Keeping a log may allow you to show reason for implementation of a Master Data Management process
Get white papers and roadmaps on Master Data Services
Get the What, Why, and How of Master Data Management
Read the Seven master data management best practices as reported by TechTarget
Check out additional information on SQL Server 2008 R2: Master Data Services at MSDev.com
Watch for more tips on SQL Server 2008 R2 Master Data Services at http://www.mssqltips.com!

About the author

Tim Cullen has been working in the IT industry since 2003 and currently works as a SQL Server Reports Developer.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2010-06-04

Tuesday, May 20, 2014 - 12:17:01 PM - Tim Cullen	Back To Top (30850)
Unfortunately it is only available in 2008 R2 and later.