Archive for March 25, 2011

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!

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.

Introduction to Master Data Services Presentation Source Code

Thanks to CTDOTNET and the 40 or so folks who showed up despite a tanker rollover on I-91.  I sometimes find that I have difficulty filling 90 minutes with content when presenting on a topic.  Not Master Data Services!  This is a surprising product to many experienced .NET practitioners (and even SQL DBAs) and the topic of MDM never fails to provoke conversation.
 
We spent the first 45 minutes simply understanding what MDM was and was not.  Then 30 minutes discussing modeling and business rules.  Before I knew it, I had 10 minutes to show some code.  Ah well.
 
The Slide Deck for those who missed it is here:
 
I also published several Source Code samples.  The first is the SharePoint Workflow which was initiated in response to a Master Data Action:
 
The second is the custom workflow extender which leverages External Actions to launch a customized Business Process:
 
Finally, this Windows App works with the Sample Customer Model and demonstrates how you might go about creating a custom UI:
 
Enjoy!
 

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.