Incremental loading with APIs

Hi @christian.koeken 

Yes if they are using the RSD REST provider, they should be able to add primary key and incremental rules on their data source (assuming the tables have primary key fields and a modified date timestamp). Please see the following guide

• Incremental Load in an Ingest Instance Incremental

Hi Christian

That depends on how the API can be used. It is a requirement that a date field, ideally datetime field exists that can limit what is given back from the API.

Like URI: https://www.api.com/rest/v2/sales?from=2023-03-03

If you have that, you can use a RSD file and add a dynamic date to add to it, for example yesterday and then use that in your data source.

In the BU you can add a parameter and use the max value from the incremental table in a dynamic variable.

I have an idea that may give the same in a ODX, but I do not have a proof about it working.

Hi Christian

I actually have a file using AFAS, that was used as a template for testing if Incremental load would work.

So it will be possible to set it up for you as well.

@christian.koeken is the issue resolved? If so could you help us by marking a best answer above? Please let us know if we can assist further

Hi @Thomas Lind,

In your last post you said you have a file using AFAS, that was used as a template for testing if Incremental load would work.

Am I right that you have an example for me that would possibly work?

Hi @Thomas Lind @Christian Hauggaard, 

We also have a RSD that incrementally loads data from an api. However, the api is quite unstable. Sometimes certain devices go down for a day or two and then the data only becomes available later. In the meanwhile we load new data from the same device, and it the rows which were missing won't be entirely added, usually only one row is added. The solution we apply now is deleting all the rows from the moment it went wrong and then manually editing the RSD with the data range and running the transfer task until the data is complete again. Do you guys know a solution for this so the transfer task does this by itself?

Example:

Device down

Pk1            Pk2            count(rows)

device 1 - 12/3/2024 - 10

(device down)

device 1 - 15/3/2024 - 15

Device up again

Pk1            Pk2            count(rows)

device 1 - 12/3/2024 - 10

device 1 - 13/3/2024 - 1

device 1 - 14/3/2024 - 1

device 1 - 15/3/2024 - 15

Expected result

Pk1            Pk2            count(rows)

device 1 - 12/3/2024 - 10

device 1 - 13/3/2024 - 12

device 1 - 14/3/2024 - 11

device 1 - 15/3/2024 - 15

These are ours settings

Hi @spenning 

I believe incremental load with subtraction should tackle this use case. Please see Subtract from value section in the following guide for more info:

• Incremental Load in an Ingest Instance Incremental

Hi @Christian Hauggaard ,

Thanks, we already had a suspicion that this feature could help us, we will run some tests. Besides that, we were looking into implementing the incremental loading maybe in the DWH as seen here.

• Incremental load in Prepare Instances Tutorial

In the article it says that in order for a table to be incrementally loaded in DWH, it also needs to be incrementally loaded in ODX? So it would not be possible to load data incrementally from a ODX source which is not incremental, into a DWH table which is incremental. Or if this is possible, would it be recommended to do so? 

Hi @spenning 

If the error happens on the date range you have set up in your URI in the RSD file applying subtractions does not work or you will have to add the subtraction to the input dates.

Hi @Thomas Lind @Christian Hauggaard  , 

We have used the subtraction date in the ODX incremental settings and this seems to work. No need to change the RSD itself :) 

A confirmation to know if we can use incremental loading in DWH without creating incremental rules in odx would be nice to know for future planning / implementations. 

Hi @spenning 

I think you can set up a rule like for a normal table coming from another DWH. So if you make a rule for the date field. However, if it will not use incremental load on the ODX there will be a lot of redundant data in the ODX storage.

@spenning does Thomas’ comment above answer your question? Please let me know if you have any follow up questions 

Hello, we have the same question. We tried to add a primary key to incrementally load into the DWH based on a NOT incremental table in the ODX (The API provider doesn't support incremental loads) I also don't understand why this should be a prerequisite. We get the error message:

An incremental load enabled table requires all mapped odx tables to support incremental load

With kind regards,

Jeroen

Hi @Jeroenski74 

Incremental load only works if a field to use for it exists in the result. The incremental load we made for REST APIs have always been based on allowed overlays of dates as you could not be 100% precise.

The error about mapped sources means that one of the tables doesn’t have the necessary primary key or incremental value field in its table.