Problem solve Get help with specific problems with your technologies, process and projects.

Scenarios abound for SQL Server Master Data Services

SQL Server Master Data Services, Microsoft’s master data management service, can be put to a number of different uses in businesses concerned about data quality and consistency.

SQL Server 2008 R2 introduced a feature with major implications for people concerned about the quality and consistency of their data: Master Data Services (MDS). It sounds impressive, but what’s it really all about? And why would you want to take the time to use it in the first place?

MDS, MDM explained
MDS is an implementation of what’s called master data management (MDM), a philosophy and a set of procedures for handling data. MDM is a way to make sure that data is distributed and presented throughout your organization in a consistent way.

It sounds like it should be simple -- just move everything into one master copy -- but in practice it can be quite complex. Let’s say a very large organization may be tempted to have multiple, redundant copies of a given set of data -- for example, a branch office’s copy of a master customer database, which it uses instead of connecting to a centrally managed database somewhere.

Or perhaps two organizations with somewhat similar sets of data (again, for instance, a customer list) merge and find that combining the two into a single consistent master list is harder than it looks for a variety of reasons. Maybe the two sets are formatted differently -- for example, first names come first in one and last in the other; one database has columns the other doesn’t; or the two were improperly compiled into a single copy and need to be redone.

Because keeping data clean isn’t something you just do once, but again and again in a variety of contexts, master data management requires a process -- a methodology. And it requires a set of standards detailing how data is handled, implementing that methodology.

How MDS does MDM
MDS consists of a set of structures and processes for handling data that work in parallel to but are strongly influential on the way you use data now. It comes with its own terminology for handling data, which might seem arbitrary and arcane from the outside. By now most anyone who deals with a database knows what rows and columns are, what a query is, what a snapshot is.

SQL Server Master Data Services uses a different set of terms for describing data, all from the point of view of consistency and management. For instance, the most top-level unit of organization in MDS is not a database per se but rather a model, which describes how data is grouped and organized. Models contain entities, which are vaguely analogous to tables of master data in a database, which in turn contain members (roughly, rows) and attributes (columns).

The MDS description of a database is different from the way the database looks to a DBA, because the actual in-database representation of things like models and entities don’t directly correspond to any one database object. It’s also because the data design is entirely up to you. With MDS, you have to not only provide the data but design the models and hierarchies that govern the data, all created from the ground up. This means much more work, but it also means you can apply MDS to more things than traditional out-of-the-box business scenarios, like inventory tracking and customer management.

MDS is not specifically for large organizations: most any outfit of any size that needs MDM-style consistency can benefit from it. That said, MDS is included with versions of SQL Server 2008 R2 that are aimed mainly at larger deployments: Enterprise, Data Center and Developer. It’s not available in the Web, Compact or Express editions of SQL Server 2008 R2, and its inclusion in the Developer edition is solely to allow work to be done with MDS outside an immediate production environment. (In other words, it’s not so you can cheat on the licensing.) MDS will also be included with SQL Server 2012 when that ships later this year, in its Business Intelligence and Enterprise editions.

So why would you want to use MDS? Here are a few common scenarios:

You want consistent implementations of data. This is the most common reason to use MDS: You have data sets that must be consistent throughout your organization. These sets do not even have to be all that large, although the larger the data set the more complex the management job it’s going to be. Bear in mind complexity isn’t correlated with size alone: You can have a relatively small data set that is complex because of the hierarchies or inter-relations between the data elements. Or you may have data attributes -- the color of an item, say, or its location -- that need to be strictly controlled.

You want to define and maintain hierarchies for data elements. Hierarchies are exactly what they sound like: ways to describe how members (individual rows) are organized into vertical categories or trees. Hierarchies help with how things are tabulated, since each item in a hierarchy is represented once -- the way, for instance, a particular sibling only appears in a single location in a family tree. This ensures that members are not counted more than once or just flat-out not counted. MDS gives you a way to create and manage hierarchies automatically, instead of having to develop such things from scratch.


You want to enforce rules about the data and put specific people in charge. MDS provides a methodology for ensuring data entered into a system governed by MDM follows rules you set down. This can be used to constrain newly entered data or to make sure a version of already entered data is valid. That can be useful if you’re dealing with consolidated pre-populated data sets. What’s more, MDS provides you with the ability to notify people by email if an attempt to set a value doesn’t pass the rules.

You want native version control and permissions control over your data. When you create a master model for your MDM data, you can also generate and audit multiple revisions of that data. The version control system in MDS gives you a native mechanism for doing this so you don’t have to cough up an ad hoc solution and create more work for yourself. MDS also gives you permissions tools so that you can delegate who gets to see or update what across models and hierarchies.

The more of these needs you have at any one time, the better the odds using MDS will be a good way to fulfill them. That said, bear in mind using SQL Server Master Data Services is not a trivial operation -- it’s not like simply writing a new view for existing data. It’s architectural, meaning you have to plan an entirely new way for the data to be held, processed, verified and released. Implementing it is best thought of as migrating to a new data design; in essence, that’s what you’re doing.

Serdar Yegulalp has been writing about computers and IT for more than 15 years for a variety of publications, including
InformationWeek and Windows Magazine.

Dig Deeper on SQL Server Business Intelligence Strategies

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.