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.

Staging Examples and Troubleshooting

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

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.

In this post I will provide some staging examples and give you some tips for troubleshooting. First I'll show examples of the data format required by the staging tables. Then I'll show how to use SQL scripts to import data, invoke the staging process, and then invoke the validation process. Finally, I'll give you some tips that can help with issues you might run into while staging data.

For an introduction to the staging process, see my post: Importing Data by Using the Staging Process.

Data Format Examples

Creating members or collections
You can use the Members staging table (tblStgMember) to create leaf members, consolidated members, or collections. For more information about the fields in this table, see Members Staging Table.

The following example shows how you could create a leaf member, a consolidated member, and a collection, respectively.

INSERT INTO mdm.tblStgMember (ModelName, HierarchyName, EntityName, MemberType_ID, MemberName, MemberCode) VALUES

(N'Product', NULL, N'Product', 1, N'Mountain-100', N'BK-M101'),  
(N'Product', N'Product Management', N'Product', 2, N'Men''s and Women''s Products', N'MW'),
(N'Product', NULL, N'Product', 3, N'John''s Responsibility', N'JR')

Updating attribute values
You can use the Attributes staging table (tblStgMemberAttribute) to update attribute values. For more information about the fields in this table, see Attributes Staging Table.

The following example shows how you could update an attribute for a leaf member, a consolidated member, and a collection.

INSERT INTO mdm.tblStgMemberAttribute (ModelName, EntityName, MemberType_ID, MemberCode, AttributeName, AttributeValue) VALUES

(N'Product', N'Product', 1, N'BK-M101', N'DaysToManufacture',N'4'),
(N'Product', N'Product', 2, N'MW', N'Owner', N'Gisli Olafsson'),
(N'Product', N'Product', 3, N'JR', N'Description', N'John Yokim''s Products')

You can also use this table to deactivate a member. Deactivating a member changes the MemberCode to a GUID and changes the AttributeValue to De-Activated so the member is no longer displayed in the UI.

INSERT INTO mdm.tblStgMemberAttribute (ModelName, EntityName, MemberType_ID, MemberCode, AttributeName, AttributeValue) VALUES

(N'Product', N'Product', 1, N'BK-M101', N'MDMMemberStatus', N'De-Activated')

To reactivate a member, use the following example.

INSERT INTO mdm.tblStgMemberAttribute (ModelName, EntityName, MemberType_ID, MemberCode, AttributeName, AttributeValue) VALUES

(N'Product', N'Product', 1, N'B12DDC7B-DD25-4623-AAC6-BBE51A017D2F', N'MDMMemberStatus', N'Active')

To determine the GUID that is the MemberCode, open the view: mds.viw_SYSTEM_SCHEMA_ENTITY. Find the name of the entity that contains the deleted member or collection. Note the value in the EntityTable column. Then open the table that was listed in the EntityTable field. Find the name of the member or collection and note the value in the Code column. That value is the GUID.

Moving members in explicit hierarchies or adding members to collections
You can use the parent child relationship staging table (tblStgRelationship) to move members in explicit hierarchies and to add members to collections. For more information about the fields in this table, see Parent Child Relationships Staging Table.

The following example shows how you could stage these relationships.
• The first line in this example contains the column names.
• The second line designates the MW consolidated member as a parent of the BK-M101 leaf member in the Product Management explicit hierarchy.
• The third line designates the BK-M101 leaf member as a sibling of (at the same level as) the BK-M202 leaf member in the Product Management explicit hierarchy.
• The fourth line adds the JR collection to the C2 collection.
• The fifth line adds the BK-M101 leaf member to the C2 collection.
• The sixth line adds the MW consolidated member to the C2 collection.

INSERT INTO mdm.tblStgRelationship (ModelName, EntityName, HierarchyName, MemberType_ID, MemberCode, TargetCode, TargetType_ID) VALUES

(N'Product', N'Product', N'Product Management', 4, N'BK-M101', N'MW', 1),
(N'Product', N'Product', N'Product Management', 4, N'BK-M202', N'BK-M101', 2),
(N'Product', N'Product', NULL, 5, N'JR', N'C2', 1),
(N'Product', N'Product', NULL, 5, N'BK-M101', N'C2', 1),
(N'Product', N'Product', NULL, 5, N'MW', N'C2' , 1)

SQL Scripting Examples 

Invoking the Staging Process
After the tables are populated, you can execute a stored procedure that batches your records and sets them as queued to run. The staging table records that will be batched have:

  • The model in the ModelName field.
  • The user’s name or no user name in the UserName field.

The user must also be a model administrator.

DECLARE @ModelName nVarchar(50) = 'Customer'
DECLARE @UserName nvarchar(50)= 'DOMAIN\user_name'
DECLARE @User_ID int
DECLARE @Version_ID int

SET @User_ID =    (SELECT ID 
                   FROM  mdm.tblUser u
                   WHERE u.UserName = @UserName )

SET @Version_ID = (SELECT MAX(ID) 
                   FROM mdm.viw_SYSTEM_SCHEMA_VERSION 
                   WHERE Model_Name = @ModelName)

EXECUTE mdm.udpStagingSweep @User_ID, @Version_ID, 1

The 0 in this procedure indicates that the batch will be processed at the interval specified in Master Data Services Configuration Manager. If you specify 1, the process runs immediately.

Note: When the process runs, all batches that are queued to run are imported. This can include your batch and other user’s batches.

Invoking the Validation Process
If you want to invoke validation, you can do so after the staging process is complete. You will know the process is complete when all batches for the version have a Status_ID of 2 in mdm.tblStgBatch.

If you invoke validation before the staging process is complete, members that have not finished staging will not be validated.

You can then invoke business rules to validating data by executing mdm.udpValidateModel:
 
DECLARE @ModelName nVarchar(50) = 'Customer'
DECLARE @Model_id int
DECLARE @UserName nvarchar(50)= 'DOMAIN\user_name'
DECLARE @User_ID int
DECLARE @Version_ID int

SET @User_ID =  (SELECT ID 
                 FROM  mdm.tblUser u
                 WHERE u.UserName = @UserName)

SET @Model_ID = (SELECT Model_ID
                 FROM mdm.viw_SYSTEM_SCHEMA_VERSION
                 WHERE Model_Name = @ModelName)

SET @Version_ID = (SELECT MAX(ID)
                   FROM mdm.viw_SYSTEM_SCHEMA_VERSION
                   WHERE Model_ID = @Model_ID)

EXECUTE mdm.udpValidateModel @User_ID, @Model_ID, @Version_ID, 1

Troubleshooting
When you import data by using the staging process, you might encounter any of the following issues.

The model is not available in the Model list
In Master Data Manager, in the Integration Management functional area, on the Import page, in the Unbatched Staging Records pane, the model may not be available in the Model list.

You must be a model administrator to import data for a model. A model administrator is a user with Update permission assigned to the top object in the model, and no other permissions assigned.

The version is not available in the Version list
On the Import page, in the Unbatched Staging Records pane, the version may not be available in the Version list.

Model versions with a status of Committed are not available. Ensure that you are importing into an Open or Locked version.

The number of records is not correct
On the Import page, in the Unbatched Staging Records pane, the number of records available for import may not match the number of records you expected to retrieve from the staging tables in the Master Data Services database.

  • In the staging tables, ensure that the UserName field for each record contains your user name in the format Domain\user_name, computer\user_name, or that the field is NULL.
  • In the staging tables, ensure that the value in the Batch_ID field for each record is NULL and that the Status_ID field is 0.
  • In the staging tables, ensure that the value in the ModelName field matches the name of the model exactly. The name should be the same case as the model name in Master Data Manager.

The batch doesn't process
The batch may be queued to run but it does not process. In Master Data Manager, on the Import page, in the Unbatched Staging Records pane, the status remains Queued to Run.

Ensure that Service Broker is enabled. For more information, see How to: Activate Service Broker Message Delivery in Databases (Transact-SQL).

The batch is taking a long time to finish

If the batch takes longer than usual to complete, the statistics on the Master Data Services database may need to be updated. For more information, see:

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 5:13 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

 

Master Data Services Team : Importing Data by Using the Staging Process said:

July 16, 2012 5:32 PM
 

manishkumar1980 said:

I am new to MDS, executed mdm.udpValidateModel SP with @username etc. parameters twice.

will there be any side effect . please guide me urgently.

October 28, 2012 12:59 PM

Leave a Comment

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