Monthly Archives: January 2013

Speaking at Microsoft TechDays 2013 in Helsinki

tommi_techdays

I will be speaking at Microsoft TechDays 2013 conference on March 6th. In Solita we have leveraged MDS in many DW/BI projects to tackle down the analytic master data management challenges that our customers have encountered. In my session I will present how to use MDS for analytical master data management by going through some examples of the solutions that we have created including

  • using MDS as a centralized repository for the analytical master data (dimension data), maintenance and change management
  • enriching the master data entites to complete dimension data and fully satisfy the reporting and analyzing needs of the business units
  • harmonize and combine master data from multiple sources to achieve robust, accurate and consistent dimension data

The full agenda for the two-day event can be found here: http://www.techdays.fi/fi/ohjelmaopas

See you in March!

Advertisements

MDS auto-generated Code value – how to import new members via staging tables?

Creating the business rule is straightforward but problems start when it comes to importing data via staging tables.

Recently I had to set up an entity that had to have an unique code across the enterprise. In SQL Server 2012 version of MDS this is achieved quite easily just by checking the “Create Code values automatically” checkbox when creating the entity. In 2008 R2 version we have to leverage the business rules feature.

Action "defaults to a generated value" comes handy.

Action “defaults to a generated value” comes handy.

First, create a new business rule and choose the “defaults to a generated value” action and drag Code-attribute into the “Edit action”-section. Starting the incremental generation from number 1 is fine. Now we are ready. At least if we only use MDS web user interface to add new members to our entity. In my case I need to import the data from source system(s) and therefore I need to use staging tables.

Importing via staging tables new members that will have an auto-generated code

First try
One can not insert NULL values into the MemberCode column.

One can not insert NULL values into the MemberCode column.

First of all, how the hell I’m supposed to insert any new members into the staging table since the MemberCode-column is not nullable? Luckily I found this MSDN Forum Thread where I was told to assign the legendary “” – empty string value to MemberCode. OK, works out well but the problem is that I also want to import some additional attributes (other than the mandatory Name and Code attributes) via tblStgMemberAttribute-table and there I need to have the unique MemberCode that is assigned after the model is validated or the business rules are executed. How am I supposed to assign the correct attributes to correct member if I don’t know the unique MemberCode because it’s generated after the model is validated? Now what?

How to import additional attribute values if we don't know the Code value when importing?

How to import additional attribute values if we don’t know the Code value when importing?

Second try

We’re now facing bit of a “Chicken-and-egg problem” here. Luckily I came across another blog post that handled the very same issue. The trick is quite simple now when I think about it afterwards. The point is that when the new member records have been imported to MDS with empty string as their MemberCode value MDS will assign a special system-guid to the newly imported members:

Special "#SYS-"-prefixed GUID Codes are assigned to members that were imported with empty string as membercode.

Special “#SYS-“-prefixed GUID Codes are assigned to members that were imported with empty string as membercode.

These “#SYS-“-prefixed GUID values are only temporary because when the business rules are executed MDS replaces them with the value that we have set when defining the business rule. So how does this help us since the #SYS-values are still assigned AFTER the import and we need them BEFORE we import the rows via tblStgMemberAttribute-table? We simply “emulate” the MDS’s internal process by assigning the system guids by ourselves already in the loading phase by generating the unique system guids for example using the newid()-function in SQL Server.

Generating the #SYS GUID on our own by newid() function call.

Generating the #SYS GUID on our own by newid() function call.

Now, by assigning the system GUID ourselves to the tblStgMember and tblStgMemberAttribute tables we can successfully import new members with additional attributes and get the correct auto-generated code values by validating the model.