How does Incorta handle Source deletions of rows ?

[KailaT]

How can Incorta support hard deletes in Source System ?

[amit_kothari · Accepted solution]

There are two usecases here - 
1) For some sources like Siebel which supports soft deletes (typically a binary valued column indicating if the row is deleted or not) then we handle deletes by using a table security filter on that field.  
2) For sources like E-Business Suite which does not support soft deletes then here is one way to handle it in Incorta -

1. Define a slim table which just has the Key fields of the table we want to support deletes
2. We will always  do a full load of the above table.
3. Make the above table as a parent table of the base table by creating a child join from the base table to this keys table.
4. Apply a table security filter on the base table as - "a field of parent table is not null" . 
5. Sample security filter on base table using the parent table field - not(isNull(Test.parent_table_with_keys.id)) 
6. For Oracle cloud ERP source first create a new oracle cloud applications data connections and select file extension as '.pecsv'
7. '   
   1. Then do a Primary Key Extract in BICC for that VO using manage extract schedules and selecting the job type as ‘Active primary Key Extract’  . We need to make sure that this always runs in full extract mode.
   2. Create a new Incorta schema (or use an existing one)  for these PK tables and add the PK tables using Schema wizard
   3. Always do a full load of the above PK table. Make sure that the Incremental toggle is turned off for this table 
   4. After this follow steps 3,4 & 5 above

This way the deleted rows from the base fact will always be filtered off from the dashboards. If required you can also schedule a periodic full load (for example every week) to flush out the deleted records from the base table.

[LayeredDelay · Accepted solution]

Another option to build on what Amit mentioned is to have a temp table in the source database (in the case of a database) and install a database trigger on the main table you want to track deletes. This trigger will only be for "on delete" which will only fire when you delete a record. You can then sync that table to the Incorta instance and use as Amit mentioned above as a security filter. If you have any questions let us know.