Brendan Cournoyer, Site Editor
Published: 06 Apr 2010
If there is one area that Microsoft focused on when it developed SQL Server 2008 R2, it was business intelligence. Ready for general availability in the first half of 2010, the company's latest database management system (DBMS) is packed with supported BI improvements like the upcoming PowerPivot for Excel, formerly code-named Gemini.
R2 will debut less than two years after the release of SQL Server 2008, which included its own share of BI features and enhancements to SQL Server Analysis Services, Integration Services and Reporting Services.
Despite this influx of new technology, most organizations are still running SQL Server 2005 in production—and are seemingly content to continue doing so.
So what will it take to convince companies to upgrade? Likely, it will require substantial business benefits for many companies to invest precious time and resources into a migration—something that minor tweaks and improvements don't always provide.
With SQL Server 2008 R2, however, Microsoft has added one new feature to its BI platform that could prove attractive to those pondering an upgrade: Master Data Services (MDS).
Although it's new to SQL Server, MDS is a seasoned technology that was derived from a Stratature product, a data management company that Microsoft acquired in 2007.
Stratature's technology was considered a strong acquisition at the time since it was already built on SQL Server. It also filled a perceived infrastructure void for Microsoft in comparison to other enterprise vendors like IBM, Oracle and SAP.
The Underlying Problem of MDS
The main purpose of Master Data Services—and master data management in general—is consistency. This is a particular challenge for larger enterprises that host multiple databases, each with slight variations of the same attributes.
"When you want to pull all your data together and report on it, [organizations often find] that they have real data quality problems," said Kevin Kline, a technical strategy manager for SQL Server solutions with Quest Software Inc. "This is when you want to implement a master data strategy; one version of the truth, or 'reference data', as we call it."
Having multiple "versions of the truth" is the cause of most data quality issues. A simple example of this could involve two databases, each housing customer data. While Database 1 might list John Smith as working at Hewlett-Packard, Database 2 would have him working at HP. Therefore, two different "versions" of the same company exist.
When it comes to reporting, cleaning up such inconsistencies has caused database professionals considerable time and stress. Microsoft's goal with Master Data Services is to provide a simpler, more cost-effective way to keep data in sync across multiple systems.
"[What MDS] does nicely is that it enables you to build your records data," Kline said. "It gives you one version of the truth, which is a powerful thing when you have lots of different databases. So you don't have a customer with eight different company names, for example."
Herain Oberoi, group product manager with the Microsoft SQL Server business group, described MDS as an important addition to the company's BI platform. Oberoi said that when customers are building a BI solution, they don't want to have to guess where their master data reference is, adding that MDS is designed to solve this problem in multiple ways.
"There's this notion of operational master data, which is keeping systems in sync," Oberoi said. "…Then there's this notion of analytic master data, which is not just keeping my traditional line of business systems in sync, but also keeping both things in sync with my data warehouse."
The Business Case for MDS
Microsoft views Master Data Services as a natural addition to its current slate of BI features, and Kline agrees. "It aligns really well with the rest of Microsoft's BI platform," he said, but noted that it's not ideal for organizations where business intelligence isn't already a chief priority.
"It's the kind of thing that small companies might not find interesting, but once you have a BI strategy in place, you start to see the need for it all over the place. It makes sense that it would come out a few years after SQL Server Analysis Service, which a lot of people are using as their first BI tool," Kline said.
Larger companies with established BI strategies could have a much stronger interest in MDS and, in turn, SQL Server 2008 R2. While master data management solutions are nothing new, implementing such a project is traditionally a costly and tedious endeavor.
Kline said that having a service like MDS could prove to be extremely cost-effective for organizations running SQL Server. "Microsoft is making the same play with the other BI tools in that this was formerly a high-end expensive market, and now [the company is] going to make this much easier and inexpensive," Kline said.
He noted that another cash-saver for MDS comes from its process automation capabilities. "Any time you have to pay people to get this stuff done, that really adds up in terms of dollars," he explained. "A big element of MDS is process improvement and process automation. So it's much easier in that you don't have to hire a consultant anymore; you can just do it yourself."
Other MDS benefits include improved data cleansing operations and process management capabilities. The latter is designed to help guide database professionals toward properly cleaning up their data.
Room for Improvement
Even though it was derived from seasoned technology, the original incarnation of Master Data Services with SQL Server 2008 R2 is just that—a first edition. Therefore, it's not surprising that the technology still has some holes to fill when compared to competing products. This will likely open the door for Microsoft partners to put their stamp on MDS.
"The SQL Server product is a little more generic, so it won't give you the same built-in toolset right off the bat," said John Welch, a SQL Server MVP and business intelligence architect with Varigence Inc. "You might see vendors start to offer extended plug-ins to fill these holes over the next one or two years."
One notable area of improvement could involve the logic that goes with identifying different versions of the same customer. Welch said that while much of the MDS functionality is geared toward cleaning up varying attributes, it might not be as mature when it comes to identifying similar customers that are actually different people. An example could involve differentiating between customers Jon Smith and John Smith; similar names that have different spellings.
"Some products out there are more mature in doing those types of processing and are capable of finding matches and saying this one [is the same], this one isn't," Welch said. "What we'll see from MDS in the first round is not going to be as 'baked in' and effective at doing that."
One vendor that is more advanced in that area is Zoomix, a data quality software company that Microsoft acquired in 2008. While the Zoomix technology will not be a part of Master Data Services with SQL Server 2008 R2, Welch said it will likely come into play in the next version of MDS.
Still, the R2 feature stands to make a big difference for organizations looking to expand their BI strategies to ensure a higher level of data quality—particularly if they are already using SQL Server's BI tools.
"Remember, this is the tool you'd want to use after your first BI project," said Kline. "It's for when you say, 'This worked great, but we realize our data is messed up, so how do we keep it clean?' It's actually a follow-up to the BI project you've already done."
Editor's note: This article originally appeared in the February 2010 issue of our SQL Server Insider e-zine.