THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Master Data Services Team

Blog for the Microsoft SQL Server Master Data Services (MDS) team. Blog posts are contributed by various team members.

Importing Data by Using the Staging Process

(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.


Staging Tables

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.

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.

Staging Process Workflow

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.

staging step 1

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.

staging step 2

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. 
staging step 3
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. 
staging 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.

Legal Notice 
© 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.

Published Wednesday, February 10, 2010 4:21 PM by mattande
Filed under: , ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

deva said:

Thanks for step by step process. However link to

Staging Examples and Troubleshooting is not working.

Best Regards

Deva

February 12, 2010 6:22 PM
 

Brian said:

The "Staging Examples and Troubleshooting" link is working now.

February 15, 2010 7:38 AM
 

Master Data Services Team : Staging Examples and Troubleshooting said:

February 25, 2010 5:11 PM
 

Lena said:

Hello,

You refered that "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."

Can you please tell me what setting specify the processing interval? I can't find that.

Thank you.

Lena

March 17, 2010 11:03 AM
 

Suzanne Selhorn said:

Hi Lena,

In CTP3 this setting isn't exposed in MDS Configuration Manager--that was my mistake. If you have access to your MDS database, in the mdm.tblSystemSetting table there is a setting called StagingBatchInterval that notes the frequency in seconds. The default is 60 seconds but you can update it if you'd like. Just note that 60 seconds must pass before the new frequency begins.

Thanks,

Suzanne

March 17, 2010 1:11 PM
 

Steve B said:

Can you use the staging process to "update" a previously loaded member?

I want to be able to update MDS values when external data sources change.

Thanks

August 11, 2011 7:51 AM
 

Steve B. said:

After experimenting with the staging process, I see that is handles updates as well as inserts.

August 11, 2011 8:34 AM
 

Łukasz said:

It's a quite old topic, but maybe someone will help me.

There is a possibility to insert and update members in Entity using ImportType field. Unfotunately I cannot find an option to ONLY update all specific attributes without adding new.

I have two datasources. First is required to insert new members (codes) or update existing attributes in this specific entity. Second should only update rest of attributes, it can't generate new codes.

How to do that?

Thanks,

Łukasz

July 12, 2012 4:43 AM
 

LuisDM said:

I have same question that Łukasz

July 31, 2012 12:23 PM
 

Arnas said:

It would be nice that you make a tutorial step by step for new users of MDS how to import data in 2012 SQL, because the vidoe is bad quality, and there is no example how to do that. The old process of staging is not working, have tried, and read somewhere. It would be very good to have raw example from scartch for SQL 2012 MDS data import. If of course anyone uses and reads this article.

January 11, 2013 2:42 AM
 

smurugappan said:

I like you enjoy reading their comments and sometimes get ideas from them.

<b><a href=http://www.smurugappan.com/>Income Tax Calculator India</a></b>

June 5, 2014 6:04 AM

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement