This topic discusses how you can identify and manage errors during intra-ETL execution.
Monitoring the Execution of the Intra-ETL Process
ocdm_sys schema control tables,
DWC_OLAP_ACTIVITY monitor the execution of the intra-ETL process. These tables are documented in Oracle Communications Data Model Reference. You can access these three tables from ocdm_user user.
Each normal run (as opposed to an error-recovery run) of a separate intra-ETL execution performs the following steps:
DWC_INTRA_ETL_PROCESStable with a monotonically increasing system generated unique process key,
SYSDATEas process start time, RUNNING as the process status, and an input date range in the
DWC_INTRA_ETL_ACTIVITY, with values for:
ACTIVITY_KEY, a system generated unique activity key.
PROCESS_KEY, the process key value corresponding to the intra-ETL process.
ACTIVITY_NAME, an individual program name.
ACTIVITY_DESC, a suitable activity description.
ACTIVITY_START_TIME, the value of
ACTIVITY_STATUS, the value of
DWC_INTRA_ETL_ACTIVITYtable for the activity end time and activity status after the completion of each individual ETL program (either successfully or with errors). For successful completion of the activity, the procedure updates the status as
'COMPLETED-SUCCESS'. When an error occurs, the procedure updates the activity status as
'COMPLETED-ERROR', and also updates the corresponding error detail in the
DWC_INTRA_ETL_ PROCESStable for the process end time and status, after the completion of all individual intra-ETL programs. When all the individual programs succeed, the procedure updates the status to
'COMPLETED-SUCCESS'; otherwise it updates the status to
CUBENAMEas cube name, status as
LOAD_START_DTas SYSDATE for each cube. It updates the record upon the completion of cube loading. It updates STATUS column to
'COMPLETED-SUCCESS'if cube loading is successful, otherwise
SYSDATE. In case of
'COMPLETED-ERROR'cubes, it also updates
ERROR_DTLcolumn with error details.
You can monitor the execution state of the intra-ETL, including current process progress, time taken by individual programs, or the complete process, by viewing the contents of the
DWC_OLAP_ACTIVITY tables. In
DWC_INTRA_ETL_ACTIVITY table, see the records of currently running process. Monitoring can be done both during and after the execution of the intra-ETL procedure.
To recover an intra-ETL process
The intra-ETL workflow process identifies whether it is a normal run or recovery run by referring the
DWC_INTRA_ETL_ACTIVITY table. During a recovery run, the intra-ETL workflow executes only the necessary programs. For example, for a derived population error as a part of the previous run, this recovery run executes the individual derived population programs which produced errors in the previous run. After their successful completion, the run refreshes aggregate materialized views in the appropriate order.
In this way, the intra-ETL error recovery is almost transparent, without involving the data warehouse or ETL administrator. The administrator must only correct the causes of the errors and re-invoke the intra-ETL process. The intra-ETL process identifies and executes the programs that generated errors.