Master Data Services in SQL Server Denali - Preview in CTP3

Here is a quick peek at some of the enhancements to Master Data Services (MDS) in SQL Server 2012 (formerly code-named Denali) as of CTP3. Before I do that,though, let’s take a look at what MDS is from a SQL Server product perspective …

MDS is Microsoft’s MDM tool that is included in SQL Server 2008 R2 Enterprise Edition and above. The lineage of the product is from the Microsoft acquisition of Stratature and the MDS release in SQL Server 2008 R2 was the first release of the Statature IP with Microsoft. MDS is meant to be a tool that you can use to build data domains around your critical business entities like products, customers and employees and to use MDS as the tool in your master data management (MDM) projects to provide centralized data governance that creates single version of the truth repositories. The final step is to integrate MDS with your operational and analytical systems with common Microsoft data integration tools like SSIS and BizTalk.

What MDS in Denali CTP3 provides is a big step forward in terms of the usability, applicability and analytical MDM by integrating with a brand new capability in the SQL Server data management suite called Data Quality Services (DQS). Ok, so without further ado, I will get you started with 3 new features in MDS in SQL Server Denali CTP3 (download it here) that I’ll highlight and then you can go and explore the rest of the suite in CTP3.

Improved Web UI

I’ll be honest, no matter what product I’m talking about, saying that a new version has an “improved Web UI” is just simply not a major feature of the product. An example is the way that Oracle has been touting the UI improvements in Fusion Applications over the Web-based navigation UI on some of their ERP apps. The MDS Web UI is meant as a tool for data stewards and so, like ERP systems, they are not going to be glamorous user interfaces. I’ve pasted a screen capture below which doesn’t really do it justice. But what is important to note is that if you are a current user of MDS in SQL Server 2008 R2 and you find the Web UI difficult to navigate, you will first notice that the interface looks similar to today’s Web UI. But it is now built with Silverlight with much more natural in-place navigation. You’ll definitely enjoy this experience much more! But your business data experts and data stewards will probably be much happier and have a much more pleasant end-user experience performing data management tasks in MDS using their favorite tool, Excel.

Excel Add-in

The trend that Microsoft began even before SQL Server 2008 was released, was to take advantage of the fact that Excel is the world’s most popular and most used data management and BI tool. PowerPivot, MDS, DQS, etc. are all using Excel as the home base for your business users. Excel is being loaded-up with Add-ins and in the CTP and when DQS becomes available, you can use Excel as the tool to manage your business entities. Keeping business users happy and in the tool that they use most of the time without the need to relearn other UIs.

Integration with DQS

What I find really exciting are the opportunities to perform real EIM or Enterprise Information Management with tools from Microsoft that reside within the SQL Server data management platform. DQS is a new addition to the suite and is the first release from Microsoft that takes the acquisition of the IP from Zoomix and incorporates common tasks performed in data management by data stewards like deduplication and data cleansing. What is additionally helpful is that Denali also includes a new SSIS transform task for DQS. So SSIS can serve as your integration engine between your operational or analytical systems and your cleansed reference data in MDS.