Incremental Settings
A number of tasks in Loome Integrate will allow you to add an Incremental Data Refresh on the target tables. The Data Migration task will also allow you to alter the target table name and add indexes to the tables.
The tasks that are available to add a Incremental configurations to are:
- Data Migration
- SSIS
- Stored Procedure
Click on a task, then select the Incremental Config option from the sub menu.
A row of incremental settings will appear for each query-based Dataset or table that has been selected from the data Source (the tables you have selected when setting up the Task).
To configure incremental load or index migration, click the corresponding pencil icon next to the table name.
Tip: Only one table can be configured at a time.
Incremental Settings
Is Incremental:
Set this flag on to specify that the content of this table will be retained and updated incrementally (incremental loading). When not set (the Default) the target table will be dropped and recreated from the Source definition every time the Data Migration step runs. When set, the target table and contents are retained, in accordance with the Data Currency Column and Refresh Period settings (below).
NOTE: Is Incremental MUST be ON for target tables which have identity column in order to preserve identity values.
Tip: Incremental Data Migration tasks create the target table if it doesn’t exist, and import the entire source if the target doesn’t exist or is empty.
Incremental Data Migration tasks are now based entirely on the column names in the target table, so they continue to work if new columns are added to the source.
Table Name:
The name of the source table that the configuration properties belong to.
Target Table Name:
If you want to change the name of the target table, you can specify the new name here. Or you can keep the same table name as the source.
Data Currency Column:
The target table column name which the incremental data migration will be based on. When this column is supplied, the maximum value of this column in the target table prior to the import will be applied as a filter to the same column in the source to limit the number of rows imported. This column should contain a date, datetime or increasing integer data types.
Refresh Period:
Together with the Refresh Type below, the refresh period determines how far back the target table data will be refreshed.
For example, to set a refresh period of 3 weeks you would enter 21 here and select “Day” as a Refresh Period Type. When executed this will refresh the incremental load 3 weeks back from the maximum value of the Data Currency Column.
Refresh Period Type:
The available refresh period type options are:
- No Refresh Type: Specify this value if you want to migrate all source table records that have a data currency column value that is greater than maximum current column value in the target table - I.E. All new data.
- Day
- Hour
- Minute
- Month
- Second
- TransactionID
- Year
CI:
Abbreviation for Clustered Index. Loome Integrate will retrieve the clustered index definition from the source table and recreate the index in the target table.
SI:
Abbreviation for Secondary or Non-Clustered Indexes. Loome Integrate will retrieve the definitions of all non-clustered indexes from the source table and recreate the indexes in the target table.
Tip: To import changes based on the maximum value in a data currency column while deleting previously imported data, define the Data Currency Column and Refresh Period but DO NOT set the Is Incremental flag ON.
If Is Incremental is ON (ticked) the incremental rows will replace rows in the target which have a Data Currency Column greater than (>) the calculated incremental start point. Rows which have a Data Currency Column less than or equal to (<=) the start point will be retained in the target.
If Is Incremental is OFF (not ticked) the target table will be recreated and only the incremental rows will be imported to the target.
Tip: If there is no incremental data to import and Is Incremental is OFF, then the target table will no longer contain a maximum value, and the entire source table will be imported next time the step is executed.
We strongly recommend ensuring you define a Refresh Period when applying a Data Currency Column with Is Incremental OFF. This will ensure you re-import the maximum value.
Incremental Data Migration Logic
When a table has been configured for Incremental Data Migration, Loome Integrate will use the following logic to migrate data on each execution.
Logic 1: When no Refresh Period has been specified
In the target table, Loome Integrate will retrieve the maximum value of the Data Currency Column.
In the source table, Loome Integrate will retrieve all records where the column matching the target Data Currency Column has a value that is greater than the above maximum value.
The retrieved data will be migrated to the target table.
Refer to below example:
Logic 2: When a Refresh Period of 8 Days has been specified
In the target table, Loome Integrate will retrieve the maximum value of the Data Currency Column and also calculate the Data Currency Column value that is: the start of the Refresh Period = the maximum value minus (-) the number specified in the Refresh Period
Loome Integrate will delete all records in the target table, that has a Data Currency Column value going back from the maximum value to the start of the Refresh Period. For example, if a Refresh Type of “Day” and a Period of “8” has been specified, then Loome Integrate will delete all records that has a Data Currency Column value matching the dates in the 8 Day Period.
In the source table, Loome Integrate will retrieve all records where the column matching the target Data Currency Column has a value that is greater than the target table’s start of Refresh Period.
Refer to the below example:
