Changing A Table's Source Key Once It Has Been Established? : Portal

B

Brent Van Allen

started a topic over 2 years ago

Hello,

So we have a source-key for a given table. The source key is made of of three segments like AAA.BBB.CCC. We want to change the CCC part of the key, but have already loaded about 300K records with the original source-key value.

If we do change this key composition, I can calculate the new third keypart outside or inside the Semarchy database, but applying it is a challenge. I do not know every place I would have to change it at once to not break the system and also to ensure the newly applied keys in the DB line up with the Publisher side change to the algorithm to use the new key pattern so records will stilll match up on load into the Source tables.

Has anyone changed their source-key pattern "on the fly" like this once it is already established? If so how did you do it?

Anyone at Semarchy have a list of every single place I would have ot update the key to guarantee continuity after the change?

Thanks Much,

Brent Van Allen

Best Answer

J

Julien Peltier said about 2 years ago

Hello Brent,

To move forward with your use case, you will probably enfacing some DB issues when you want to update directly the key at DB level. In this situation, we advise you to:

- Recreate the existing 300K by copying your MI table to SD table and calculating the new sourceId

- After the certification process, these 300K records will have merged normally with the older300K records

- Then you can publish deletions for the older 300K records (https://www.semarchy.com/doc/semarchy-xdm/xdm/5.3/Integrate/publish-deletions-with-sql.html) to recover Golden record with only 1 master record for this source

Best Regards

Julien

S

Stéphanie FOURRIER

said over 2 years ago

Hello Brent,

thanks for the details provided for this interesting use case! I have to loop internally at Semarchy to check if someone faced this question earlier and to make sure of the right tables and columns to update within your data location. Let us get back to you as soon as possible.

Best regards,

Stéphanie.

J

Julien Peltier

said about 2 years ago

Answer

Hello Brent,

To move forward with your use case, you will probably enfacing some DB issues when you want to update directly the key at DB level. In this situation, we advise you to:

- Recreate the existing 300K by copying your MI table to SD table and calculating the new sourceId

- After the certification process, these 300K records will have merged normally with the older300K records

- Then you can publish deletions for the older 300K records (https://www.semarchy.com/doc/semarchy-xdm/xdm/5.3/Integrate/publish-deletions-with-sql.html) to recover Golden record with only 1 master record for this source

Best Regards

Julien

B

Brent Van Allen

said about 2 years ago

That is a great idea Julien. We do have a number of matching schemes for these records (11 total with various scoring). This might work well for the solution. What do you think of guaranteeing a perfect row-to-row match by generating a UUID for every row (in place in the GD table) such that this UUID is only set up, generated, copied to the SD table, the dropped after ... not sure if I can even drop columns once created.

Brent VA

J

Julien Peltier

said about 2 years ago

Hi Brent,

You can add a specific attribute to force matching on the Golden record ID by adding a dedicated matching rule. Still, it usually would not be necessary if you keep exactly the same record and change only the source id. Your proposal seems more secure but requires a lot of technical tasks after (update model and matching rule to remove the specific column + removing the column manually from SD/SA/MI/MD/GD/GA/GF/GH/GI/GP/GX/MX/SF tables. xDM cannot automatically drop the column because we have to support the deployment of a previous model version and keep associated data.

I will advise here to test the procedure without a dedicated column to avoid too many manual operations on DB.

Regards

Julien