Where is my “Homeless” Master Data?
The first question to be asked in any Master Data Management project is: Where is my Master Data? The prevailing assumption seems to be that master data lives in an ERP table called “the Customer Master” or “the Item Master” for example. From here, project stakeholders focus intensely upon making the ERP data complete, aligned and in-sync. These analyses are all valid and important.
But consider the analytical side of MDM: analytical databases provide the ability to aggregate and roll-up similar entities, or concepts. Therefore, reporting systems (OLAP or Business Intelligence systems) need and thrive upon data consolidation concepts – roll-ups, hierarchies, collections of master data which can be used to construct dimensional analysis.
For example, a customer may be a stand-alone business, but more often a place of business is owned by a legal entity. Credit Analysts want to see the total credit being extended to a business, not simply a customer. In manufacturing, the phrase “chain” is common to describe essentially a collection or consolidation of customers. In order to provide intelligent customer chaining, the master data needs to include these kinds of “sibling” relationships.
Some ERP systems do this well, and therefore are able to offer analysts a tightly integrated Business Intelligence experience over the ERP data. But no matter what your ERP system, this approach assumes that the enterprise is under a single ERP; indeed, this is rarely the case. If you are an organization which has grown by mergers and acquisition, you may have dozens of ERP systems in the enterprise, at varying level of capability. Those that do have BI capabilities often promote a fairly rigid, out-of-the-box solution to hierarchy management, incompatible with other systems.
So what happens? These consolidations become mappings tables in Excel and Access applications and analysts continually scramble to keep their version of this institutional data up to date. These common data assets are essentially “living on the streets” – not inside an ERP system and not inside an MDM solution. And the astonishing thing here is: this data is highly valuable master data. Without it, the enterprise has a hard time getting data into the system.
It’s this recognition, I believe, which has driven Microsoft in SQL Server 2012 to deliver an Excel 2010 Add-In for Master Data Services. This add-in should help ease the transition for analysts and Information Workers who have taken the homeless data in. Master Data Services provides a full-featured MDM home.
In: Uncategorized
Auto-Generating a Master Data Member Code
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.
Now Officially Partners with Profisee
BlumShapiro is now one of a select few consulting firms working directly with Profisee to deliver rich tools for SQL Server 2008 R2 Master Data Services.
This makes life in our Microsoft BI and Master Data practice much easier because I can freely demo both the Master Data Maestro Client Application and the Master Data Maestro server solution coming in Version 2 of the product.
Version 1 Master Data Maestro offered several benefits to organizations looking to leverage Master Data Services:
- Workspaces for Data Stewards working with Master Data Day-in Day –out
- Merging to the “Golden Record” enabling native merge capabilities found in the WCF Services layer which are difficult to leverage out-of-the-box
- Hierarchy Navigation and Management because nobody wants to manage a Hierarchy in a browser (trust me)
Version 2 adds a Server component to the product which a key Data Quality ask for nearly every one of my clients: Address Standardization with Bing Maps. I can’t wait to get the beta installed in the Blum Lab.
Cheers,
Brian
Just Announced: Digital Asset Management in the Cloud has arrived!
I have had the pleasure of working with Equilibrium on some competitive opportunities, promoting SharePoint 2010 as a collaboration platform for digital asset management – great folks and a great product! Today I learned that they are partnering with NetConnect’s Team Portal Platform to deliver a SharePoint online offering of their Media Rich ECM DAM product. This is great news for companies looking to manage digital assets in the familiar SharePoint 2010 world. Low TCO just got even lower!
A free trial of the service is available here: https://equilibrium.teamportal.com/Default.aspx
Enjoy!
Clearing the Master Data Services Staging Tables
In order to clear the Staging tables from Master Data Services, it is easiest to simply call a stored procedure.
exec mdm.udpStagingClear
@User_ID,@StagingType_ID,@DeleteType_ID, @ModelName, @Batch_ID
To get your @User_ID, refer to the mdm.tblUser table.
@StagingType_ID is required, but only used when @DeleteType_ID = 0 or 1
The @DeleteType_ID Parameter controls how the staging table is cleared.
|
Value |
Description |
|
|
@DeleteType_ID |
0 |
Delete by Model and User. |
|
@DeleteType_ID |
1 |
Delete by Model and User the staging records that have processed successfully. |
|
@DeleteType_ID |
2 |
Delete by User. |
|
@DeleteType_ID |
3 |
Delete by Batch, including the batch record. |
When @DeleteType_ID = 0 or 1, @ModelName is required and you should pay attention to the @StagingType_ID.
|
|
Value |
Description |
|
@StagingType_ID |
1 |
Delete Members |
|
@StagingType_ID |
2 |
Delete Attributes |
|
@StagingType_ID |
3 |
Delete Relationships |
|
@StagingType_ID |
4 |
Delete All |
I prefer to use @DeleteType_Id = 3 so that I clear the tblStgBatch table as well, but @DeleteType_ID=1 is also appealing, as it bulk clears while retaining failed loads. Also, you really should only use this for Initial Data Loads of Master Data. For all other write operations, I recommend the WCF Service.
Enjoy!
Speaking of SQL Master Data Services
Yeah! I’m so glad that SB asked me because after SharePoint Saturday Hartford (of course you were there) I was thinking to myself – I need to get up and talk to people more about Master Data Services.
Anyway, I enjoy this group. They meet at the Microsoft office in Farmington, CT every 2nd Tuesday of the month. Nice group of people and I have seen several excellent presentations given there. My talk is on March 8, 2011.
I’m not sure when the event starts, but you can find out here: http://ctdotnet.org/default.aspx
I’ll talk about what Master Data Management is, why companies care, and the of course SQL Server 2008 R2 Master Data Services. We’ll talk architecture, modeling, rules, getting data in, getting data out, and even demo some SharePoint code!
Hope you can make it.
In: Presentations and Events
TypeMock Isolator for SharePoint
<Begin>
Typemock are offering their new product for unit testing SharePoint called Isolator For SharePoint, for a special introduction price. it is the only tool that allows you to unit test SharePoint without a SharePoint server. To learn more click here.
The first 50 bloggers who blog this text in their blog and tell us about it, will get a Full Isolator license, Free. for rules and info click here.
<End>
I am currently working on a complex SharePoint application which includes over 15 webparts, custom workflows, feature handlers, list event handlers – you name it. So, I could really use this tool!
In: Uncategorized
Solution Development with SharePoint Extensions Part 1: Planning Your Features
The WSS Extensions for Visual Studio 2008 1.2 are terrific – a big improvement over the tools provided in VS2005 – for a number of reasons. One of these is the WSP View tool.
The first time you create a SharePoint solution with the extensions, you will notice that the files which are needed to deploy are generated for you by the extensions when you click “Deploy”. No more handwritten solution manifest files (yes, I admit, I have done this – I even created a DDF file for makecab.exe to use in order to create my .WSP files. Not fun.) This is great, but the tools cannot infer the arrangement of SharePoint assets into features. Instead, it simply assumes that each asset is its own Feature. Therefore, if you create 10 Web Parts for your solution, the extensions will, by default, create 10 features for you and deploy them to your development server.
OK, not a big deal at development time, but when we are ready to start thinking about deploying the code to an Integration Server, we will want to consolidate a bit. Ideally, we should have control over how the features are packaged without having to hand-write the changes which come out of our build server.
WSP View is a solution manifest viewer which works inside Visual Studio to do just this. You can drag and drop your feature elements from feature to feature, until you have a set of features which have manageable dependencies.
In the example above, I created a List Definition named “Tracking Report Card”. The List Definition has several Views which are represented as ASPX files. I then created an Event Handler for the List Definition so that I can respond to events within the list. When I created this, Visual Studio created 3 features. Since the List Definition, List and Item Event Receivers are all inter-dependent. It makes sense to package them as a single feature with multiple elements.
WSP View makes this much easier than having to hand-change the feature definition files – too many GUIDs!
In: MOSS 2007 · Tagged with: SharePoint, Visual Studio
Microsoft Business Intelligence Conference 2008 Recap
I had a tremendous opportunity earlier this month when I travelled to Seattle for the 2nd Annual Microsoft Business Intelligence Conference. There I was able to meet with nearly every Program Manager on the PerformancePoint Server Planning team, customers who had actually implemented PPS-P and lived to tell the tale, and some Brit bloggers at Adatis Consulting whom I like to keep tabs on. Anyway, I received an email this week informing me that the Conference DVD will be arriving in December 2008 and I am looking forward to sharing it with colleagues.
In: Presentations and Events
Executive Seminar in Hartford, CT
I had the opportunity to attend an excellent presentation on Corporate Performance Management yesterday, given by my colleagues David Putt from Blum Shapiro and Michael Hollenbeck from Microsoft. David began with an overview of CPM and the value proposition for companies in the mid-market. Traditionally, these companies suffer from a disproportionate amount of time wasted on “low value activities” – collecting and consolidating information, or simply just rooting out the “real measurement” from a set of contradictory versions of the truth. The result is an organization which is not effectively working together to pursue strategic objectives. David’s specialty is helping finance departments escape from what he is calling the “Spreadsheet Quagmire”. His thoughts are in a White Paper on the subject on our public web site.
Michael Hollenbeck is “The BI Guy” at Microsoft and after his presentation I can see why. He used the standard “All Up” demo image (you’ve seen it, right?) very effectively to present a vision for the audience of a fully integrated PM cycle. Sure, the demo was straight out of the high level script, but I learned a great deal from the way he delivered the message. He cited compelling examples of companies who have really turned the corner in the way they work together in an integrated PerformancePoint Server environment.
In: Presentations and Events