how to maintain foreign keys in MDS& DQS? RRS feed

  • Question

  • Hi,

    i have two entities - Address with 5 attributes (Code,Address_key, county,state,zip) and Member with 7 attributes  (Code,first_name,last_name,middle_name,gender,dob,address_key). the Code in Address entity is same as Address_key attribute in that table. the code in Member entity is seq. number. Both entities are related with address_key domain-based attribute.

    i also have match&merge rule sets on Address entity in DQS.

    now, i have two records in my database, Address table with address_key 101,102 but with same county,state,and zip. And two corresponding records one with address_key 101 and another record with 102 in Member table. after applying match&merge logic on Address table, only one record with address_key=101 is selected as survivorship  record by DQS because  their county,state,zip are same. 

    when i try to load these records into MDS entity, the member record with address_key=102 validation is failed because its domain-based key is missing in Address entity. 

    So, how to handle this situation in MDS? is there any mechanism in MDS? (or) is there anything wrong in my design? Please advise. 

    Saturday, August 6, 2016 5:48 PM


  • From a modeling point-of-view, I would worry about what happens when one member has an address change.  If you have de-duped the addresses does that change apply to all the other members who are now linked to that address?

    So I think you can avoid this issue by simply _not deduping_ Address.

    If you _did_ need to do this, you would need to maintain a mapping between the source system keys and the de-duped "golden record" members in MDS.  In this case multiple source system keys could point to the same member, and you would need a separate entity to store this. 

    Another modeling option is to load all the Address rows and simply mark one as being the "master" or "golden record".  Then the Members would load and you could treat the Members-linking-to-non-master-Addresses in a downstream process with Business Rules.


    David http://blogs.msdn.com/b/dbrowne/

    • Marked as answer by Charlie Liao Monday, August 22, 2016 1:59 AM
    Saturday, August 6, 2016 11:07 PM