Incremental Load in an ODX Instance

  • 28 December 2022
  • 3 replies
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.

3 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