SQL Server Insider

Microsoft Master Data Services boosts SQL master data management


News Stay informed about the latest enterprise technology news and product updates.

Microsoft retooling SQL Server Master Data Services after 'vexing' debut

Microsoft’s master data management product in SQL Server, Master Data Services, drew tepid reviews from users, but the company has high hopes for a revamped release.

Master Data Services (MDS), released with SQL Server 2008 R2 Enterprise Edition, is Microsoft’s offering for companies grappling with mounting stacks of all-important master data. The aim, as with all master data management (MDM) products, is to ensure the accuracy of information critical for strategic business decisions. But with users saying the interface is clunky and performance poor, whether MDS is meeting the challenge is up for debate.

Master data can provide companies with valuable information, including details about products, customers, processes or any other component essential to doing business. MDM systems capture and organize that data in an effort to synchronize analytical and operational systems, enforce compliance requirements and establish business processes that are auditable and repeatable.

According to Kirk Haselden, product manager at Microsoft, the goal of MDS is to provide organizations a way to consolidate domains of master data so downstream business intelligence (BI), analytical and line-of-business systems can consume the information.

However, most users find MDS lacking in a number of areas, according to John Radcliffe, research vice president at Gartner Inc., an analyst firm in Stamford, Conn. “It needs merge facilities, better data quality tooling, data stewardship applications, better UIs [user interfaces] for business users, better workflows for authoring and managing tasks.” Radcliffe also said the product must demonstrate scalability, availability and marketability in operational environments that are increasingly demanding.

Master Data Services: Innovation or regurgitation?
Mark Kromer, a SQL Server technology specialist for Microsoft, shared some of these concerns, saying that the initial release of MDS was mostly a repackaging of EDM+, an MDM system developed by software vendor Stratature. Microsoft acquired Stratature in 2007 as part of its strategy to deliver a unified MDM system that included SQL Server, Office SharePoint and other BI-related technologies.

Kromer said a primary reason to implement MDS is to provide data governance over an organization’s assets, enabling businesses to build data domains based on their own business models. Within these domains, businesses can create reliable, centralized data stores for analyzing their master data in order to create “a single version of the truth.”

That’s easier said than done. As Radcliffe pointed out, the biggest challenge with MDM products is not the technology, “but getting agreement on the necessary governance and organizational roles.” A product can do only so much without internal consensus, he said.

Yet even with a consensus, effective tools are needed to control the methods and processes used by data stewards as part of that data governance. Haselden said MDS addresses some of the key challenges around managing master data, and it solves the problem of how to grant secure access to the people who need it. “In the early days of the product,” Haselden said, “the team called this the human factor—basically, delivering the data to the business users in their context.”

But, Haselden said, the current release of MDS is not without its problems. For example, in feedback to Microsoft, users have reported that the UI is difficult to navigate. “Improved functionality is a priority,” he said. “We know about the issues and are working to improve them.”

Looking ahead with Master Data Services
The next version of MDS, which will be released with the upcoming SQL Server 2012, formerly called Denali, was designed to address many of those issues. According to Kromer, the new version will demonstrate that Microsoft has heard what users have been saying: The Web UI will be more intuitive and better geared toward business users. Page navigation will be improved through the use of the Microsoft application framework Silverlight. The workflow will be enhanced. Data staging will be simplified. And the product will be better integrated with SQL Server’s BI tools.

“The Denali release of MDS starts to make this a much more integrated ecosystem with the SQL Server product suite,” Kromer said, “which I think will go a long way in increasing the usefulness of MDS and the customer adoption rate.”

Microsoft released the revamped MDS features in a community technology preview (CTP) of Denali in July, and users who’ve worked with the new version report that they’re seeing improvements in the product. According to feedback, performance is better and the UI is more user-friendly, partly because it has been rebuilt in Silverlight.

Perhaps the most popular MDS feature in the Denali CTP is a new add-in for Excel. Haselden said Excel integration will further address concerns about the UI, improving the look and feel “to the extent that most business users will use Excel as the preferred experience.”

Radcliffe was not so sure. Although he agreed Excel might be beneficial to people accustomed to the program, he said it should be only one of several UIs offered in MDS. “For example, many competitive MDM products have graphical UIs that allow you to visualize, transverse and manipulate hierarchies,” he said. “I’m not sure that Excel is the best tool for that. And on the data stewardship side, again, people are looking for very visual analysis and reporting tools.”

Microsoft might not have any choice but to deliver the tools it has, given the number of MDM products crowding the arena, such as SAP NetWeaver MDM, Oracle Master Data Management Suite, IBM InfoSphere Master Data Management Server and Informatica MDM. As Radcliffe points out, MDS comes at a good price -- it’s free -- and that’s definitely worth considering. Even so, he said, MDS “is still a long way behind the MDM competition.”

A peek inside Master Data Services

MDS organizes master data into models. Each model contains a single domain of data—that is, data related in a meaningful way, such as product or customer information. Within each model, you can configure a set of objects that correspond to objects in the MDS database:

  • Entity. A set of related data that corresponds with a database table.
  • Attribute. An entity property that corresponds to a table’s column.
  • Domain-based attribute. An attribute linked to another entity’s attribute (often corresponding to a column’s foreign key).
  • Attribute group. A collection of attributes used to group data in the MDS interface.
  • Member. An entity record that corresponds to a row in the database table. Leaf and consolidated members support explicit hierarchies. A leaf member is at the lowest level of the hierarchical structure. A consolidated member can serve as parent to a leaf member or other consolidated member. A collection member is part of a collection.
  • Attribute value. A specific data value in a member (corresponds to a row’s value in a database table).
  • Hierarchy. The hierarchical organization of members. Derived hierarchies are based on the relationships established by domain-based attributes. Explicit hierarchies are groups of data organized according to a user’s requirements.
  • Collection. A user-defined subset of members.
  • Business rule. Business logic stored as a SQL expression and applied to imported data.
  • Subscription view. A view created specifically for systems accessing the master data.

Think of MDS as a front-end application that lets you store and manage data within a SQL Server database. The application automatically takes care of table structures, relationships, constraints, permissions and other database components, letting you focus on the data itself.

Robert Sheldon is a technical consultant and the author of numerous books, articles and training material related to Microsoft Windows, various relational database management systems and business intelligence design and implementation. Find more information at

Article 2 of 3

Dig Deeper on SQL Server Business Intelligence Strategies

Join the conversation

1 comment

Send me notifications when other members comment.

Please create a username to comment.

To build mission critical applications and Big Data solutions using high performance, in memory technology across OLTP, data warehousing, business intelligence and analytics workloads without having to buy expensive add-ons or high-end appliances. SQL Server 2014 uses a common set of tools to deploy and manage databases both on-premises and in the cloud, which makes it easier for customers to take advantage of the cloud with existing skills.