How to recognize deleted records in ODX Server Storage
Hi team,
In the business units and data warehouses, deletions in an incremental table are identified by system field IsTombstone = 1. You can use this field in a data selection rule to filter out deletions between the business unit and DSA. This field is not present in ODX Server tables with incremental and delete handling enabled.
Is there a way to identify records that have been deleted from incremental tables in ODX Server? And if not, what is the purpose of enabling deletion handling in ODX Server?
Kind regards,
Andrew - E-mergo
Page 1 / 1
Note: Andrew was asking about 20.10.39 but this also does not work in 6221.1. The documentation shows that selecting deletion handling should add an Is Tombstone field in the new release when mapping from ODX to DSA, but it does not:
Note the lack of a system field in the screenshot. This applies to both soft- and hard deletes. Synching ODX to DSA, remapping, refreshing the desktop app does not help
Hi Andrew
It is true you can’t see what fields have been deleted in the ODX store.
If you set up your table to handle deletes, specifically sets it to soft deletes, you will be able to see what rows have been deleted from that table until you run a full load. So it will work the same as when you have a business unit.
I get the same issue though. I found that it does not create the IsTombstone field unless you have this setup.
I guess it will automatically change it from automatic if you turn on Incremental load on a table in a MDW not pointing at a ODX.
Hi Thomas,
I assume it is a bug that ‘IsTombstone’ does not show up on tables in an MDW unless the above settings are used (only difference seems to be ‘Keep field values up-to-date’).
You mention ‘you will be able to see what rows have been deleted from that table until you run a full load. So it will work the same as when you have a business unit.’. How will I be able to see this? Should there be an IsTombstone field in ODX? Or something with the primary key table?
Thanks,
Andrew
Hi Andrew
Yeah, it may be so. It should either not allow you to set the table to run with deletes and be kept in automatic or give an error message if you turned on deletes, like it would for history settings.
I have made an internal note regarding this.
So here is why this happens and it works the same in the new and the old version.
I did get a bit stuck due to some data source issues, but it should still explain it.
You can’t turn on deletes with the automatic setting when the source is not running with deletes in the incremental load setup.
Hi Andrew
Can you confirm that it behaves like explained above?
Hi @Thomas Lind ,
Thank you for your explanation video. It is a bit clunky to figure out based on the UI what is happening with the field ‘Is Tombstone’ in a DWH layer. Perhaps it works differently when an ODX Server is used than a business unit, but I am still not sure I understand.
What can I expect the ‘Is Tombstone’ field in the DSA table to represent?
In the old version of TimeXtender and with BU ODX, the field ‘Is Tombstone’ in DSA would only be = 1 when there are primary keys missing from the BU table, which are present in the DSA table. General best practice in the BU was to track soft-deletes in the BU ODX, and use the field ‘Is Tombstone’ from the BU ODX table to filter out deletes at some stage in the DWH (so map the system field to DWH table). Tracking deletes in DWH made sense only when, for example, hard-deletes were enable in BU ODX, or the table in BU ODX was being full loaded.
What does the ‘Is Tombstone’ field represent when the source is ODX Server table? Is the field mapped to something from the ODX Server? Or is it still only showing records that do not exist in ODX Server but do exist in the DSA? If the latter is true, than I still do not understand the purpose of tracking deletes in ODX Server.
