To homepage To homepage
Solved

How to recognize deleted records in ODX Server Storage

  • 7 April 2023
  • 7 replies
  • 150 views
andrew.gebhard
Rank
Userlevel 3
Badge +1

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

icon

Best answer by Thomas Lind 13 April 2023, 15:14

View original

7 replies

rory.smith
Rank
Userlevel 6
Badge +7

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

Thomas Lind
Rank
Userlevel 6
Badge +5

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.

andrew.gebhard
Rank
Userlevel 3
Badge +1

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

Thomas Lind
Rank
Userlevel 6
Badge +5

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.

Thomas Lind
Rank
Userlevel 6
Badge +5

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.

https://use.vg/K63Pot

You can’t turn on deletes with the automatic setting when the source is not running with deletes in the incremental load setup.

 

Thomas Lind
Rank
Userlevel 6
Badge +5

Hi Andrew

Can you confirm that it behaves like explained above?

andrew.gebhard
Rank
Userlevel 3
Badge +1

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.

Kind regards,

Andrew - E-mergo

Reply


Terms and ConditionsCookie settings