Archive for July 30, 2013

Three Major Changes to Master Data Services in SQL 2012

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.

One Simple Way to Reduce Email Clutter

Have you been CC’d before on emails that you really should not have been CC’d on the first place? The old way of handling these emails were to simply delete them as they come in. However, in larger email chains there may be 5, 10 email replies or more which quickly adds up in time and lost productivity. A fast and easier way to reduce those emails is to highlight the message in your Outlook inbox and click Ignore on the ribbon. Now if there are more replies to that email conversation, they’ll be automatically deleted before you even see them in your inbox.

MS Outlook

Quick Note: You do need at least Office 2010/2013. If you don’t have it installed, we recommend Office 365 which gives you access to the latest Office version (Office 2013). Sign up for a free trial if you’re interested in trying this and other features out!

Office 365 E3 Trial (30 Days)