To homepage To homepage
Solved

Incremental load with Table Inserts and Views

  • 23 February 2023
  • 3 replies
  • 157 views
V
Rank

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

icon

Best answer by fwagner 23 February 2023, 16:05

View original

3 replies

fwagner
Rank
Userlevel 2
Badge +3

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
Rank
Userlevel 6
Badge +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
Rank
Userlevel 6
Badge +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


Terms and ConditionsCookie settings