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
The Golden Hour 
In a full production system i cannot recommend truncating the stating tables as in this example
Guldmann
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