This page provides information on how to add support for incremental load of a Fact in the ETL process.
It is assumed that an existing Fact is the target, i.e. SSIS packages for load of the Stage, Dw and Dm areas already exist.
Make sure to add the following information in the file dbo.SSIS_PACKAGE_CONFIGInsertStmts.sql in component APTOOL for the fact source identity that should support incremental load.
| Column Name | Column Value | Note |
|---|---|---|
| supports_incremental_load | 1 | Value=1 means that true incremental load is supported. For all other set Value=0 |
| incr_load_date_col_name | MVT_CREATED_DT | The name of the column in the snapshot (MVT) table that keeps the created datetime for each record. Normally the name is MVT_CREATED_DT but could be something else. |
| last_max_incr_load_dt | NULL | Always supply NULL. This column will be updated during the ETL process. |
| suggested_incr_stmt | WHERE MVT_CREATED_DATE > &LAST_MAX_INCR_LOAD_DT |
This is the suggested WHERE statement to be
used when loading incrementally from IFS Applications |
The incremental snapshot source table in IFS Applications will have at least one or even two new columns to consider in the stage step.
The column name is given by attribute incr_load_date_col_name in the configuration table. In most cases the column name is MVT_CREATED_DT. The data type should be datetime.
A unique fact identifier may have been added during development of Incremental Load. The suggested name is ID but could also be OBJID.
Note: Use this Fact attribute/column instead of building it in the SSIS packages as an additional column.
Note: Only applies to the standard stage SSIS package
Add a new string variable named XConfLastMaxIncrLoadDtStr. The purpose with the variable is to store the value of the last saved max created timestamp of transactions transferred from IFS Applications database to the data warehouse for the current source.
Note: Only applies to the standard stage SSIS package
The Execute SQL task that gets configuration settings should be updated to select the current value the column Last_Max_Incr_Load_Dt_Str from the configuration table for the current source.
@[User::XConf_Load_Type] == "FULL" ? "" : @[User::XConf_Load_Type] == "CONDITIONAL" ?
@[User::XConf_Cond_Where] : @[User::XConf_Load_Type] == "INCREMENTAL" ?
REPLACE(@[User::XConf_Incr_Where],"&LAST_MAX_INCR_LOAD_DT",
"TO_DATE('"+@[User::XConfLastMaxIncrLoadDtStr]+"', 'YYYY-MM-DD HH24:MI:SS')" ) : ""
The purpose is to make sure that the the variable
&LAST_MAX_INCR_LOAD_DT, if it exists in the incremental
WHERE condition, is replaced by
the current max last loaded date for the current entity, represented by the
configuration view (SSIS_PACKAGE_CONFIG) column
Last_Max_Incr_Load_Dt_Str
The column name might of course differ but is in the standard case always MVT_CREATED_DT.WHERE MVT_CREATED_DT > &LAST_MAX_INCR_LOAD_DT
Running the package
should give the same amount of rows as the FULL
load since if no processing has been done previously, the variable
&LAST_MAX_INCR_LOAD_DT will be set to
1900-01-01 00:00:00
The incremental snapshot source table in IFS Applications will have at least one or even two new columns to consider in the Dw step.
The column name is given by attribute incr_load_date_col_name in the configuration table. In most cases the column name is MVT_CREATED_DT. The data type should be datetime.
A unique fact identifier may have been added during development of Incremental Load. The suggested name is ID but could also be OBJID.
Note: Use this Fact attribute/column instead of building it in the SSIS packages as an additional column.
No other modifications are necessary. Just make sure that the SSIS packages still are working and that transactions are correctly transferred from the Stage table(s) to the Dw tables(s). Also make sure that the columns representing Created Snapshot Data and Unique Identity have been successfully transferred.
The incremental snapshot source table in IFS Applications will have at least one or even two new columns to consider in the Dm step.
The column name is given by attribute incr_load_date_col_name in the configuration table. In most cases the column name is MVT_CREATED_DT. The data type should be datetime.
A unique fact identifier may have been added during development of Incremental Load. The suggested name is ID but could also be OBJID.
Note: Use this Fact attribute/column instead of building it in the SSIS packages as an additional column.
Note: Only applies to the standard stage SSIS package
Add a new DateTime variable named XMaxCreatedDt. The purpose with the variable is to store the value of the max created timestamp of transactions, in the current execution, transferred from the data warehouse to the data mart for the current source.
Use an existing SSIS package that supports incremental load as a template. Select the Script Component and then just copy it in to the target Dm SSIS package.
The purpose of this task is to update the configuration table with the last max changed date, i.e. the last max created timestamp of transactions transferred to the Dm area.
