Dear Support,
My customer is using AFAS. From this tool they load data into TimeXtender with an API.
They have a wish to load the data from AFAS incrementally in the ODX.
Is it possible to load incrementally from an API to the ODX server and what are your suggestions to start with?
Best regards,
Christian Koeken
Best answer by Thomas Lind
View original
+5
Hi
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
+5
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.
+5
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.
+5
Hi
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?
+5
Hi
Yes I have sent the files to you.
In essence what I do is a Input field, which is explained in my Advanced RSD guide and for the ODX you use a dynamic date range and a query slicer query.
Hi
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
+5
Hi
I believe incremental load with subtraction should tackle this use case. Please see Subtract from value section in the following guide for more info:
Hi
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.
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?
+5
Hi
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
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.
+5
Hi
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.
+5
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.
