Skip to main content
Solved

Survivorship Logic in Ataccama

R
Data Voyager

I’m new to Ataccama ONE MDM and have recently completed the training. I’m currently doing a POC on it and am stuck at two places.

So here’s the situation:

First, I’m able to load the data but I’m not sure how to update it. Like, lets say I have a person record and after loading it generated Person_ID X23233X. Now, I made changes to some of the data in the same request. For eg, I’ve updated the contact number or the address of the record. Now, when I’ll be loading this same data again I want the ID to remain the same and just update the contact/address data along with Last_Updated _Timestamp.

 

Secondly, lets say I have a record with 2 different phone types, ‘Home Phone’ and ‘Work Phone’, and I’ve loaded it. So, the Person_ID  X12322X will have two contact details associated with it. Now, lets say I removed the ‘Home Phone’ details from the record and send it for update load. So, after updating the same request, ‘Home Phone’ details should be dissociated from the Person_ID X12322X (the contact details can still be present in the Contact table) and the Person_ID should only be associated with ‘Work Phone’ i.e only ‘Work Phone’ details should survive.

 

HOW TO DO THIS? Can help or suggestions will be highly appreciated.

Best answer by Samuel Muvdi

Hi @RandomStranger37,

When it comes to survivorship in MDM, it revolves around the Source_ID column. So, it's basically an UPSERT. Once MDM detects that the Source_ID does not exist in the table, it will create a record with that Source_ID, if it detects that the Source_ID is there, then it will do an update on that record.

So for example, lets say you have 2 entries coming into MDM

Source_id name phone_val
1 John 555
2 Steve 333

 

You can see that each person has a source_id, name, and phone value. Lets say now we send in an updated sheet

Source_id name phone_val
1 John  

 

Here now John with Source_id of 1 comes in without a phone number. This new entry upserts into the MDM table, and updates the record on the source_id of 1, removing the phone val. The MDM table looks like this now:

Source_id name phone_val
1 John  
2 Steve 333

 

Lets say now 2 more entries come in now:

Source_id name phone_val
1 John 954
1 John 754

 

The first entry will come in and update the table on the record with source_id of 1, adding the phone val to be 954. After that, the next entry will get processed, and now the final record for source_id 1, will have phone val of 754, with the final table looking like this:

Source_id name phone_val
1 John 754
2 Steve 333

 

In summary, we received these entries and processed them into our load plans:

Source_id name phone_val
1 John 555
2 Steve 333
1 John  
1 John 954
1 John 754

 

And our final MDM table after processing all these will look like this:

Source_id name phone_val
1 John 754
2 Steve 333

 

When it comes to deleting in MDM, you will handle this scenario in the delta load component, where it has the mapping columns step. You need to go into the step and change the change_type from ‘U’ to ‘D’. The issue with this, is that any source ID you run through there now will be deleted, so to solve this, you can attach an ‘active_flag’ value to each of your records, which then can be mapped in that change_type column expression. For example:

source_id name phone_val active_flag
1 John 754 Y
2 Steve 333 Y
1 John 754 N

 

Inside the change_type expression, you will need a condition that is something like this iif(active_flag is ‘Y’, ‘U’, ‘D’)

This will make sure that you are mapping your records in using that active flag, so that MDM can translate the active_flag from YES and NO to DELETE and UPDATE (UPDATE also inserts)

 

Hope this helps!

Best regards

 

 

View original
Did this topic help you find an answer to your question?

S
Ataccamer
Forum|alt.badge.img+2

Hi @RandomStranger37,

When it comes to survivorship in MDM, it revolves around the Source_ID column. So, it's basically an UPSERT. Once MDM detects that the Source_ID does not exist in the table, it will create a record with that Source_ID, if it detects that the Source_ID is there, then it will do an update on that record.

So for example, lets say you have 2 entries coming into MDM

Source_id name phone_val
1 John 555
2 Steve 333

 

You can see that each person has a source_id, name, and phone value. Lets say now we send in an updated sheet

Source_id name phone_val
1 John  

 

Here now John with Source_id of 1 comes in without a phone number. This new entry upserts into the MDM table, and updates the record on the source_id of 1, removing the phone val. The MDM table looks like this now:

Source_id name phone_val
1 John  
2 Steve 333

 

Lets say now 2 more entries come in now:

Source_id name phone_val
1 John 954
1 John 754

 

The first entry will come in and update the table on the record with source_id of 1, adding the phone val to be 954. After that, the next entry will get processed, and now the final record for source_id 1, will have phone val of 754, with the final table looking like this:

Source_id name phone_val
1 John 754
2 Steve 333

 

In summary, we received these entries and processed them into our load plans:

Source_id name phone_val
1 John 555
2 Steve 333
1 John  
1 John 954
1 John 754

 

And our final MDM table after processing all these will look like this:

Source_id name phone_val
1 John 754
2 Steve 333

 

When it comes to deleting in MDM, you will handle this scenario in the delta load component, where it has the mapping columns step. You need to go into the step and change the change_type from ‘U’ to ‘D’. The issue with this, is that any source ID you run through there now will be deleted, so to solve this, you can attach an ‘active_flag’ value to each of your records, which then can be mapped in that change_type column expression. For example:

source_id name phone_val active_flag
1 John 754 Y
2 Steve 333 Y
1 John 754 N

 

Inside the change_type expression, you will need a condition that is something like this iif(active_flag is ‘Y’, ‘U’, ‘D’)

This will make sure that you are mapping your records in using that active flag, so that MDM can translate the active_flag from YES and NO to DELETE and UPDATE (UPDATE also inserts)

 

Hope this helps!

Best regards

 

 

Samuel
Cansu
1 person likes this
  • Cansu
    Cansu
Cansu
Community Manager
Forum|alt.badge.img+3
  • CansuCommunity Manager
  • Community Manager
  • July 29, 2024

Hi @RandomStranger37, I’m closing this thread for now, if you have any follow-up questions please don’t hesitate to share them in the comments or create a new post 🙋‍♀️

Check out our Quick Start Guide to get started on the community 🙋‍♀️
R
Data Voyager

but how to implement this in ataccama? Thats my question.

Cansu
Community Manager
Forum|alt.badge.img+3
  • CansuCommunity Manager
  • Community Manager
  • August 19, 2024

Hi @RandomStranger37, the merge step is the last process we do in the plan. May I ask if you have already completed setting up connected systems and the instance layer? If you haven’t yet, you could create a CDI example project and go top down seeing how things were created there as an example flow, I’m sharing an article from our documentation that can be helpful  https://docs.ataccama.com/mdm/latest/logical-models/creating-a-logical-model.html

Check out our Quick Start Guide to get started on the community 🙋‍♀️

Reply


Community Terms of Use & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings