To homepage To homepage
Solved

Computed columns and incremental loading

  • 31 May 2023
  • 9 replies
  • 163 views
sierd.zuiderveld
Rank
Userlevel 3
Badge +1

Hi,

I am working on a column that evaluates a date against GetDate() and returns a 1 or 0. The table can be loaded incrementally. I assumed that if I turned on "Keep field values up-to-date", this column would always be recalculated at loadtime, but it seems that this is not true.

Is there a way to make these two work together or do I have to give up on incremental loading?

Thanks

icon

Best answer by daniel 1 June 2023, 09:04

View original

9 replies

fwagner
Rank
Userlevel 2
Badge +3

Hi @sierd.zuiderveld 

thank you for your question!

You can absolutely have such a column together with incremental loading.

Here’s how I would go about it:

  1. Load the table incrementally
  2. Create a custom SQL step that will update the whole column, e.g. UPDATE [your_valid_table] SET [your_flag_column] = [your_date_column] <= GETDATE()
  3. Add the custom SQL step as a post scripts on the table (right click, advanced, “Set pre- and post scripts” and configure it to execute as the very last step:

 

Hope that helps!

sierd.zuiderveld
Rank
Userlevel 3
Badge +1

Thanks @fwagner 

Currently this option is grayed out for me.
 

Can you point me to some documentation on how to enable this? (v 20.10.37)

 

Also, will this update all DW_TimeStamp's or only for the row values that actually change? 

rory.smith
Rank
Userlevel 5
Badge +7

Hi @sierd.zuiderveld ,

 

the ‘Keep field values up-to-date’ option will only update a lookup (not sure about transformations) that had a NULL value if in later loads it gets an actual value. It will not update all lookups on any changes.

The ‘Set Pre- and postscripts’ option is greyed out until you have at least one Script Action defined under Scripts in the layer you want to add such a step. If you already have a Stored Procedure: make a Script Action that does: EXEC <stored procedure you made>.

If you want your post step to update DW_Timestamps, you will need to do that in your script, TimeXtender only does this for records handled by the ‘normal’ route.

daniel
Rank
Userlevel 4
Badge +5

Hey @sierd.zuiderveld ,

 

I like to use a self lookup for this. So have you table, then create a view with the fields you want (or in this case maybe the GETDATE())) and keep thevalues up to date turned on)  and then lookup the field from the view in to the table. You could also have the view calculate all the booleans and have then one look op on the PK or the DW_Id.

i found this articles which has helped me a couple of time:
https://legacysupport.timextender.com/hc/en-us/articles/360020610578-Create-a-self-join-table-using-Custom-Views

(and personally im not to fond of pre-post scripts as they can be hard to find. Ilike everything to be as naitive as possible to my lineage is never broken)

 

Hope this helps

= Daniel

rory.smith
Rank
Userlevel 5
Badge +7

Hi @daniel ,

that may work as long as your scenario matches the cases where ‘Keep field values up-to-date’ actually performs an update. The script approach gives you full control over which records get updated at the cost of less readability (and manual dependency setting if the source of lookups is external to the table itself). In large fact tables it is also likely that a well-written update query will strongly outperform the ‘Keep lookup values up-to-date’ feature.

You will find that ‘Keep field values up-to-date’ does not do what your intuition makes you think it does, this is why the tooltip is rather specifically defined

sierd.zuiderveld
Rank
Userlevel 3
Badge +1

Hi @rory.smith , 

Thanks for the clarification on the script actions! It's very helpful.

 

Regarding the 'Keep values up-to-date' feature, I have verified this and found that any change in value triggers an update in the [TableName]_LookupKey and consequently in the incremental table as well. The previous value does not have to be NULL for this to happen.

 

This also implies that @daniel 's idea would work. 

daniel
Rank
Userlevel 4
Badge +5

Hey @sierd.zuiderveld ,

 

Thanks for checking that! In this case I would use the script pre-postfuncionality because of perforance. 
It kind of depens how fast it would run,but if it is really slow then a full load might be the better option (or the scripting option @fwagner and @rory.smith sugested)

 

= Daniel

rory.smith
Rank
Userlevel 5
Badge +7

Hi @sierd.zuiderveld ,

 

that is interesting - then that must have been updated somehwere between 20.10.26 and 20.10.37 or your scenario is different enough from the one I encountered a year ago that it does work for you. It would be good if someone from TimeXtender can confirm either way.

I cannot share the details of the tickets I had then, but we pretty definitively proved it did not work for updates to lookups from Table A to Table B (the table with ‘Keep field values up-to-date’). TimeXtender R&D confirmed it was ‘working as intended’ at the time and I pushed it to the Focus Group.

Christian Hauggaard
Rank
Userlevel 6
Badge +5

Hi @sierd.zuiderveld has the issue been resolved? If so, could you please help us by marking a best answer above? Please let us know if you have any follow up questions

Reply


Terms and ConditionsCookie settings