Incremental Changed Data Extraction

Changed data is extracted during daily/nightly batch runs of the Business Intelligence ETL processes. To ensure that only changed data is extracted, ETL processes must run successfully with zero warnings/errors.

Infosphere is not configurable and enforces zero tolerance for warnings/errors with all ETL's, hence any errors/warnings will result in the ETL process being marked as failed

Oracle Warehouse Builder is configurable, allowing ETL processes to be flagged as failing. This is controlled by the 'Maximum number of errors' property value.

Default runtime behavior in Oracle Warehouse Builder:

1. When a critical SQL exception occurs within an ETL process, then the ETL is marked as failed and the changed Data Capture date is not updated.
2. When the number of errors/warnings exceeds the 'Maximum number of errors' threshold (the Oracle Warehouse Builder default is 50) within an ETL process, then the ETL will be marked as failed.
3. If the number of warnings is less than the 'Maximum number of errors' threshold, then the ETL will be marked as completed successfully but the Changed Data Capture dates will NOT be updated.

To ensure that all ETL processes are correctly flagged as either successful or failed, a zero tolerance to warnings/errors is enforced by the build process which sets the 'Maximum number of errors' property to zero for all ETL's. This is set during the import process.

Two new build commands have been introduced which are embedded in the import process but can be run individually:

1. build owb.etl.runtime.check
   • This command reports an error if the max warnings threshold is not zero. The base value can be overridden at build time using -Denvironment.owbconfig.maxnumberoferrors=0
2. build owb.etl.runtime.set
   • This command sets the value of the max warnings threshold to zero. The base value can be overridden at build time using -Denvironment.owbconfig.maxnumberoferrors=0