Skip to main content
To homepage To homepage
Solved

Incremental load in TimeXtender REST API 6.1

S
Contributor
Forum|alt.badge.img

Hello,

We are looking to implement incremental data fetching from REST API endpoints, specifically by dynamically filtering with an updatedAt parameter. The goal is to use the maximum updatedAt value previously fetched from each API call, which is stored in our ODX tables.

While I'm aware of the capability to use dynamic parameters based on endpoints and queries within TimeXtender, I'm struggling to find a method to reference the maximum updatedAt value already loaded in the ODX tables for this purpose.

Has anyone implemented a similar dynamic data fetching mechanism in TimeXtender? If so, could you share how you managed to reference data from the ODX tables to set the parameters for the next API call?

Thanks!

Best answer by Thomas Lind

Hi @sigsol 

There currently is no option to set up a REST Data Source to run with incremental load. So you will need to do it another way.

You need to be sure that this UpdatedAt date or another date field becomes a value in the resulting table that is containing the max date of the data pulled. 

Once this is found you run the REST data source once to get the data into the ODX. 

Then you add it to your DW instance as a table, turn on History (SCD) on the table and deploy and executes it once.

Now you can make a dynamic SQL query in your REST data source that looks at this table and pulls the Max UpdatedAt date as a option and it will be used on future executions.

If the result doesn’t contain the UpdatedAt date you will need to either use a dynamic date as the rule and accept that there is some overlap or use the DW_TimeStamp field that is added to all tables.

UPDATE

With the history table set up you should also apply soft deletes, so you can see when rows are removed from the source.

If you want to avoid these in the following Data Areas, you can apply a rule to only transfer data where IsTombStone is equal to 0 or False.

View original
Did this topic help you find an answer to your question?

3 replies

Thomas Lind
Community Manager
Forum|alt.badge.img+5
  • Thomas Lind
  • Community Manager
  • 1070 replies
  • Answer
  • April 15, 2024

Hi @sigsol 

There currently is no option to set up a REST Data Source to run with incremental load. So you will need to do it another way.

You need to be sure that this UpdatedAt date or another date field becomes a value in the resulting table that is containing the max date of the data pulled. 

Once this is found you run the REST data source once to get the data into the ODX. 

Then you add it to your DW instance as a table, turn on History (SCD) on the table and deploy and executes it once.

Now you can make a dynamic SQL query in your REST data source that looks at this table and pulls the Max UpdatedAt date as a option and it will be used on future executions.

If the result doesn’t contain the UpdatedAt date you will need to either use a dynamic date as the rule and accept that there is some overlap or use the DW_TimeStamp field that is added to all tables.

UPDATE

With the history table set up you should also apply soft deletes, so you can see when rows are removed from the source.

If you want to avoid these in the following Data Areas, you can apply a rule to only transfer data where IsTombStone is equal to 0 or False.

S
Contributor
Forum|alt.badge.img
  • sigsol
  • Author
  • Contributor
  • 26 replies
  • April 17, 2024

It works, thank you @Thomas Lind !

I’m not sure how the IsTombStone flag can know that rows have been deleted when we’re doing incremental load, however. Only solution would be that the source table itself has soft deletes (that updates the UpdatedAt value) and then just use the IsDeleted flag found in the source.

Any idea if support for incremental load in the REST Data Source is on the roadmap?

Thomas Lind
Community Manager
Forum|alt.badge.img+5
  • Thomas Lind
  • Community Manager
  • 1070 replies
  • April 17, 2024

Hi @sigsol 

You are welcome. The reason for the soft delete option is that with an History table you can’t delete any rows. So if the source REST API has lost some rows after a transfer it will not be able to see it unless you turn on this option.

Reply


Most helpful members this week

Terms and ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings