4 ETL Extensibility

Custom Routines are the configured procedures that are executed during Argus Insight Incremental ETL to perform custom actions.

Argus Insight supports the following custom routines:

PRE_INCREMENTAL_ETL_TASK - Executes the configured routine during incremental ETL before population of Argus Insight staging tables.
POST_INCREMENTAL_ETL_TASK - Executes the configured routine during incremental ETL after population of Argus Insight mart tables.

These custom routines are useful in the following scenarios:

Populating custom tables or new columns based on the business needs.
Analyzing tables with huge data.
Triggering an event based on ETL completion for the use with other custom products.

4.1 Viewing Argus Insight Custom Routines

Note:

These routines are Global-level switches, visible in Argus Insight Administration Tools.

In a multi-tenant environment, these switches are visible only when you login through Default enterprise.

The following are the steps to view the custom routines:

Log in to the Argus Insight Application as Admin user.

Alternatively, in a multi-tenant environment log in to the Default enterprise.
From the menu bar, click Tools.
Click the List Maintenance tab to open the List Maintenance Items.
Select Profile Switches from the List Maintenance Items.
From the Attributes section, select CUSTOM ROUTINE BEFORE INCREMENTAL ETL, and click Modify to see the Value for this routine.
Similarly, select CUSTOM ROUTINE AFTER INCREMENTAL ETL, and click Modify to see the Value for this routine.

4.2 Executing Argus Insight Custom Routines

The ETL Routines can be executed at two levels:

Before starting the incremental ETL.
After executing the incremental ETL.

The following are the steps to execute the custom routine:

Select the custom routine (PRE or POST), and enter an Oracle stored procedure name in the Value text box relevant to that custom routine. This Routine searches the database object that matches the procedure name in the schema APR_MART during Incremental ETL execution.

Note:
To view or modify the Value of a custom routine, refer to the Section 4.1, "Viewing Argus Insight Custom Routines".
If the procedure is found, the application executes the ETL.
1. If the custom routine executes without any errors, then the application moves to the next step of the Incremental ETL process.
2. If the Custom routine executes with errors, then the application logs the error(s) in the table MART_DATA_INSERT_LOG and exits.
Note:
- ETL does not process any data before execution of the pre-incremental ETL custom routine.
- ETL commits the data before the execution of the post-incremental ETL routine.
- You should manually execute the post-incremental ETL routine, if it fails as it cannot be resumed.
To track the error when a custom routine fails, refer to the example as explained below:
1. Connect to the schema APR_MART, and create:
  
  A table PRE_POST_ETL_CHK with one column col1 varchar2(100).
  
  A procedure P_PRE_ETL to insert a row in the table with less than 100 characters.
  
  A procedure P_POST_ETL to insert a row with more than 100 characters.
2. Update the Values of the custom routines.
  
  For key PRE_INCREMENTAL_ETL_TASK, set the value to P_PRE_ETL.
  
  For key POST_INCREMENTAL_ETL_TASK, set the value to P_POST_ETL.
  
  The ETL will show an error while executing the post-incremental custom procedure as we try to insert large value than the column's length.
  
  Note:
  To view or modify the Value of a custom routine, refer to the Section 4.1, "Viewing Argus Insight Custom Routines".
3. Run the incremental ETL.
4. Since P_POST_ETL procedure fails to insert a row, error occurs at the end of the ETL execution.
5. To verify the error, view the table MART_DATA_INSERT_LOG.
  
  The actual error text that is displayed in the column ORA_ERR_DESC is as below:
```
"ORA-20010: ORA-12899: value too large for column "APR_MART"."PRE_POST_ETL_CHK"."COL1" (actual: 105, maximum: 100)
ORA-06512: at "APR_MART.P_POST_ETL", line 3
ORA-06512: at line 1
ORA-06512: at "APR_MART.PKG_PWR_UTIL", line 3306
 -- ERROR while processing p_pre_post_incr_etl_tasks at 25-jun-2013 12:
ORA-06512: at "APR_MART.PKG_AIR_STOM", line 313
ORA-06512: at "APR_MART.PKG_PWR_UTIL", line 3323
ORA-06512: at "APR_MART.PKG_DBMS_JOB", line 1659
```
6. To ensure that a row is inserted from the custom routine before incremental ETL - P_PRE_ETL, view table PRE_POST_ETL_CHK.
If the procedure (or database object) is not found, then the application logs the error(s), and fails the ETL.

In this case, the ETL may be executed if you have explicitly created an exception-handling for such cases to absorb any exceptions, and go to the next step in the ETL process.

Optionally, to resolve this issue, create a procedure of that name, provide an existing procedure name, or remove the configuration.
Oracle Recommends:
- You should not modify the existing names of the database objects of Argus Insight, though additional objects can be created as part of customization as per your business needs.
- The changes related to custom routines should be tested in a test environment before implementing in the production environment.