Importing Data Into Master Data Services 2012 – Part 1

Welcome to Part 1 of a 3-part series on using the new Entity-Based Staging Process in Master Data Services (MDS) 2012.

Part 1:  Overview of the Staging Process

Part 2:  Loading a Model Where All Attributes Come From a Source System

Part 3:  Loading a Model Where Some Attributes Are Maintained Directly in MDS

Introduction to the MDS Entity-Based Staging Structure

One of the new features of MDS in SQL Server 2012 is a new process for staging data to be imported into MDS.  Key things to be aware of:

  • Each entity has its own staging table in the stg schema.
  • Each entity has its own stored procedure (udp) to load data from staging into the model.
  • Within the stored procedure, you have an ImportType parameter which specifies how new and updated members are treated.  New in 2012 is the ability to update existing values, if you so choose. 
  • If more than one batch will be running at the same time, each batch needs a unique BatchTag. 
  • The model needs to be validated after the stored procedure to load the model is executed.
  • Each entity has its own view to display errors which occurred while loading the model.

The objective of this process is for us to interact with the staging table (in the stg schema), then allow the MDS-generated stored procedure interact directly with the model (in the mdm schema).

An overview of the process is as follows:

       image

Advantages of this new structure include:

  • Ability to handle updates (as well as inserts and deletes), if you choose the Import Type which permits updates.
  • Easier to understand ETL processing.
  • Much faster and efficient ETL processing.
  • Security may be set up per individual staging table, if necessary.  Permission to insert data to the staging table(s) is required.
  • Security may be set up per stored procedure, if necessary.  Permission to execute the stored procedure(s) is required.
  • Members and attributes may be loaded in single batches related to one specific entity.

Tables, Stored Procedures, and Views in the Staging Schema

For each entity, up to 3 staging tables may exist depending if consolidated members exist, and if explicit hierarchies exist.  The leaf member table will always exist once the entity has been created.  It would be very common to not use all 3 possibilities.

Note:  All names are based on the Entity name unless a different name was chosen when the entity was created.

Leaf Members

     Staging table:  stg.EntityName_Leaf

     Stored procedure to load model:  stg.udp_EntityName_Leaf

     View which displays errors: stg.viw_EntityName_MemberErrorDetails

     Details re: fields loaded to the staging table:  http://msdn.microsoft.com/en-us/library/ee633854.aspx

Consolidated Members

     Staging table:  stg.EntityName_Consolidated

      Stored procedure to load model: stg.udp_EntityName_Consolidated

     View which displays errors: stg.viw_EntityName_MemberErrorDetails

     Details re: fields loaded to the staging table:  http://msdn.microsoft.com/en-us/library/ee633772.aspx

Explicit Hierarchies

     Staging table name: stg.HierarchyName_Relationship

     Stored procedure to modify hierarchy:  stg.udp_HierarchyName_Relationship

     View which displays errors: stg.viw_HierarchyName_RelationshipErrorDetails

     Details re: fields loaded to the staging table:  http://msdn.microsoft.com/en-us/library/ee633902.aspx

Managing Import Processes

In the Integration Management section of the MDS web interface, staging batches which have completed, or are queued to run are displayed:

     image

Finding More Information

Part 2 of this series (discusses Loading a Model Where All Attributes Come From a Source System)

Part 3 of this series (discusses Loading a Model Where Some Attributes Are Maintained Directly in MDS)

MSDN – Load or Update Members in Master Data Services by Using the Staging Process