Running Incremental ETL Jobs

The load script that you run during Operational Reporting deployment performs a full ETL to load all PPM data into the Operational Reporting database schema. Incremental ETL jobs are scheduled to run automatically every 24 hours thereafter. These incremental ETL jobs cover the day-to-day updates for the PPM data tables.

The incremental ETL job that runs automatically every 24 hours is named PPM_ETL_BATCH_JOB. You can use an Oracle command to reschedule or change the frequency of the PPM_ETL_BATCH_JOB run. (For information on how to reschedule the PPM_ETL_BATCH_JOB, see the Oracle Database Online Documentation.) This section contains instructions on how to run incremental ETL jobs manually.

Checking ETL Job Progress

To check the job progress, you can query the RPT_EVENT_LOG_DETAIL table, as follows:

Example: select event_time, lead(event_time,1) over
(order by event_log_id desc), round((event_time - lead(event_
time,1) over (order by event_log_id desc))*24*60 ,2) durations,
t1.*
from rpt_event_log_detail t1
order by event_log_id desc;

where the value of event_time must contain both date value and time value.

If the value of event_time does not contain date value or time value, configure the settings in Oracle client tool as follows:

  1. Open Oracle SQL Developer.

  2. Click Tools > Preferences.

  3. In the left pane, expand Database (if it is not already expanded).

  4. Under Database, click NLS.

  5. Set the date format to: YYYY-MM-DD HH24:MI:SS

  6. Click OK.

To view the status of an incremental ETL job, you can query the job control tables (RPT_ETL_JOB and RPT_EVENT_LOG_DETAIL tables).

Verifying Successful Incremental ETL Jobs

To determine whether the last incremental ETL job run completed successfully, run the following:

Example: select event_time, lead(event_time,1) over
(order by event_log_id desc), round((event_time - lead(event_
time,1) over (order by event_log_id desc))*24*60 ,2) durations,
t1.*
from rpt_event_log_detail t1
order by event_log_id desc;
SELECT * FROM rpt_etl_job ORDER BY etl_job_id desc;

where the value of event_time must contain both date value and time value.

If the value of event_time does not contain date value or time value, configure the settings in Oracle client tool. For detailed steps, see If the value of event_time does not contain date value or time value, configure the settings in Oracle client tool as follows: .

Note: We recommend that you delete the contents of the rpt_event_log_detail and
rpt_etl_job order tables at least once a month to prevent them from becoming too large. You must delete the contents manually.