This wiki is a transcript of a previously recorded video.

Related content assets:

  • Presentation: Introducing SQL Server 2012 Master Data Services Improvements (SQL11UPD05-DECK-02)
  • Video: Introducing SQL Server 2012 Master Data Services Improvements (SQL11UPD05-REC-03)

Introducing SQL Server 2012 Master Data Services Improvements

Welcome to this presentation introducing SQL Server 2012 Master Data Services Improvements. My name is Peter Myers and I am a SQL Server MVP and a Mentor with SolidQ.


In this presentation we’ll introduce what is new for Master Data Services (MDS) in SQL Server 2012. There are developer opportunities we can talk about, and pretty much what we discover is that Master Data Services is a SQL Server solution for Master Data Management (MDM). It was first released in SQL Server 2008 R2 and so in this release we see that there are many new improvements and features to extend beyond the capabilities initially released.


When we talk about SQL Server Enterprise Information Management (EIM) we are really talking collectively about three services that work together to deliver quality information to the organization. So we have already covered Integration Services (SSIS) in a previous presentation – this one, focusing on Master Data Services, is all about Master Data Management. That includes managing reliable, centralized data, and what we’ll discover in the SQL Server 2012 release is that the product broadens its reach with a new Excel add-in and also the ability to leverage Data Quality Services (DQS) that we’ll talk about in the following presentation. So all of these services are designed to collectively deliver complete, current, consistent and clean data with the emphasis that Master Data Services is all about delivering consistency within the organization.


I like to provide an example at this stage about where Master Data Services could be of use. I can imagine within the organization you have many different data sources, data systems that are designed to do things independently and they probably do that very, very well, but they potentially define entities and duplicate them across the different systems. For example, geography is a common one. The geography of our employees, our customers, the stores where we sell our products. And so rather than having multiple definitions and an inconsistency in the way that we report and analyze geography, it may well make sense to use a product like Master Data Services to define a master list and to maintain that and then to use that as the consistent approach for geographic analysis. And that’s just one example. In fact, Master Data Services really is there to solve multiple different problems, whether it’s just a simple matter of keeping a list of employees and their phone numbers – while that’s not a fantastic use of the product, it’s certainly viable – or whether it’s a need to keep a list of your customers in a centralized store and to maintain them in centralized way like with geography. And potentially the master list could then be used as a source to populate the structures in your data warehouse.


So new improvements and features. Master Data Manager, being the web interface, has been improved and now uses Silverlight so part of the data exploration and management uses a very slick presentation experience. Perhaps one of the most impressive features is the Excel Add-in and it supports easier data updates and management, simplified data model creation, and integration with the Data Quality Services. There’s a new staging interface that supports loading data into staging tables to then bulk load that data into the entities that you have in Master Data Services, and new subscription views that facilitate the export of data. For example an Integration Services package can simply query one of these views to load data into a table. There are also numerous improvements that are centered around usability, security, scale, and performance.


And here’s a snapshot of what the improved Master Data Manager looks like. Perhaps if you are familiar with the product from the previous release, you may notice that not a lot has changed. But when we take a look at the data exploration capability, what you are seeing here before you is an example of an entity and the members being listed. The selection of one of those members then on the right hand side makes it available for us to go ahead and view, edit or add annotations to that item.


So with the new Excel Add-in, it opens up the potential for Master Data Services to be used by a broader range of people within the organization. So master lists of reference data can be distributed to anyone in the organization that has Excel, and of course connectivity to the Master Data Service and has permission to work with it. So some ideas for use can be that general users can use it to retrieve, update and publish list data. They can also work offline. As an example, they can connect to the service; they can open up an entity consisting of members. For example, all of our customers, they can disconnect, take that long haul flight, make appropriate changes to the data and when they reconnect, they can simply go ahead and publish the changes up to the service. Now for data stewards, those that are charged with the responsibility of maintaining the integrity of our data or for administrators, it supports creating entities and attributes, using Data Quality Services to match data before loading it into Master Data Services, and in doing so it helps prevent duplicate data finding its way into our master lists. Be aware that permissions can be defined in a granular way so that will determine what users can do using the Excel Add-in.


And here’s an example of what the Excel Add-in looks like. In this slide we get to see that a list has been opened and you can see the different members displayed in the table that represents the list data. Be aware that if the user has permission, they can modify the values or even add new members to this list, and then publish those changes back to the server.


This module does include a demonstration and it shows you how to create a model using Master Data Manager – it then uses Excel to connect and import some table data to create a new entity and populate it with data. In addition, it identifies that we have problems with the data so that we modify that data in Excel or we can switch to the Master Data Manager and then explore the data, locate the member and update the change. In addition, it creates a subscription view and it shows how that creates a view in the Master Data Services database and how that simplifies the extraction of data from a list.


So for developer opportunities, there are considerations for both System Integrators and for ISVs. For System Integrators, you could look at leveraging Master Data Services to connect to disparate information systems that share master common data. And for the ISV, you can integrate with Master Data Services, allowing customers to deploy your solution and to go ahead and incorporate it into their own enterprise-wide Master Data Management solution. And this should ensure that your solution is not just an island and it stays consistent and compliant with the data that is shared across applications within their organization. Be aware then that anything Master Data Services can do, can be embedded into, or automated by, your solution by using the WCF API. For example, you can programmatically create, read, update, and delete metadata as well as create, read, update, and delete the members within your lists.


In summary, MDS is the next-generation Master Data Management solution delivered in SQL Server 2012. We see that the new Master Data Manager web interface greatly improves upon the previous releases in terms of both functionality and reliability. The service broadens its reach with the new Excel Add-in and new staging tables facilitate loading of data while new subscription views facilitate list data export.


We have some resources here for you including TechEd North America 2011, the introduction to what’s new in Master Data Services and the Excel Add-in. TechNet includes details about the Master Data Services Add-in in Excel. The MDS Team Blog is rich with information about what’s coming and why the product was designed the way it was. And then finally, Books Online for SQL Server includes, of course, comprehensive documentation on Master Data Services.


There is a hands-on lab available from wherever you have located this presentation and that will allow you to explore in greater detail the Master Data Services web interface in addition to the Excel Add-in for Master Data Services. Thanks for watching this presentation.

Return to SQL Server 2012 Developer Training Kit BOM (en-US)