X

The Oracle BI Application Blogs provides the latest and tips and tricks on Oracle BI Applications Product (OBIA)

Monitoring BIAPPS ETL via Oracle Enterprise Manager

Monitor ETL Status

Monitor ETL Duration Phase-wise

Sample Alerts

Background

BIAPPS ETL or Load Plan can be monitored from ODI clients or BIACM. However what if you wanted to automate the monitoring? Is there a way to detect ETL is running? Is there a way to be notified if a particular ETL phase (like SDE/SIL/PLP) is taking longer than expected? This blog shows how it is possible using the latest BIAPPS Version 11.1.1.10.2 and  Oracle Database Enterprise Manager (OEM),that comes with the database.

There is a new table starting BIAPPS 10.2 release called W_ETL_STATE. Following columns in that table are relevant for this blog.

 Column Name

 Purpose

PROCESS_TYPE

Will
contain ETL for ETL runs

STATE_CODE

Represents the current state of the
process. Contains one of the following

STARTED, FAILED,COMPLETED

SUCC_PHASE_CODE

Represents the last successfully
completed phase of the process.

E.g. if SUCC_PHASE_CODE
='SDS_COMPLETED' and STATE_CODE='FAILED' it implies that the ETL failed after
the SDS got successfully loaded

 

This table is automatically populated by the BIAPPS Load Plan (assuming it is a Generated Load Plan and not a custom built one). We can write a query on this table and use it to achieve the desired monitoring.

Monitor ETL Status:

User Defined Metric for ETL
Status: Warning when ETL is running and Critical when ETL fails

Follow the below steps to create a user defined metric in Oracle Database Enterprise Manager(OEM).

1) Open the User Defined Metric Screen from OEM (see screenshot below).

2) In the screen that opens, choose create to create a new metric and then populate the values as shown below

 

The SQL to be entered is as below

SELECT 'ETL_STATUS',
CASE WHEN STATE_CODE='STARTED' THEN CURRENT_ETL_PHASE
    ELSE STATE_CODE
END ETL_STATUS
FROM (SELECT STATE_CODE,
    CASE WHEN SUCC_PHASE_CODE='INIT_COMPLETED' THEN 'RUNNING'
         WHEN SUCC_PHASE_CODE='SDS_COMPLETED' THEN 'RUNNING'
         WHEN SUCC_PHASE_CODE='SDE_COMPLETED' THEN 'RUNNING'
         WHEN SUCC_PHASE_CODE='SIL_COMPLETED' THEN 'RUNNING'
         WHEN SUCC_PHASE_CODE='PLP_COMPLETED' THEN 'LOAD_DONE'
END AS CURRENT_ETL_PHASE
FROM W_ETL_STATE);

Define the threshold as below. Setup a schedule as per your preference.

Once you have defined the metric and the schedule is active, it will show up in your home screen, whenever ETL is running or FAILED.

Monitor ETL Duration Phase-wise

User Defined Metric for ETL Duration Phase-wise: Warning when any phase takes over x hours and Critical when ETL phase takes over y hours

Follow the below steps to create the required user defined metric in Oracle Database Enterprise Manager(OEM).

1) Open the User Defined Metric Screen from OEM (see screenshot below).

2) In the screen that opens, choose create to create a new metric and then populate the values as shown below

The SQL to be entered is as below

SELECT 'SDS_DURATION',
   CASE WHEN SUCC_PHASE_CODE='INIT_COMPLETED' THEN (SYSDATE-W_UPDATE_DT)*24
   ELSE -1 END AS SDS_DURATION_HOURS  FROM W_ETL_STATE
UNION ALL
SELECT 'SDE_DURATION',
   CASE WHEN SUCC_PHASE_CODE='SDS_COMPLETED' THEN (SYSDATE-W_UPDATE_DT)*24
    ELSE -1 END AS SDE_DURATION_HOURS FROM W_ETL_STATE
UNION ALL
SELECT 'SIL_DURATION',
   CASE WHEN SUCC_PHASE_CODE='SDE_COMPLETED' THEN (SYSDATE-W_UPDATE_DT)*24
    ELSE -1 END AS SIL_DURATION_HOURS FROM W_ETL_STATE
UNION ALL
SELECT 'PLP_DURATION',
   CASE WHEN SUCC_PHASE_CODE='SIL_COMPLETED' THEN (SYSDATE-W_UPDATE_DT)*24
    ELSE -1 END AS PLP_DURATION_HOURS FROM W_ETL_STATE
;

 

Define the threshold as required. Setup a schedule as per your preference. Once you have defined it, it will show up in your home screen. Whenever any ETL phase takes over 2 hours it will throw a warning and when it exceeds 3 hours it will throw an exception. Note: The 2 hours and 3 hours shown in this blog, is only an example.  You can change them as per your requirement. You can also setup individual alerts for each phase, each having a different value for warning and exception. In this  example, each of the phase is expected to run under 2 hours.

Sample Alerts

ETL failed and SIL duration is over 188 hours. So both raised as critical.

ETL running (so warning) and SDE Duration is over 189 hours(so critical).

Note: Once a warning/alert is raised, it is not raised again until the value changes.

You can also see the history of alerts from Oracle Database EM.

 

This opens up a screen as below which shows the history.

Similar screen with history can be seen for the ETL_STATUS metric

Note: BIAPPS 10.1 customers can apply the Patch 25196751 available on MOS site and then follow the instructions in this blog to achieve the same functionality. No patch required if you are on BIAPPS 10.2 as this functionality is available out of the box. 

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.