Skip to main content
To homepage To homepage
Solved

Incremental load with Table Inserts and Views

  • February 23, 2023
  • 3 replies
  • 251 views
V
Participant

Hello,

Current Setup: TimeXtender 20.10.31.64 and ODX 20.10.31.

I need help setting up incremental load on a rather different type of data flow from the standard one, that has table inserts and views inbetween tables.

The flow is as follows:

Table A (has incremental load today) => View1 => TableB => Table C(MDW) once directly but also through View2.

Both views are necessary so we cannot remove them. 
 

My question is how to setup incremental load in timextender on this solution, if it is even possible. I am not sure how to do it through a view.
 

Currently there is only an incremental load set up on Table A and then we fully load table B and C.
 

Thank you

Best answer by fwagner

Hi @Victor,

thank you for your question!

Yes, this is absolutely possible to load incrementally table C in this case.

It is a 3 step process:

1. target table settings

On the target table make sure these settings are in place:

  • enable history
  • disable “truncate valid table”
  • and configure all the columns that you want to keep their original value (type 0), update (type 1) or build history (type 2) in the “History Settings”.

2. delta detection view

Basically, you build a view, that detects new and updated rows based on whichever logic makes sense:

delta detection view

3. add delta to target table with Table insert

then you add the delta to the target raw table with a “Table Insert”:

add to target table

Works for inserts and updates:

 

This is a pretty high level description of the process - can you take your next steps with this information?

 

Please let us know how it goes!

 

View original

3 replies

fwagner
Employee
Forum|alt.badge.img+4
  • fwagner
  • Employee
  • 33 replies
  • Answer
  • February 23, 2023

Hi @Victor,

thank you for your question!

Yes, this is absolutely possible to load incrementally table C in this case.

It is a 3 step process:

1. target table settings

On the target table make sure these settings are in place:

  • enable history
  • disable “truncate valid table”
  • and configure all the columns that you want to keep their original value (type 0), update (type 1) or build history (type 2) in the “History Settings”.

2. delta detection view

Basically, you build a view, that detects new and updated rows based on whichever logic makes sense:

delta detection view

3. add delta to target table with Table insert

then you add the delta to the target raw table with a “Table Insert”:

add to target table

Works for inserts and updates:

 

This is a pretty high level description of the process - can you take your next steps with this information?

 

Please let us know how it goes!

 

Christian Hauggaard
Community Manager
Forum|alt.badge.img+5

In terms of the arrow highlighted in red below (i.e. view 2 to Table C)

This should be possible by dragging the view onto the tables section in the MDW, and right clicking on the table and selecting add incremental selection rule.

However, incremental load is not currently supported for table inserts within the same data area / DW (i.e View 1 to table B in the DSA). Please see Frank’s workaround above on how to accomplish this part.

Christian Hauggaard
Community Manager
Forum|alt.badge.img+5

Hi @Victor are you satisfied with the recommendations? If so can you please help us by selecting and marking the best answer above. If you have any follow up  questions please let us know  

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