In SQL Server 2008 R2 Master Data Services, one of the first design decisions you will need to explore is: How will we uniquely identify a master data record? In MDS, each member has a Name and a Code. You cannot remove either of these attributes, alter their data type, or alter their length: they are not User Defined Attributes. The Code attribute is always required and always unique for the member. Therefore, the “primary key” column for your master data is always Code.
In many cases, you may wish to have this Primary Key attribute be an auto-generated number, just like the familiar IDENTITY concept in SQL Server databases. This design decision avoids having perform in-depth data profiling to figure out of there is a more natural unique identifier (i.e. DUNS number for customer, UPC for Product or SSN for Person) which could be used. However, the correct way to do this is obscure and difficult to glean for 3 reasons:
- You cannot change the data type for Code in the System Administration area of the MDS Stewardship Portal
- When creating an Entity Member in the WCF layer, you are required to submit a Member Code or the operation will fail
- When Bulk Loading Members into the MDS Hub, you are required to provide a Member Code for members in the tblStgMembers table
I list these three items for a simple reason: cumulatively, they had convinced me that you could not auto-generate a Member Code in MDS. Indeed, I have stated repeatedly that this was the case, both to colleagues and innocent user group presentation audiences. Mea Culpa! There is a way.
First, you need to create a business rule which performs the auto-generation process. MDS provides an action in the business rules designer for this purpose.
You will need to drag the Code attribute from the Member Attributes list to the “Select attribute” item shown above to indicate that Code is the attribute to assign a generated default value. This will have an immediate impact on the user interface in the MDS Stewardship Portal. The next member that you create will not display a textbox for Code.
Apparently, the UI has enough metadata information about Code to know that the value should be auto-generated, not entered. Great! But what about the WCF service layer? And the Bulk Load / Staging tables?
The WCF layer will return an error “The Member Code is required” in the OperationResult object returned by EntityMemberCreate method. So what should we pass in? If we pass a real value, MDS should respect that value and refrain from asserting a default value. To prove this theory, I added this business rule to a pre-existing test model (one where the Codes where all set to an alpha-numeric value). When I ran the rule, the assigned values had not changed.
The answer to the question is that MDS is actually assigning a dummy code to the member: a dummy code which MDS understands can and should be overwritten. To watch this occur, exclude the business rule you just created and publish the change. Now return to the Explorer and enter a new member record. Note that the Code textbox remains hidden. Create a name and hit the Save and Back button. When you search for the member you just created, you will see a generated code which is a GUID prefixed with the string “#SYS-”
The prefix tells MDS that this is a generated value. Now return to System Administration, uncheck the Exclusion option, re-publish the rules, and run the Business Rules against your member to generate a code for it.
Therefore, the second thing you need to do is ensure that all calls into the WCF Layer to perform EntityMemberCreate operations provide a dummy code in the specified manner. This easy enough to do, now that we know how.
Note that in the above example, “MDS” is named as an alias for my service proxy in a using statement not shown (I recommend that you do the same).
Finally, you will want to create a similar dummy code when creating members through the staging infrastructure. If writing straight Transact-SQL to insert records into tblStgMember, use newid() to get a guid; SSIS requires a different syntax. You can use this very same code for tblStgMemberAttributes and tblStgRelationships for bulk updating User Defined Attributes and Hierarchy Memberships, respectively. When the business rule runs for that member, MDS will cascade the changed code through the hub, so that nothing is lost in the change.
Thanks to Pam Mathews at Profisee for setting me on the right path.
Brian Berry is a Director of Technology Consulting with BlumShapiro, focusing on Microsoft Business Intelligence solutions, with a strong focus on Systems Integration, Master Data Management and PerformancePoint Services. He has been helping companies optimize their investments in Microsoft technology for over 12 years.