Can icet.DCTransDate be used to manage incremental ETL?

Legacy Contributor

I'm using SSIS and FetchWhere to do incremental updates by finding records later than the max transdttmz in the data lake compared to the local tables.  The icet and kpsk tables don't have that column.  But I see DCTransDate in icet and possibly all tables.  Can that field be used somehow in the same way?  What does the field name mean?

Thanks!

Legacy Contributor

Hey Denver,

Sounds like this is CSD/SX.e territory 

I'm admittedly not too familiar with CSD schemas and the content they store so maybe someone with a background in that ERP might have more specifics details for you here. However, I think there's another angle you should consider here.

Take a look at the documentation for incremental data loading patterns with Compass. All of the content you're querying is stored within data objects (a fancy way of saying files) and each of those files is indexed when that data object arrived in Data Lake. You're actually able to use this information to help process your data incrementally by using the infor.lastModified() function which returns back a synthetic column you can process against.

This effectively allows you to process incrementally at the file level first and you can further evaluate the data afterwards. After you run your initial load, capture the MAX() value of infor.lastModified() and use that in your subsequent query predicates. I have an example below that helps better outline this.

Here's a fairly ordinary table called Products with an equally ordinary SELECT against it.

You can use a query hint to expose all of the synthetic properties associated with the rows and records in the table. Either use the Compass browser editor to auto-generate a query with additional properties or include the following above your query:

--*includeInSelectAll=s/g/p/l

As you scroll to the right of your resultset, you'll see the synthetic columns that outline the address of the data object (accessible via infor.dataObjectID()), the lastModified timestamp (accessible via infor.lastModified()), and other keys - that's for another time

However, you'll find that these synthetic values and the functions used to retrieve them can be pretty useful. For my products table, for example, I can identify all of the unique data object IDs and the time at which they were landed in the Data Lake. Keep in mind, this is using the "latest variation" or most current results. There could be other data objects in Data Lake that aren't listed here but you can simply wrap your table in one of the variation functions to influence the behavior of the Data Lake. For example:

SELECT

DISTINCT

infor.DataObjectID(),

infor.lastModified()

FROM infor.allVariations('Products')

Right, so bringing this all together. You can use those synthetic keys as an argument for filtering on the data. You can see here that I grabbed one of the infor.lastModified() values and appended it to my query and retrieved data only from data objects that have landed in the Data Lake after that time.

You can start to fiddle with these concepts like retrieving all records from a particular data object or lop on additional logic to filter some more.