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 loading for an ODX instance. See Incremental load in Data Warehouse Instances for more information on setting up incremental loading in a data area within a Data Warehouse instance
Setup Incremental Loading in an ODX Instance
It is recommended to start by setting up the primary keys on the tables that will be incrementally loaded. See Set up Primary Key Rules on an ODX Data Source for more detailed information on setting up primary keys for an ODX data source.
After setting up the ODX data source and configuring the primary key rules for the relevant tables, incremental loading rules can be added as follows:
It is possible to configure specific schemas, tables, or columns for incremental load rules. The key focus is on the column being monitored. For instance, one might search for the ModifiedDateTime field across all tables.
Subtract from Value: This option allows subtraction from the field to which the rule applies. This feature is useful for data sources where the modified date is a Date field instead of a DateTime field. Additionally, it can be utilized for data sources with a created date but no modified date, where incremental loading would improve performance. In such cases, incremental loading based on the created date with an offset allows updates to occur on rows recently created, provided the changes happen within the defined interval. The Amount specifies the decrement to apply, which could be on a timestamp or similar field. Time defines the units—Seconds, Minutes, Hours, Days, Weeks, or Years—that will be subtracted from the last added DateTime.
Additional Actions: These settings determine whether only inserts are used or if deletes and updates are also handled.
- Without Deletes: The system will only insert new rows larger than the rule field, meaning it doesn't check if the rows already exist in the source.
- With Deletes: A primary key (PK) table or folder is created, where the primary keys are compared to the source for updates or deletes.
If updates are not used in the setup, they will not be available in the Data Warehouse, and the same applies to deletes. Therefore, if updates and deletes are not used, frequent full loads will be necessary.
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 the incremental loading rule includes Deletes or Updates, then after you click OK to save the rule the following menu regarding primary keys may appear, which shows displays the tables that are missing primary keys. Select the primary keys for these tables as are appropriate and necessary.
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.
