This topic discusses how you can identify and manage errors during intra-ETL execution.
Monitoring the Execution of the Intra-ETL Process
Three ocdm_sys
schema control tables, DWC_INTRA_ETL_PROCESS
, DWC_INTRA_ETL_ACTIVITY
, and DWC_OLAP_ACTIVITY
monitor the execution of the intra-ETL process. 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_PROCESS
table with a monotonically increasing system generated unique process key, SYSDATE
as process start time, RUNNING as the process status, and an input date range in the FROM_DATE_ETL
and TO_DATE_ETL
columns.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 SYSDATE
.ACTIVITY_STATUS
, the value of RUNNING
.DWC_INTRA_ETL_ACTIVITY
table 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 ERROR_DTL
column.DWC_INTRA_ETL_ PROCESS
table 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 'COMPLETED-ERROR'
.DWC_OLAP_ACTIVITY
table with CUBENAME
as cube name, status as 'RUNNING'
, and LOAD_START_DT
as 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 'COMPLETE-ERROR'
and updates LOAD_END_DT
column to SYSDATE
. In case of 'COMPLETED-ERROR'
cubes, it also updates ERROR_DTL
column 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_INTRA_ETL_PROCESS
, DWC_INTRA_ETL_ACTIVITY
, and 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.
Related Topics
To recover an intra-ETL process
DWC_INTRA_ETL_ACTIVITY
table.DWC_OLAP_ACTIVITY
table.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.