(this post was contributed by Suzanne Selhorn, Technical Writer on the MDS Team)
This article is applicable to Microsoft SQL Server 2008 R2 Master Data Services feature.
To populate the Master Data Services database with data from your organization's disparate data sources, you can import data into staging tables and then process the staged data as a batch. This post provides some basic information about the staging process. It introduces you to the database tables used for staging and the workflow required to import data.
For staging examples and tips for troubleshooting, see my post: Staging Examples and Troubleshooting.
Update Notice for SQL Server 2012 users: This blog article is applicable to SQL Server 2008 R2 Master Data Services. MDS Staging was vastly improved in SQL Server 2012 MDS release and is called Entity Based Staging. Please watch this video intro and refer to product documentation on the new 2012 MDS staging concepts.
The Master Data Services database includes three staging tables that you can populate with data and one batch table that tracks each batch of staged data.
Staging Process Workflow
mdm.tblStagingMember - Use to create new leaf members, consolidated members, and collections.
mdm.tblStgMemberAttribute - Use to update the attributes of existing members and collections.
mdm.tblStgRelationship - Use to add members to a collection or move members in an explicit hierarchy.
mdm.tblStgBatch - Displays the status of each batch of staged data.
Note: You must have INSERT permission to the staging tables in order to populate them with data.
During each step of the staging process, fields in the staging and batch tables are updated. The following workflow explains how and when the most important fields in each table are updated.
Step 1: Populate the staging tables
When you initially populate the three staging tables (tblStgMember, tblStgMemberAttribute, and tblStgRelationship), the Batch_ID for each record should be NULL. The Status_ID field for each member will default to 0, which indicates that record is ready to be processed.
The batch table (tblStgBatch) does not yet have a record for the batch.
For examples of how to populate the staging tables, see my post: Staging Examples and Troubleshooting.
After you have imported data into the staging tables, you can open the Master Data Manager user interface (UI) to confirm the number of records available to process. In the Integration Management functional area in the Unbatched Staging Records pane, choose a model. The number of records available to stage is displayed. This is the total number of staging records that contain:
- The model in the ModelName field.
- The logged-in user's name or no user name in the UserName field.
Step 2: Invoke the staging process
To invoke the staging process in the UI, select the version to import data into and click Process unbatched data. Or, for an example of how to use SQL scripting to invoke the staging process, see my post: Staging Examples and Troubleshooting.
When the staging process starts, a row is added to the batch table. An ID is assigned in the ID field and the Status_ID field is updated to 1 to indicate that the batch is queued for processing.
Each row in the staging tables is updated with the batch ID from the batch table. The Status_ID remains 0 to indicate that processing has not started.
Note: Staging batches that are queued to run are processed in sequence, and processing begins at an interval determined by a setting in Master Data Services Configuration Manager. The staging tables are processed one after another. tblStgMember is processed first, followed by tblStgMemberAttribute, and then tblStgRelationship. You cannot initiate the processing of one table at a time.
Step 3: Batch completes
As each row in the staging table is imported into the appropriate Master Data Services database tables, the Status_ID field for the row is updated with 1 or 2 to indicate success or failure, respectively. Each row’s ErrorCode field is also updated. These error codes are displayed along with descriptions on the Staging Batch Errors page in Master Data Manager.
When all members in a batch have been processed, the batch table Status_ID field is updated to 2 and the LastRunEndDTM field displays the date and time that the process completed.
Records that were successfully loaded are now available in the Explorer functional area of Master Data Manager.
Note: ERR210000 is not an error and appears on successfully loaded records.
The following illustration shows a summary of the workflow.
Note: Members remain in the staging tables until you clear them. To clear the records, in Master Data Manager, click Integration Management, select the batch to delete and click Clear selected batch. The batch will be cleared when the staging process runs.
Step 4 (Optional): Invoke the Validation Process
In Master Data Manager, you can validate the version in the Version Management functional area by clicking Validate Version on the menu bar. Choose your model and version and click Validate version.
For an example of how to use SQL scripting to invoke the validation process, see my post: Staging Examples and Troubleshooting.
© 2010 Microsoft Corporation. All rights reserved. This information is provided “as-is”. Information and views expressed, including URL and other Internet Web site references, may change without notice. You bear the risk of using this information. Examples are provided for illustration only. Provision of this information does not provide you with any legal rights to any intellectual property in any Microsoft product. You may copy and use this information for your internal, reference purposes only.