About Diagnostics Health Check 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 might 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 health check 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. You can download a health check report from Configuration Manager and the health check report 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 into varchar 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.

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 into `varchar` 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 values being inserted into a `NOT NULL` column. Invalid data, for example alphanumeric values 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, don’t have a dangling foreign key.

All other issues, including:

Number values larger than allowed being inserted into numeric columns.
NULL values being inserted into a NOT NULL column.
Invalid data, for example alphanumeric values 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, don’t 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 here. Each interface step in the package that uses one of the IKMs has a ko flow. At a high level, if there is no failure in the interface step, the scenario execution completes 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, for example trimming string lengths in case of a string length overflow error.

Description of GUID-E8278035-0201-4BDE-87D1-7B012B4A072F-default.gif follows

Description of the illustration GUID-E8278035-0201-4BDE-87D1-7B012B4A072F-default.gif

If the diagnostic framework can’t 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 does not attempt to perform any automatic corrections on data. Upon failure of a task, it still re-runs 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. Your load plans are regenerated after resetting these parameters.