Modelling Question on Business Key Updating in source system

Data Vault 2.0
1

Hi, we are beginning to implementing DV2. Need advice on how to deal with below scenario
In source system we have surrogate key and Business key, There are some situations where Business key is updated in source system and the same Business key will get created with new Surrogate key

image

Ideally we thought of creating a HUB on Policy Number and now with above situation we end up #P1 a new entry in HUB and create a new satellite record.

Please suggest how to handle this scenarios when we donā€™t have a strong natural key. The only option I was able to think is Peg legged link where I will have HUB POLICY plus Dependent child(PK from Source).

2

Identify these anomalies and request the source to fix them; the data vault is not the repository of technical debt

3

Can you describe your case a little more.

  1. Do you get all id:s and policynumbers for each batch?
  2. Is the descriptive data based on the surrogat key in the source system?
  3. Can you extend your example a little, can there be different policynumbers for different surrogat keys?
4

The way Application system works when user want to re -issue a policy, then On Backend DB ,P1 will get recreated with new Surrogate key and Old record will be prefixed with ā€œ#ā€
Example

Day2 P1 is reissued
Pk in source PolicyNumber
1 #P1
5 P1
Day3 P1 is Reissued Again
Pk in source PolicyNumber
5 #P1
6 P1

  1. I dont get all the IDā€™s and Policy Number on each Batch, But when there is a update on Business Key, then I will definitely will see another entry of old Business Key with new Surrogate key in that batch(like the example that I showed in screenshot on Day2)

  2. All the descriptive Data and child Table in source system based on surrogate key

  3. Generally we will have one Policy Number and One Surrogate key, Same Policy Number cannot have different surrogate keys( except when prefix is ā€œ#ā€, that you see in example Day2 and Day3 records above)

Thanks

5

Hi preddy!
I would ask for a full dump every day so it would be easier/safer to handle the changing relations over time.
If that is not possible then i would suggest you model a NHL and capture all changed relations over time.

Hang the satellites on the surrogat keys in this case.

Good luck!

6

Preddy, there is no such thing as a peg-legged link in DV2 standards. DV2 recommended best practice is to get the old key to new key mapping from the source system for any key that is changing. If itā€™s a true business key, then the frequency of encountering a change should be extremely low. DV2 recognizes that business keys change - even though they shouldnā€™t - it does happen, but it should be a rare situation. If you are finding that the business key you chose is changing frequently, then the team may have chosen an incorrect key. You may also chose to resolve the changes to the BK downstream in a business vault object in the form of a Master Policy link (recommended) using a single tiered hierarchical link to resolve the discrepancies.
The additional suggestion Iā€™d make is that you develop a feedback report to the business to show them how frequently the selected business key is changing so that they understand the impacts to the business when this happens.
Respectfully,
Cindi Meyersohn

1 Like
7

wouldnā€™t a same-as-link (SAL) work in these scenarios? We have cases where business keys can change/mutate over time (Counterparty codes changes when legal entities change domicile), and we track this using a SAL (we are fortunate that we receive enough secondary/candidate identifiers/keys from the source that we are able to detect this and load the SAL accordingly).

8

Exactly,

No peg-leg links in DV2.0, itā€™s Ensemble DV that allows for this!
For tracking SAL bk to sk changes consider:

  • will you need RTS/STS/EFS to track movement?
  • for EFS nominate BK as the driver key, track movement
9

This issue has just popped up for me. D365 source system - business without IM controls - allows (occasional) changes in BK. The only immutable thing in the source record is the source GUID. Business refuses to change the system and I refuse to use a GUID as BK.

My response to this is to get the business to agree that the same record with a changed BK will be treated as a new object, but for that source, I can detect itā€™s a change by common GUI and link them together with the SAL pattern. i.e detecting that the new BK (B) shares the GUID with an existing BK (A) from the same source system, I will create a LINK that says ā€˜A has been renamed to Bā€™. and all future updates to the HUB will be linked to ā€˜Bā€™

Iā€™m intrigued @patrickcuba, it looks like you are suggesting using the HUB as a repository of all keys, HK and SK and linking them together? Some questions on this pattern:

  • Do you use a different HUB for the SK? If not, then how are you counting/distinguishing the various keys?

  • Do you SAL each HK to the SK

10

Not at all

I only suggest that you donā€™t build a fake vault: Seven Deadly Sins of Fake Vault. The term ā€œFake Vaultā€ was coined atā€¦ | by Patrick Cuba | Snowflake | Medium

And You might be doing #datavault Wrong! | by Patrick Cuba | Snowflake | Medium

1 Like