Follow

Understanding deployment strategy for incremental tables

This guide describes the most common incremental load strategies used in timeXtender and how tables are created, updated and deleted.

Source based incremental load

This load strategy handles inserted and updated data in the source, but doesn’t handle deletes. It relies on one or more incremental value fields of a numeric or date/time data type, such as transaction number, transaction date or inserted date. It uses a primary key on the destination to determine if an incoming record is an insert or update. To prevent gaps in data caused by long running transactions in the source system, an optional overlap can be added to each incremental selection rule.

Target based incremental load

This load strategy can be configured to handle inserts, updates and deletes in the source. It assumes that there are no reliable incremental value fields available in the source, thus compares the incoming records to the existing records to determine if it’s an insert, update or delete. To optimize performance, the comparison is done on a hashed value that is often the result of a composite key.

All other incremental load strategies are considered as hybrids and not described in this guide.

What is a full load of the valid table

The valid table is deleted if it exists

The valid table is created

The incremental tables are deleted if they exists

The incremental tables are created

 

Effectively this means that all source records are transformed, validated and inserted into the valid table

 

What is a full load of the incremental table

The incremental tables are deleted if they exists

The incremental tables are created

 

Effectively this means that all source records are extracted from the source, transformed and validated. The insert, update and delete towards the valid table happens according to the following rules

For source-based, new records are inserted into the valid table

For source-based, all existing records are updated without checking for changes

For target-based, new records are inserted into the valid table (optional)

For target-based, existing records are updated based on the incremental value key settings (optional)

For target-based, existing records in the valid table not found in the raw table are deleted from the valid table (optional)

If history is also enabled on the table, the valid table is handled according to the history settings (business key, type I and II settings)

 

A full load of the valid table is forced under the following conditions

When the physical valid table does not exist on SQL Server

 

A full load of the valid table is suggested under the following conditions

When there are changed to the incremental selection rules

When there are changes to the static selection rules

When the valid table structure changes (fields, change data type or are added or removed)

 

A full load of the incremental table is forced under the following conditions

When the physical incremental table does not exist on SQL Server

When the incremental table structure changes (fields, change data type or are added or removed)

When a full load of the valid table is executed as per above

 

Update strategy for valid table in all other scenarios

Any changes to a table such as adding, renaming, changing, deleting fields will attempt to update the existing physical table

If fields are added, the existing records are updated only if a default transformation has been added to the new field

Any changes to lookups, transformations, validations will not affect existing records

If fields are deleted, the existing records are not reprocessed with regards to lookups, primary key validations etc.

 

Strategy for all other objects

All other objects derived from a incremental load enabled table will always follow a drop and create strategy

Was this article helpful?
5 out of 5 found this helpful
Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.