cancel
Showing results for 
Search instead for 
Did you mean: 

How does Incorta handle Source deletions of rows ?

KailaT
Employee Alumni
Employee Alumni

How can Incorta support hard deletes in Source System ?

2 REPLIES 2

amit_kothari
Employee
Employee

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. amit_kothari_0-1678388429282.png

     

     
    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
Employee
Employee

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.