locked
Data Matching Reference Table RRS feed

  • Question

  • I have two different systems that both contain what is supposed to be matching data so in system A I have employee 'Fred' and in system B I have employee 'Fred' but both systems have given them different unique identifiers.

    Up until now I used an SSIS package to match the names and create a table of master data that has the identifiers from system A and from system B in and give 'Fred' a new identifier that is then used by other system such as Biztalk.

    I want to do the same using MDS and my question is how do I approach this?  Do I use a domain based attribute as a look up which contains the IDs from both/all systems and use the code field as the new ID?  If I use the domain based attribute how do reference this when populateing MDS from staging tables?

    Would anyone know the best way to approach this?

     

    Thank you

     

    Tuesday, November 2, 2010 8:15 AM

Answers

  • Buxton,

    I finally got around to posting a wiki article on mapping: http://social.technet.microsoft.com/wiki/contents/articles/mapping-multiple-systems-with-master-data-services.aspx

    I have a feeling this article will be updated over time. Also, because it's a wiki article, people can edit it, so feel free.

    Thanks.


    Suzanne Selhorn [MSFT]
    Tuesday, December 28, 2010 7:42 PM
  • Buxton,

    I saw this post that might help you: http://mattbi.spaces.live.com/blog/cns!9EE4FA43D304E745!276.entry

    You've also gotten me thinking, and I talked to one of the PMs about what to do about how we do 1-1, 1-many, and many-many relationships in MDS. At the moment it's on my whiteboard but I'll make a blog post out of it and let you know when it's out there.


    Suzanne Selhorn [MSFT]
    Wednesday, November 3, 2010 5:03 PM
  • Hello Buxton

    yes, you should use the Code Attribute as your ID for employee table

    to load data with Domain based attribute, please check this link

    http://msdn.microsoft.com/en-us/library/ee633772.aspx

     

    AttributeValue

    For free-form attributes, specify the new text or string value for the attribute. For domain-based attributes, specify the code for the member that will be the attribute.

    If you leave this field blank, when the staging process runs, the attribute value is updated to a blank value. The only exceptions are numeric values, date values, or the Name attribute. These cannot be assigned a blank value through staging.

    If you used MDMMemberStatus for AttributeName, use De-Activated or Active for AttributeValue.

    NoteNote
    You cannot stage file attributes.

     


    Note: Please vote/mark the post as answered if it answers your question/helps to solve your problem. _________________________________________________________ Regards, Xavier Averbouch, Consultant, Avanade France
    Tuesday, November 2, 2010 9:39 AM
  • Buxton,

     

    I'm not sure to understand your question.

     

    Assuming you have an entity 'Employee' with :

    [Attribute Name] [Example value]

     

    CODE 1

    NAME John Smith

    Sys-A_ID 1234

    Sys-B_ID 5678

     

    To obtain these values above, I will load staging tables like that:

     

    /* Add a member in Employee entity */
    
    INSERT INTO mdm.tblStgMember ([Batch_ID], [UserName], [ModelName], [HierarchyName], [EntityName], 
    
    	[MemberType_ID], [MemberName], [MemberCode], [Status_ID], [ErrorCode])
    
    VALUES (NULL,NULL,'YourModelName',NULL,'Employee', 1, 'John Smith', 1, 0, '')
    
    /* set attribute value for 'Sys-A_ID' in Employee entity */
    
    INSERT INTO mdm.tblStgMemberAttribute([Batch_ID], [UserName], [ModelName], [EntityName], 
    
    	[MemberType_ID], [MemberCode], [AttributeName], [AttributeValue], [Status_ID], [ErrorCode])
    
    VALUES (NULL, NULL, 'YourModelName', 'Employee', 1, 1, 'Sys-A_ID', 1234, 0, '')
    
    INSERT INTO mdm.tblStgMemberAttribute([Batch_ID], [UserName], [ModelName], [EntityName], 
    
    	[MemberType_ID], [MemberCode], [AttributeName], [AttributeValue], [Status_ID], [ErrorCode])
    
    VALUES (NULL, NULL, 'YourModelName', 'Employee', 1, 1, 'Sys-B_ID', 5678, 0, '')
    

     

    1) A domain based attribute allow you to have a relationship between 2 entities.

    Could you describe your MDS Model : do you need one or two entities for the example you gave ?

    If you need only one entity, you do not need a Domain based attribute.

    2) Here is an example of SSIS package I've made to update data via staging tables:

    http://social.msdn.microsoft.com/Forums/sr-Latn-CS/sqlmds/thread/3edcde59-374a-4c73-953c-779bc7481163

     

     


    Note: Please vote/mark the post as answered if it answers your question/helps to solve your problem. _________________________________________________________ Regards, Xavier Averbouch, Consultant, Avanade France
    Tuesday, November 2, 2010 12:47 PM
  • Okay

    do you know business rules (B.R.) in MDS ?

    If I understand your request, I think you can do some kind of lookup with B.R.

    http://sqlblog.com/blogs/mds_team/archive/2010/02/03/creating-a-simple-business-rule.aspx

     


    Note: Please vote/mark the post as answered if it answers your question/helps to solve your problem. _________________________________________________________ Regards, Xavier Averbouch, Consultant, Avanade France
    Tuesday, November 2, 2010 2:06 PM

All replies

  • Hello Buxton

    yes, you should use the Code Attribute as your ID for employee table

    to load data with Domain based attribute, please check this link

    http://msdn.microsoft.com/en-us/library/ee633772.aspx

     

    AttributeValue

    For free-form attributes, specify the new text or string value for the attribute. For domain-based attributes, specify the code for the member that will be the attribute.

    If you leave this field blank, when the staging process runs, the attribute value is updated to a blank value. The only exceptions are numeric values, date values, or the Name attribute. These cannot be assigned a blank value through staging.

    If you used MDMMemberStatus for AttributeName, use De-Activated or Active for AttributeValue.

    NoteNote
    You cannot stage file attributes.

     


    Note: Please vote/mark the post as answered if it answers your question/helps to solve your problem. _________________________________________________________ Regards, Xavier Averbouch, Consultant, Avanade France
    Tuesday, November 2, 2010 9:39 AM
  • But how would I use an entity as a lookup table so I can use the Code based in the lookup attribute to update the correct employee entity?

    e.g. I am importing data from system A and it has an ID of 1234 and the same employee is also imported from system B and in B they have an ID of 5678.  I have created a lookup entity which has:

    Name, Code, Sys-A_ID, Sys-B_ID
    Fred, 1, 1234, 5678

    How do I use the code in this entity to update the staging tables so I can update the correct entity, I would need some way to look up Sys-A_ID and then see it has a code of 1 and then create an update query to the staging tables that would update the Employee entity that has the Code of 1, is this possible?  Is there any recommended ways of doing this?

    Thanks

    Tuesday, November 2, 2010 9:54 AM
  • Buxton,

     

    I'm not sure to understand your question.

     

    Assuming you have an entity 'Employee' with :

    [Attribute Name] [Example value]

     

    CODE 1

    NAME John Smith

    Sys-A_ID 1234

    Sys-B_ID 5678

     

    To obtain these values above, I will load staging tables like that:

     

    /* Add a member in Employee entity */
    
    INSERT INTO mdm.tblStgMember ([Batch_ID], [UserName], [ModelName], [HierarchyName], [EntityName], 
    
    	[MemberType_ID], [MemberName], [MemberCode], [Status_ID], [ErrorCode])
    
    VALUES (NULL,NULL,'YourModelName',NULL,'Employee', 1, 'John Smith', 1, 0, '')
    
    /* set attribute value for 'Sys-A_ID' in Employee entity */
    
    INSERT INTO mdm.tblStgMemberAttribute([Batch_ID], [UserName], [ModelName], [EntityName], 
    
    	[MemberType_ID], [MemberCode], [AttributeName], [AttributeValue], [Status_ID], [ErrorCode])
    
    VALUES (NULL, NULL, 'YourModelName', 'Employee', 1, 1, 'Sys-A_ID', 1234, 0, '')
    
    INSERT INTO mdm.tblStgMemberAttribute([Batch_ID], [UserName], [ModelName], [EntityName], 
    
    	[MemberType_ID], [MemberCode], [AttributeName], [AttributeValue], [Status_ID], [ErrorCode])
    
    VALUES (NULL, NULL, 'YourModelName', 'Employee', 1, 1, 'Sys-B_ID', 5678, 0, '')
    

     

    1) A domain based attribute allow you to have a relationship between 2 entities.

    Could you describe your MDS Model : do you need one or two entities for the example you gave ?

    If you need only one entity, you do not need a Domain based attribute.

    2) Here is an example of SSIS package I've made to update data via staging tables:

    http://social.msdn.microsoft.com/Forums/sr-Latn-CS/sqlmds/thread/3edcde59-374a-4c73-953c-779bc7481163

     

     


    Note: Please vote/mark the post as answered if it answers your question/helps to solve your problem. _________________________________________________________ Regards, Xavier Averbouch, Consultant, Avanade France
    Tuesday, November 2, 2010 12:47 PM
  • Sorry, I think you've misunderstood what I have been trying to do, I know how to load the attributes and the concept behind it and the staging tables, what I'm trying to do is use a entity as a lookup table or try and find some way of doing this, I don't have a model yet as I need the information on how I will be approaching this before I can build a model.

    I need to use some sort of table that links the two or more systems together and refers to them with an ID, I then need to insert this ID into the 'Code' part of the Employee attribute, is there a way of doing this within MDS or do I need to do this externally before inserting it into MDS, can MDS be queried from SQL to return any part of an attribute?

    Could I approach this any other way eg would it best best to just have an entity that contains the lookup table and then use a view from MDS as part of the SSIS process, how would anyone else go about this?

     

    Thanks

    Tuesday, November 2, 2010 2:02 PM
  • Okay

    do you know business rules (B.R.) in MDS ?

    If I understand your request, I think you can do some kind of lookup with B.R.

    http://sqlblog.com/blogs/mds_team/archive/2010/02/03/creating-a-simple-business-rule.aspx

     


    Note: Please vote/mark the post as answered if it answers your question/helps to solve your problem. _________________________________________________________ Regards, Xavier Averbouch, Consultant, Avanade France
    Tuesday, November 2, 2010 2:06 PM
  • Buxton,

    I saw this post that might help you: http://mattbi.spaces.live.com/blog/cns!9EE4FA43D304E745!276.entry

    You've also gotten me thinking, and I talked to one of the PMs about what to do about how we do 1-1, 1-many, and many-many relationships in MDS. At the moment it's on my whiteboard but I'll make a blog post out of it and let you know when it's out there.


    Suzanne Selhorn [MSFT]
    Wednesday, November 3, 2010 5:03 PM
  • Thanks Suzanne
    Friday, November 5, 2010 11:05 AM
  • Buxton,

    I finally got around to posting a wiki article on mapping: http://social.technet.microsoft.com/wiki/contents/articles/mapping-multiple-systems-with-master-data-services.aspx

    I have a feeling this article will be updated over time. Also, because it's a wiki article, people can edit it, so feel free.

    Thanks.


    Suzanne Selhorn [MSFT]
    Tuesday, December 28, 2010 7:42 PM