Microsoft introduced Master Data Services as part of their SQL Server product starting with version 2008 R2. In SQL 2012, there were several enhancements that made the product a bit more usable. We’ll be highlighting three of the major changes in this article. I will be presenting a more in-depth look at Master Data Services at a SQL Saturday even in Providence, Rhode Island on September 21st, 2013 (#sqlsat; #sqlpass).
Master Data Services web application is now Silverlight enabled
The first version of MDS in SQL 2008 R2 had a basic web front-end that allowed the user to create models, entities, attributes, and hierarchies. But it was slow. It did the job OK, but could be very frustrating when working with more than a handful of records. With any decent amount of data, the Explorer page tended to be unmanageable. MDS web site in SQL 2012 is now Silverlight enabled and responds much faster. Fast enough, in fact, to NOT require any third-party tools to simply browse the data.
The Master Data Services Add-in for Microsoft Excel
I heard mentioned, tongue-in-cheek, at a Microsoft conference once that eventually all any developer would ever need would be Internet Explorer and Visual Studio. I submit that the third essential component in that set would be the right Add-in for Excel. Let’s face it, EVERYBODY uses Excel these days. Realizing this, Microsoft has done us a great service by including the install link right on the MDS web site. Once installed, connecting to the data is very straight-forward. Simply supply the URL of the site to connect, then choose the Model and Version to work with. Adding and editing data is intuitive as well. The Add-in keeps track of changed data and highlights it as you work. In the end, the Publish button on the tool bar pushes the data changes back into the database.
New Staging table structure for Entity imports
In 2008 R2, to load a single member row into an entity with, say, 5 attributes required a total of 6 rows of data to be sent to two staging tables. The staging table in MDS 2008 R2 handled bulk imports of ALL entities, ALL attributes, and ALL relationships with only 4 tables, regardless of the number of Models or Entities. The two main tables were for Members and Attributes, with the latter being in an ‘Attribute Name/Attribute Value pair’ format. So your one row of data with 5 attributes would turn into one row of data in the Members staging table, plus 5 rows of un-pivoted data in the Attributes table. Entities with few attributes did not cause major issues, but complex attributes could tend to bloat the staging tables. In addition, if two different processes were trying to load two different entities at the same time, there was the possibility that both sets of data could be co-mingled to the one set of staging tables. This would not be an issue if all data would be staged within one batch, but trying to then process a sub-set of staging data could be difficult.
In SQL 2012 MDS, each entity now gets its own staging table for imports, with meta-data to match, plus a dedicated stored procedure to process data in that table. Not only does this eliminate the need to un-pivot the attributes to attribute/value pairs, it eliminates any possibility that entities could be mixed in the staging import tables.