Incremental

Incremental Load in an ODX Instance

  • 28 December 2022
  • 9 replies
  • 2819 views
Incremental Load in an ODX Instance
Userlevel 5
Badge +5

Relates to TimeXtender 6024.1 and later versions. The subtract from value feature was released in TimeXtender 6024.1

This article describes how to setup incremental load in an ODX instance, for more information on setting up incremental load in a data area within a Data Warehouse instance see Incremental load in Data Warehouse Instances.

Setup Incremental Loading in an ODX Instance

When you have created a data source in the ODX, you have the option of setting up Incremental Load.

In there you can add a rule with the following options.

  • You can set it up for some specific schemastables, or specific columns. Most importantly is the column it will look for. In the above, I look for the ModifiedDateTime field across all tables.
  • The Subtract from value is an option to subtract from the field your rule applies to. The ability to apply offset incremental selection rules can be used for data sources where the modified date is a Date field, rather than a DateTime field. It can also be used for data sources where there is a created date but not a modified date, and due to performance an incremental load would be beneficial. In this case, using incremental load, based on created date with an offset, allows updates to occur on the rows have been recently created, provided that the alteration happens in that interval that has been defined. Amount is the decrease it will apply to a value applied. This could be on a timestamp or similar. Time is the Seconds, Minutes, Hours, Days, Weeks, or Years it will subtract from the last added DateTime.
  • The Additional actions are regarding whether we use only inserts, or also handle deletes and updates.
    • Without deletes, it will only insert new rows that are larger than the rule field, which means that it doesn't check if the rows already exist in the source, or not.
    • With deletes, a PK table or folder is generated, where the primary keys are compared to the source for updates or deletes.
    • If you do not use updates in your setup, you will not be able to use updates in the Data Warehouse and the same goes for deletes. So if you do not use this, you will need to run frequent full loads.

When added and you have clicked on Refresh, you will see all the tables that are hit by the rule and on which field this is applied.

If you use Deletes or Updates in your rule and click OK in this, you may see the following menu, which shows you the tables with missing primary keys and gives you the possibility to choose what these are.

When this is set incremental load is set on the source and it will attempt to use it on transfer tasks with this setting.

If you uncheck this, it will always run full load transfers.


9 replies

Userlevel 3
Badge +1

What it’s the procedure to setup incremental loading in a table in DSA area if you define the incremental rule after that the table was added?

Userlevel 5
Badge +5

This link should explain all there is to know about incremental load in modern data warehouse instances.

If you add incremental load on a table in the ODX after it has been added to a data area, it will start using that when you have synchronized the data source and run the Synchronize Objects task.
That is how all changes in the ODX data sources gets added to the MDW instances.
If the table is not set to use Automatic, but full load it will still see the change done in the ODX, but no changes to how it adds the data will be done.

Userlevel 3
Badge +1

It’s clear, thanks Thomas

Userlevel 2
Badge

Hi @Thomas Lind 

I am a bit unclear about setting up the subtraction on an incremental rule. In your explanation: 

 Amount is the decrease it will apply to a value applied. This could be on a timestamp or similar. Time is the Seconds, Minutes, Hours, Days, Weeks, or Years it will subtract from the last added DateTime.

In 20.10 incremental loading, we selected a subtraction in time, but no ‘amount’. I am unclear about the ‘decrease it will apply to a value applied’. Could you please elaborate? 

 

Kind regards,

Andrew

Userlevel 5
Badge +5

Hi @andrew.gebhard 

When you make a rule on a timestamp field it will just be latest value - subtraction amount.

 

When you make a rule on a DateTime field the subtraction time will be converted to seconds.

 

It was the same in the old version.

 

Userlevel 2

Or you can apply subtract from value to a numeric field, e.g. include the previous 100 records based on the highest ID.

Userlevel 2
Badge

Hi,

Thanks for the responses! So it's an either/or situation and not something where both will be set for a given table. That clears things up.

Kind regards,
Andrew

Badge +1

Please add to the instruction that dynamically selecting the primary key fields using a rule only works when the data type is set to  “String”.

Example that doesn’t work:

Example that is working:

 

Userlevel 5
Badge +5

Please add to the instruction that dynamically selecting the primary key fields using a rule only works when the data type is set to  “String”.

Example that doesn’t work:

Example that is working:

 

Hi @rogier.helmus 

Regarding this. It is not that it has to be a string, it is that it will use the Data Type as a filter option.

So if a field you want to hit with your rule is an integer of some sort it will not be hit if you set the data type to String.

You can read about this in our Primary Key guide.

Unknown is not a catch all, it is a specific kind of data type our program can handle.

Reply