Compared to the 2008R2 version you can now save even 75% of development time.

Microsoft just released the new SQL Server 2012 version. There are lots of new features included but one major improvement deals with Master Data Services (MDS) application. It has now moved to its second major release and it is now shipped with SQL Server 2012 Enterprise and Business Intelligence licenses. I don’t go into details that much but what I want to point out here is that the new 2012 version of MDS has remarkable improvements when it comes to user experience and the productivity of development work.

Biggest and maybe the most important improvement is the new Excel-add-in that can be used to modify data but also to create new entities and to import new data from scratch. Where the 2008R2 version relies only on the (clumsy) database staging tables in 2012 one can now use Excel to import batches of new data. This is of course good news for new users that are already familiar with Excel. The second big improvement is the new Silverlight-based web user interface. It has been completely renewed and I can tell that now it is really ready for end users. Unfortunately the initial 2008R2 version still suffers from nasty bugs and the usability is quite poor.

What I will now show you is a comparison between MDS2008R2 and MDS2012 and how they differ from each other when importing new data and creating entities.

Creating new entities and importing data

Let’s face it: I have a simple Product – structure that  grabbed from the Adventureworks – sample database. 5 entities stored in separate csv-files:

• ProductCategory
• ProductSubCategory
• ProductModel
• Color
• Product

What we will next do is

• Create a new Model to store the entities
• Create 5 entities and the corresponding attributes + relationships: Product – SubCategory – Category, Product – Color and Product – ProductModel
• Import data into entities
• Create a derived hierarchy ProductCategory – ProductSubCategory – Product

Master Data Services 2008 R2

Create entities manually

In the old version we have to do it the hard way: first create the new entities in the web UI and then import the data via database staging tables using a proper ETL tool. I preffered SSIS.

This phase took me approximately 6 minutes to complete. Now I have the entities structure ready in MDS so it’s time to import some data.

Import data by using SSIS and the MDS staging tables

This is the most time-consuming part of the process. You have to create separate data flows for each entity and you also must do some unpivoting of the data when importing entity attributes. After each data flow you must go into the Import/Export  – page in the web UI and start the importing process manually. (OK, you can do all this automatically by using the web service calls and all that stuff but this approach is still the fastest way at this point).

Importing the attributes for each entity is bit tricky as you need to unpivot the source data into separate rows.

After a while and some serious SSIS-work we have 5 entities and a nice Product hierarchy set up in MDS:

The bad thing is that it took almost ~ 30 minutes to accomplish all this. With all the work that you have to do in the MDS Web UI we end up with appoximately total 40 minutes of work.

Master Data Services 2012

Create entities and import data

Now we’re talking. With MDS2012 you don’t actually have to separate the creating entities and importing data phases because now you can do them both at the same time! Starting with ProductCategory entity we just import the csv data into our Excel sheet and then connect to the MDS server and hit the Create Entity – button on the MDS ribbon.

Then we just choose right columns for code and name usage and we’re ready. As easy as that !

Handle domain-based attributes

The first thing that came to my mind when hearing about the new Excel-add-in was that if it was capable of dealing with domain-based attributes. And guess what, it sure is. Like we did with ProductCategory entity we do the same for ProductSubCategory. Notice here that there is a relation between SubCategory and Category and we have to handle it also correctly. That can be managed by using the Attribute Properties – functionality in the MDS ribbon.

Choose Constrained list (Domain-based) as the attribute type and populate values straight from the recently created ProductCategory entity:

Nice and easy! We now have succesfully created ProductCategory and ProductSubCategory entities and formed a relationship between. All this in ~ 3 minutes of time. After repeating the same steps for the rest of the entities (ProductModel, Color and Product) we end up with the same result as in 2008R2 but only in 10 minutes of development time. Saving 75% of time compared to the process in MDS2008R2.

Extra mentions about MDS2012

When talking about how to automate data imports that’s where the staging tables are coming into picture also in MDS 2012. Now it’s also more user friendly since you don’t have to import entity rows and corresponding attributes in separate tables and you don’t have to unpivot the columns into rows in the tblStgAttributes – table. In MDS2012 there is a separate table for each entity and it is 1:1 with the entity definition.

What really completes the whole package is the also brand new Data Quality Services (DQS) application that works nicely together with MDS. More about DQS later …

Summary

When Microsoft launched the initial version of MDS in May 2010 it was a classic “first version” of the (recently acquired)  product: missing features and minor bugs here and there. Now the second major release really finds its place in the hearts of the users and developers since there are lots of really good improvements that make it a better product.

So, if you are planning to start a fresh MDS project my honest advice is: don’t start start with 2008R2, do it with SQL Server 2012.

-GD