Each transformation in the Data Warehouse ETL job utilizes the WH_LAST_REFRESH table to determine which records need to be part of the refresh. The transform reads the LAST_REFRESH_DATE from the WH_LAST_REFRESH table and compares this value to the MODIFIED_ON date of each row in the source tables. If the MODIFIED_ON date is greater than the LAST_REFRESH_DATE then the row will be refreshed in the Data Warehouse.

Note: In order for the ETL process to run, the appropriate database driver needs to be uploaded to the DW directory, e.g. DW_FOLDER/libext/JDBC/ojdbc6.jar (for Oracle).

In the very first ETL run, there won't be any values in WH_LAST_REFRESH, so every source row will be refreshed in the Data Warehouse. Subsequent runs will have much less activity than the first run.

This job produces the TeamConnect_Warehouse_date_time.log log file in the ./TCWH_logs subdirectory. For the TeamConnect_Warehouse_date_time.log file, where date and time are numbers representing the time when the job was started. This file contains process logging messages at a level of detail that you chose during configuration.

By default, kettle.properties parameter WH_REFRESH_LOOKUPS=NO. This means that during installation the Data Warehouse tables related to categories and currencies (WH_CATEGORIES and WH_CURRENCIES) will be populated with data from the source database. However, those tables will not be refreshed during any later ETL jobs.

If you have changed your TeamConnect design in ways that affect those tables, and you want your changes to be reflected in Data Warehouse, you must edit kettle.properties to change the parameter value to YES. Then the ETL jobs will pick up changes to those tables, and will continue to do so until you change the parameter value back to NO.

The ETL job also removes deleted records from Data Warehouse. These deletions occur after the changed rows are loaded into the warehouse. Each table in the source database has a trigger that captures the deleted record keys in a table called WH_REMOVED_RECORDS. WH_REMOVED_RECORDS is read and each corresponding key in the warehouse is removed in accordance with the relationships between the Data Warehouse tables (also referred to as cascade deletion).

The following table lists the triggers in the source database.

Source Database Triggers