I’m planning to change a history table in DSA into an incremental table without removing the history and I want to make sure that I understand some of the settings correctly. The ideal outcome for me is the following:
New data added incrementally without the history being maintained
Regarding the old history:
I want to keep all rows with hSCD Is Current]=1
I don’t want to keep rows with iSCD Is Current]=0
Here are some questions I have about options in Table Settings > Data extraction :
Does it make sense for this scenario to have the boxes checked for “Truncate raw table before transfer” and “Empty raw table after data cleansing”?
What is the best approach for me to take with the delete options (“Don’t handle deletes”, “Use soft deletes” and “Use hard deletes”)? If I’m fine with new rows being overwritten can I then create some kind of incremental rule to make sure that old history is changed (or at least not for SCD Is Current]=1)?
Thanks,
Árni
Page 1 / 1
Hi Árni,
Turning on incremental load while keeping history enabled will not delete any historical records. Turning off history will require a deploy of the valid and raw tables, causing all historical data to be deleted. If you want to remove records where the hIs Current] = 0, while keeping records which are no longer available in the source, you will need to do this outside of TimeXtender with a script on the SQL Server.
In terms of your other questions:
Truncate raw table before transfer is on by default and should remain on, unless you know for certain that each increment you load will never contain records loaded in a previous increment. Given your requirements, there is no reason to turn this off.
Empty raw table after data cleansing only helps to reduce the total storage used on your server by emptying (truncating) the table immediately after data cleansing, instead of just prior to loading a new increment
Hard deletes is incompatible with history tables. In a history table, data is never deleted.
When combining history and incremental load, it is only possible to use delete handling in the history table settings. Incremental deletes will not be allowed.
If I’m fine with new rows being overwritten can I then create some kind of incremental rule to make sure that old history is changed (or at least not for eSCD Is Current]=1)?
I don't understand this part of your question. Any records not in your raw table (newest increment) will not be changed in your history table. If you do a full load of your incremental history table, all available source records will be loaded into the raw table, and these records will be updated in the history based on your history settings.
Please not that, if data is being loaded into your DSA from an Ingest Instance or ODX Server, instead of a business unit, there are some complexities when it comes to deletion handling with incremental load.
