Modeling M:N and #MDS #MASTERDATESERVICES

Modeling many to many often poses a challenge. This blog intent is to deal with this and at the same time demonstrate how the practical implementation will be in Master Data Services.

It is widespread mistake to look at Employee as a sub-type of Person. After all, a large number of the people you want to keep track of are in fact employees. A person is not inherently an employee. He or she only becomes an employee by entering into a relationship with a company. The quality of being an employee, then, is an aspect of a relationship, not of an entity.

The reason for this misperception of modeling is to be found in converting verbal expression into a data model: while many of the nouns sets the context of an object with by its relationships to other objects.

In the example below is a Person employed by one or more Organizations, each Organization may be the employer of one or more people. This implies Employment as an intersection between organization and person. The fact that an employment is the basis of one or more position assignments throws us into an intersection where a position assignment intersects employment and position.

clip_image002[8]

Looking on the payload of the entity Employment it will it include data such as "Employment date", "Termination date" and so forth. Where the entity Person carries "Social security number" eg.

 

The Example in MDS is to follow

Modeling Maps with #MDS #MASTERDATESERVICES

Entities of data is mostly a shared resource, and utilized in or contributed to by multiple systems. For example, a company may have separate systems for managing customer interactions. A part of the data will properly be an intersection, but each of the systems is also likely to have a subset of the customer information. Establishing a common definition and mapping to the natural keys of source systems is key, Master Data Services can be an effective place to store and maintain these mappings.
The following examples aim to demonstrate how to use MDS for mapping. It is also worth noticing that the examples are generic and simplistic, and assuming the members to be on the same granularity. Furthermore is it a prerequisite that the reader is familiar with MDS and its terms known ground.

 

One to One

In MDS, create one member to represent the country. There is two attributes to indicate how the country is referred to the two other systems.

The benefits of this approach are that it is easy to map source systems to one member in MDS with no impact on usability. But the downside is that it is not possible to map multiple entries to one MDS entry.

image

 

One to Many

The 1:N scenario is created by creating “mapping” entities. One for each source system is needed. The reason for separating out in more than one mapping table is that the natural keys from a source system often is built as a composite key, with varying number of attributes.

This modeling technique features more than one Natural key in the source system for the same entity, but cannot handle the Zombie Keys scenario as well described by Thomas Kejser.

image

 

image

 

Modeling this style was in earlier releases on the expense of the user experience, while navigating between members in the entities was slow and difficult. In this version has the ease of navigating to referred entities been improved. By clicking on the icon a new is a new window opened with the referring entity, and it’s easy to maintain its values without destroying the context which is currently edited.

image

Eventough its possible to manually maintain the mapping tables, is the ideal method to maintain these though ETL processes. The reason for this is that these tables quickly becomes complex, and human errors will most likely occour.

Many to Many

The N:M scenario aims to solve relations between Source Systems and an entities in a given time range. Where relations can cease to exist and reemerge later on.

This solution features a number of special purpose entities:

image

1) An entity holding “truth” as we would like to see it.

2) An entity holding the natural key from the Source System.

3) An entity holding the mapping in time between the entity containing the “truth” as we would like to see it and the entity holding the natural key for a given source system. This Entity also holds special payload. It must tell us in which timespan the relation is valid (ValidFrom, ValidTo). It is also hand with other flags indicating what state the map is in (IsCurrent , IsDeleted) all this is explained in more detail in Thomas kejsers blog post Fixing Zombie Keys

 

This illustration shows how the mapping table explicit tells the life span of the individual keys.

image

 

This construct is repeated pr source system pr unique natual key combination. Thomas kejsers blog post An Overview of Source Key Pathologies aims to explain the nature of each key type along with Transforming Source Keys to Real Keys – Part 1: Introducing Map tables and Transforming Source Keys to Real Keys – Part 2: Using Maps To Fix Key Problems is a most interesting read on this topic.

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

This blog mainly focus on my daily life, my passion for Information Management, Photography and Food.