Batching data into Master Data Services

In this post I am going to demonstrate how easy it to use batching and explicit hierarchies in MDS. We are going to source data from the old Northwind database and stage them in MDS. We are going to use the contact title as an explicit hierarchy to our companies.

Doing such a series of action create all sorts of preconditions. We cannot place a member in a hierarchy before both hierarchy and member is created.  To do this we need to control the batching sequence.

1)  First lets create a model “DemoBatching”. This model is going to hold our entity “company”.

2) Then adding the entity “company” to the model, we choose to go with explicit hierarchies while we want to utilize this structure to give us a logical grouping on contact title.

Now if you created everything correctly, you will be able to pull down menues like this under “explore”

 

Now to the data handling, this example shows how to populate the data structure through TSQL but you can just as easily use SSIS


SET NOCOUNT ON

/* Set the model, user name and entity name*/
DECLARE @ModelName NVARCHAR(50) = N'DemoBatching'
DECLARE @UserName NVARCHAR(50) = N'PLATON\jgu'
DECLARE @EntityName NVARCHAR(50) = N'Company'

DECLARE @BatchID INT = NULL
DECLARE @UserId INT
DECLARE @VersionId INT
DECLARE @BatchName NVARCHAR= @ModelName + N'_Ver_'+CONVERT(NVARCHAR,@VersionId)+N'_User_'+@UserName;

/* Get the UserID from the username */
SET @UserId = (SELECT ID FROM mdm.tblUser u WHERE u.UserName = @UserName)

/* Get the newest version number (assuming this is the version we are populating */
SET @VersionId = (SELECT MAX(ID) FROM mdm.viw_SYSTEM_SCHEMA_VERSION WHERE Model_Name = @ModelName)

/* Truncate staging tables */
TRUNCATE TABLE mdm.tblStgBatch
TRUNCATE TABLE mdm.tblStgMember
TRUNCATE TABLE mdm.tblStgMemberAttribute
TRUNCATE TABLE mdm.tblStgRelationship

/* Create a new batch, we are going to sequence the following action with this batchid */
EXECUTE [mdm].[udpStagingBatchSave]
@UserID=@UserID
,@VersionID=@VersionID
,@Name=@BatchName
,@StatusID=1
,@ReturnID=@BatchID OUTPUT

/* Create members, deliberately not setting the name */
INSERT INTO [mdm].[tblStgMember] ([UserName],[Batch_ID],[ModelName],[EntityName],[MemberType_ID],[MemberName],[MemberCode])
SELECT @UserName ,@BatchID, @ModelName, @EntityName ,1, 'To Be Created',data.CustomerID
FROM Northwind.dbo.Customers data

/* for pure cosmetic reasons, lets tell MDS how many members the batch holds */
UPDATE mdm.tblStgBatch
SET TotalMemberCount=@@ROWCOUNT
WHERE ID=@BatchID

/* Create a new batch, we are going to sequence the following action with this batchid */
EXECUTE [mdm].[udpStagingBatchSave]
@UserID=@UserID
,@VersionID=@VersionID
,@Name=@BatchName
,@StatusID=1
,@ReturnID=@BatchID OUTPUT

/* Add the name again to the member */
INSERT INTO mdm.tblStgMemberAttribute
([UserName],[Batch_ID],[ModelName],[EntityName],[MemberType_ID],[MemberCode],[AttributeName],[AttributeValue])
SELECT @UserName,@BatchID,@ModelName,@EntityName ,1,data.CustomerID,'Name',data.CompanyName
FROM Northwind.dbo.Customers data

/* for pure cosmetic reasons, lets tell MDS how many attributes the batch holds */
UPDATE mdm.tblStgBatch
SET ErrorMemberAttributeCount=@@ROWCOUNT
WHERE ID=@BatchID

/* Create a new batch, we are going to sequence the following action with this batchid */
EXECUTE [mdm].[udpStagingBatchSave]
@UserID=@UserID
,@VersionID=@VersionID
,@Name=@BatchName
,@StatusID=1
,@ReturnID=@BatchID OUTPUT

INSERT INTO [mdm].[tblStgMember] ([UserName],[Batch_ID],[HierarchyName] ,[ModelName] ,[EntityName]
,[MemberType_ID],[MemberName],[MemberCode])
SELECT @UserName ,@BatchID, 'ContactTitle',@ModelName, @EntityName
,2, data.ContactTitle,data.ContactTitle
FROM Northwind.dbo.Customers data
GROUP BY data.ContactTitle

/* for pure cosmetic reasons, lets tell MDS how many members the batch holds */
UPDATE mdm.tblStgBatch
SET TotalMemberCount=@@ROWCOUNT
WHERE ID=@BatchID

/* Create a new batch, we are going to sequence the following action with this batchid */
EXECUTE [mdm].[udpStagingBatchSave]
@UserID=@UserID
,@VersionID=@VersionID
,@Name=@BatchName
,@StatusID=1
,@ReturnID=@BatchID OUTPUT

/* Place the company within the explicit hierarchy */
INSERT INTO mdm.tblStgRelationship ([username],[Batch_ID], [ModelName], [EntityName], [HierarchyName]
, [MemberType_ID], [MemberCode], [TargetCode], [TargetType_ID])
Select @UserName,@BatchID,@ModelName, @EntityName, N'ContactTitle', 4, data.CustomerID , data.ContactTitle, 1
FROM Northwind.dbo.Customers data

/* for pure cosmetic reasons, lets tell MDS how many relations the batch holds */
UPDATE mdm.tblStgBatch
SET TotalMemberRelationshipCount=@@ROWCOUNT
WHERE ID=@BatchID

/* Tell MDS to start processing data batches */
EXEC mdm.udpStagingSweep @UserId=@UserId, @VersionId=@VersionId, @Process=1

Lets examine what MDS has done under “integration management”

And lets see the final result by browsing the ContactTitle under explore->company

2 thoughts on “Batching data into Master Data Services”

  1. To get validation going after importing data use this procedure


    CREATE PROCEDURE [utl].[ValidateModel]
    ( @ModelName NVARCHAR(50)
    ,@UserName nvarchar(50))
    WITH EXECUTE AS CALLER
    AS
    BEGIN
    SET NOCOUNT ON;

    DECLARE @Model_id int
    DECLARE @User_ID int
    DECLARE @Version_ID int

    SET @User_ID = (SELECT usr.ID
    FROM mdm.tblUser usr
    WHERE usr.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

    SET NOCOUNT OFF;
    END

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s