Skip to main content
To homepage To homepage

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 Ingest Instance. See Incremental load in Prepare Instances for more information on setting up incremental loading in a data area within a Prepare instance

Setup Incremental Loading in an Ingest 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 Ingest Instance Data Source for more detailed information on setting up primary keys for an Ingest Instance data source.

After setting up the Ingest Instance 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 Prepare Instance, 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.

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?

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.

It’s clear, thanks Thomas

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

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.

 

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

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

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:

 

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


Terms and ConditionsCookie settings