Incremental load advice

Hello,

I have access to SQL server with transactional data. I am working on a process to upload this to a TB cube.

At month end, these records may be updated with new base values. This is captured on premise with a stored procedure that updates a "master" table and increments a column called "VarID" (variation id). Following the guide I have a unique identifier, a timestamp as well as VarID in my master table.

Next, I am using three data flows:

1. LoadCubeMaster - this triggers a stored procedure to upload the master table.

2. CubeMasterToDL - this uses a JSON document to send the master table to the data lake.

3. Master - this runs a relational modelling script that runs a load query of said document to a relational table, then runs a mapping to a cube

The problem I am having is related to the variation ID. If one of the records gets updated then the VarID increments and uploads that record. But once this data flow is complete, if another record is updated then the data flow will no longer pick this up without selecting the active connection point and rewinding the incremental again. The goal is for this to be handled without any user input.

What is the recommended way to handle updated records?

How will this affect future periods?

I am relatively new to using the data lake so any advice would be greatly appreciated.

Find more posts tagged with

Infor Data Fabric

Accepted answers

All comments

Kevin Heiman

I am not super clear on your process, but will dive in to the last paragraph point about successive incremental data pulls. There is an option in the Inquiry to see the current incremental value. The incremental value is commonly a timestamp, and does not involve any data key or variation. Some requirement have other data filters, but those are in the WHERE clause, and not in the incremental.

The database connection AnySQL definition via the modeler is where this is defined.

https://docs.infor.com/inforos/2024.x/en-us/useradminlib_cloud/default.html?helpcontent=iontechconceag_cloud_osm/imy1518689690242.html

This should help you understand why the incremental is not selecting the next set of data. There is also a option to look at the logs / debug in the Enterprise connector if you find you need to look at that level.

haider-razzak

Hi Kevin, what is not clear about the process?

I have tried altering the process to use the timestamp column and I see similar results. The active connection point now uses the highest timestamp in the column, which was 4:15pm, and added an hour to it. This is now an hour in the future so if I updated a record now it will not be picked up.

Thanks

Kevin Heiman

What was not clear was the other steps in your process, however I think the core of this is about what we are discussing here.

You do have to take time zones into consideration. The SQL server / enterprise connector and your workstation are all on the same Time Zone?

I retested a configuration I had here and this incremental is working.

what is the frequency of pull? I presume you confirmed it is being run?

Can you look at the SQL server access logs, and the SQL request to see what was requested?

Check the EC log for the execution?

There is a Time Offset option to do an offset to not get the latest timestamp, which allows the data in flight to settle. I assume you are not using this?

Copyright © 2025 Infor. All rights reserved.