About Diagnostics Healthcheck and ETL Diagnostics and Automatic Correction

Diagnostic Health Check is a preliminary ETL phase in which a diagnostic report is generated to identify problematic source data that may cause ETL failure or data loss or corruption in the data warehouse. In the event of a failure of an ETL task, diagnostics are run and error handling and automatic correction are performed to enable the load plan to restart and continue.

During ETL, when you run a load plan, the relevant source data is queried to generate a healthcheck report. The health check queries are seed data XML files and can be found at {bi.oracle.home}/biapps/etl/data_files/src_files/BIA_11/metadata/healthcheck_xml The report is downloaded from Configuration Manager and includes any problematic data, a description of detected issues, and actions to resolve health check failures.

Email Notifications

If an email address is configured for ETL email notification during Functional Setup Manager configuration, an email is sent in the following cases:
  • If the health check scenario fails, an email is sent with a summary of the health check results.

  • If the load plan execution fails, an email is sent with a list of failed tasks during that load plan execution.

  • If the load plan execution completes, an email is sent with a list of tasks that are in failed state in any of the load plan instance runs and a summary of auto corrections performed (if any), during the ETL.

Automatic Correction of Data Errors

Some common data errors that can cause ETL tasks to fail are automatically diagnosed and corrected during the ETL, allowing for the load plan to restart and complete.

Data Error Type Automatic Correction
Duplicate rows being inserted into the target table causes a unique constraint violation. One row is inserted and the duplicate rows are rejected.
String values larger than allowed being inserted intovarchar or char columns. All varchar columns are truncated to the precision allowed by each column before insert. Exception: key columns defined in the interface are not truncated.
All other issues, including:
  • Number values larger than allowed being inserted into numeric columns.

  • NULL value being inserted into a NOT NULL column.

  • Invalid data, for example alphanumeric being inserted into a number column, invalid date strings, and so on.

Row is rejected.

For dimension load tasks, instead of rejecting rows, the row is inserted into the target table with the "unspecified" value for all the columns except the key columns (typically INTEGRATION_ID and DATASOURCE_NUM_ID). This is done to ensure that fact rows inserted during ETL which refer to a dimension row will not have a dangling foreign key.

The following Knowledge Modules have been enhanced to support diagnostic features during load plan execution process:

  • IKM BIAPPS Oracle Control Append

  • IKM BIAPPS Oracle Incremental Update

  • IKM BIAPPS Oracle Fact Incremental Update

  •  IKM BIAPPS Oracle Slowly Changing Dimension

  • IKM BIAPPS Oracle Event Queue Delete Append

  • IKM BIAPPS Oracle Fact Event Queue Delete Append

  • IKM BIAPPS CLOUD Oracle Incremental Update

  • CKM BIAPPS Oracle

Package Structure for Error Handling and Automatic Correction

All ODI packages that have a main interface step using one of the diagnostic-enabled IKMs include a loop as shown in the example below. Each interface step in the package that uses one of the above IKMs will have a “ko” flow. At a high level, if there is no failure in the interface step, the scenario execution will complete successfully after executing the interface step. However, if there is a failure in the interface step, the control is transferred to the “ko” flow, which executes “Refresh DIAG_EXEC_NUMBER” and “Evaluate DIAG_EXEC_NUMBER” steps and then re-runs the interface step in diagnostic mode.  The interface step now tries to capture Data Manipulation Language (DML) data errors (if any) in an error table and tries to auto correct them based on a set of predefined rules as described above, for example trimming string lengths in case of a string length overflow error.

If the diagnostic framework is not able to automatically correct the error records, they are rejected in an error table so that the non-error rows can be loaded in the target and the scenario execution can complete successfully.

If the failure was caused by a system error or error in LKM steps, the interface step is more likely to fail again, in which case the control is transferred to “Diagnostic Raise Exception” procedure step and fail the scenario execution.

Enabling and Disabling Diagnostic Features

ETL diagnostics and automatic correction can be turned on or off using two parameters in Configuration Manager, both of which are set to Y by default:

  • DIAG_ERR_LOG_SUPPORTED — This parameter controls whether the ODI IKM code performs any auto corrections for DML data errors or not. If disabled, ODI IKM will not attempt to perform any automatic corrections on data. Upon failure of a task, it will still re-run the interface step in diagnostic mode, but raises an error after attempting to capture the data errors.

  • DIAG_AUTOCORRECT— This parameter controls the ETL diagnostic and auto correction feature. When disabled, failed interface steps are not re-run in diagnostic mode.

To reset these parameters, navigate to the Manage Data Loads Parameters page in Configuration Manager and set them to N. After resetting these parameters your load plan must be regenerated for the change to take effect.