Recommendation on SQL 2012 MDS or alternative

• david.jones 48836

  Grasshopper

  Points: 10

  More actions

  May 29, 2014 at 10:28 pm

  #287064

  Hi everyone,

  I've got a problem where I need to merge a Staff table and a Payroll table (both contain list of people with different keys), and get a unified list of unique people, but with a link back to the original tables. ie. Once complete, I need to link from the Staff table into the {Person} table, and then find the FK for the the Payroll table.

  I'm trying to do this with SQL 2012 MDS - and in particular the Excel Plug-in for MDS. The Plug-in works really well and allows me to combine and match the 2 tables, and groups records into "clusters" with a confidence score of how closely they are matched.

  According to what I've read, the next step (using the MDS Plug-in) is to delete the duplicate records and then publish to MDS. However by deleting the duplicate records, I will lose either the Staff Key or the Payroll key. I also don't want to have to manually update the Staff and Payroll keys for each record in the new {Person} table before publishing.

  Can anyone give me some guidance on how I can use MDS in this scenario, if not in MDS, a strategy for merging 2 lists of similar records, whilst maintaining a link back to the source tables.

  thanks in advance,

  David Jones