ION ETL - Filter out "DELETE" records

I noticed a record count difference between a table I downloaded via ION ETL and the record count in Compass.

By changing my compass query to included deleted records I was able to get the record count to match. I.E.

SELECT

COUNT(RepSet_Variation_ID)

FROM

infor.IncludeDeleted('FHDL_FSM_Actor')

I tried to add a query string to the Read step of my transformation, but it made no difference. I believe the "queryAll" function overrides the query string.

Is there anyway I can filter out these deleted records?

Find more posts tagged with

Infor Data Fabric

Accepted answers

unknown

I recommend that we don't reject "deleted = 0" record in ETL tool process(We can reject record with condition set in "Filter rows" item on Transformation).

If we reject "deleted = 0" records after read DataLake in ETL tool, the records deleted from ERP remain in local DB table.
So when we use import data( from Data Lake), "deleted = 0" condition is is necessary.
We have 3 way, reject date removed from ERP.
1) We set "deleted = 0" condition into query like "select * from etl_mitmas where deleted = 0"
2) We create view include "deleted = 0" condition like "create view mitmas_view as select * from etl_mitmas where deleted = 0"
3) After updating etl table, we delete "deleted = 1" records.
***I cannnot recomend 3) way because fragmentation is more likely to occur.

All comments

unknown

La forma de descarga con queyall en la herramienta es correcto.

Ahora en tu instancia local de base de datos puedes usar el campo DELETED como filtro en tu tabla que descargaste, para poder borrar los registros con borrado lógico:

Select * from Ln_TABLA where deleted=0

Kevin Heiman

the QueryAll is pulling files of all changes, so you would get Deletes and Archived. https://docs.infor.com/inforos/2024.x/en-us/useradminlib_cloud/default.html?helpcontent=etl_dl_ag/gfh1623923661703.html
the Query option uses compass query.

You should either run the on premise query omitting deleted, or do as you did and count all.

Most DW designs want deleted for some referential and historical reporting needs. Some even want all variations.

You could remove them from your DW with a trigger or procedure if you really don't want them.

unknown

The "DELETE" is found in the "_Action" column not a deleted column. Like the Update action, the queryall function should have deleted the record, not create it.

unknown

Thank you for the response. I agree with you not to filter the records so the record can be marked as deleted.

However I will be taking option 3. I recall when we were on prem every single query had to include a filter to remove deleted records. Of the hundreds of reports we ran, the deleted were never required.

Creating and maintaining views for over 500 tables, between FSM and GHR, is a cost overhead I prefer not to incur. We do have frequent additions and deletions to our replication sets.

unknown

If you select 3), I recommend periodic export and truncate and import for to avoid fragmentation.
In the case deletion is slow, I recomend 'create index index_name on <table_name> (deleted)'.
It is hard to create index for all table, so you should create index for table with slow deletion only.

Additional notes
if you put following query on GetDLDicDate process in ETL Transformation, processing time of transformation may take longer than expected.
--------------------------------
Select
CASE WHEN MAX(dlDocumentDate) IS NULL
THEN '1900-01-01T00:00:00.000Z'
ELSE (CONVERT(VARCHAR(33), CAST(max(dlDocumentDate) as datetime) - CAST('00:02' AS datetime), 127))+'Z'
END as queryDlDocumentDate
FROM MITWHL_rep
-------------------------------

In the case deleted record has max(dlDocumentDate), the next highest date and time record is referenced,
and the records after '2 minutes before the next largest date and time' are read fron DataLake.

I have experience there is 1 month between max(dlDocumentDate) from deleted records and next highest(dldocumentdate) and over 200000 records are loaded with inbound IF in that 2 minutes actually

To avoid this issue, we can make table for keeping max(dlDocumentDate) include deleted data etc.

Good Luck

Copyright © 2025 Infor. All rights reserved.