12 Incremental ETL: ODI Studio

This chapter describes the steps required to administer the ETL process using the Oracle Data Integrator Studio (ODI Studio).

This chapter comprises the following sub-sections:

• Scheduling Incremental ETL

• Monitoring Incremental ETL Process

• Managing Incremental ETL Process

12.1 Scheduling Incremental ETL

The AM.zip file contains pre-configured ETL scheduling in Inactive mode.

The Incremental ETL is used to load the delta data. You can execute an Incremental ETL either by executing Load Plan LP_INC_AM, or scheduling an ETL to run at the configured time interval.

To schedule a Load Plan, execute the following steps:

1. In the Designer tab, navigate to Load Plans and Scenarios section and expand LP_INC_AM (Load Plan for Incremental ETL).

2. Right-click Scheduling and select New Scheduling, as depicted in the following figure:

   Figure 12-1 Scheduling ETL

   
   

   This displays the Load Plan Scheduling dialog box, as depicted in the following figure:

   Figure 12-2 Scheduling the Load Plan Dialog Box

   
   

   You can set options given in the Status and Execution sections, according to the requirements to schedule the Load Plan.

12.2 Monitoring Incremental ETL Process

The process of monitoring the Incremental ETL using the Oracle Database Integrator Studio is same as the process of monitoring the Initial ETL.

For step-by-step information related to monitoring the Incremental ETL process using the ODI Studio, see Section 8.2, "Monitoring Initial ETL Process: ODI Studio."

12.3 Managing Incremental ETL Process

This section describes the steps required to manage the Incremental ETL process using the Oracle Database Integrator Studio.

This section comprises the following sub-sections:

• Running the ETL

• Stopping the ETL

• Restarting the ETL

• Processing a Stopped or a Failed ETL

12.3.1 Running the ETL

To run the Incremental ETL, execute the following steps:

1. Open the Oracle Data Integrator Studio and click Connect to Repository. This displays the Oracle Data Integrator Login window, as depicted in the following figure:

   Figure 12-3 Oracle Data Integrator Login Window

   
   

2. In the Oracle Data Integrator Login window:

   1. Select the ODI Work Repository name from the Login Name drop-down list.

   2. Enter the name of the ODI user in the User field.

   3. Enter the password for the ODI user in the Password field.

   4. Click OK. This displays the Oracle Data Integrator Screen.

3. Select the Operator tab in the left pane.

4. Expand the Load Plans and Scenarios section, as highlighted in the following figure:

   Figure 12-4 Load Plans and Scenarios

   
   

   The LP_INC_AM option in this section represents the load plan for the Incremental ETL process for Argus Mart.

5. Right-click the LP_INC_AM option. This displays a menu, as shown in the following figure:

   Figure 12-5 Executing the Incremental ETL

   
   

6. Click Execute. This displays the Start Load Plan window, as shown in the following figure:

   Figure 12-6 Start Load Plan Window

   
   

7. In the Start Load Plan window:

   1. Select CTX_ARGUSMART from the Context drop-down list.

   2. Select LA_AM from the Logical Agent drop-down list.

   3. Select the desired log level from the Log Level drop-down list.

   4. Click OK. This displays the Information dialog box with the Load Plan Started confirmation message, as shown in the following figure:

      Figure 12-7 Load Plan Started Confirmation Message

      
      

8. Click OK.

   You can verify the status of the ETL process by navigating to the Load Plan Executions section and expanding the Load Plan folder. You can view the status of the Load Plan in Green color with tilted s, which signifies that the ETL session is in progress, as highlighted in the following figure:

   Figure 12-8 Status of the Load Plan

   
   

12.3.2 Stopping the ETL

To stop the Incremental ETL, execute the following steps:

1. Right-click the Load Plan, which you want to stop, in the Load Plan folder of the Load Plan Executions section. This displays a menu, as shown in the following figure:

   Figure 12-9 Stopping the Incremental ETL

   
   

2. Select Stop Normal. This displays the Stop Load Plan dialog box, as depicted in the following figure:

   Figure 12-10 Selecting the Physical Agent

   
   

3. Select PA_AM from the Physical Agent drop-down list.

4. Click OK. This stops the execution of the Load Plan.

   You can verify the status of the ETL process by navigating to the Load Plan Executions section and expanding the Load Plan folder. You can view the status of the Load Plan in Red color with the X symbol, which signifies that the ETL session is not in progress, as highlighted in the following figure:

   Figure 12-11 Stopped Incremental ETL Session

   
   
   
   

   Note: You must verify in Argus Mart database if the ETL session has been successfully ended after this step.

   
   

12.3.3 Restarting the ETL

Restarting the Incremental ETL process enables you to start the ETL process from the last execution step where it was stopped or failed.

To restart the Incremental ETL, execute the following steps:

1. Right-click the Load Plan, which you want to restart, in the Load Plan folder of the Load Plan Executions section. This displays a menu, as shown in the following figure:

   Figure 12-12 Restarting the Incremental ETL

   
   

2. Click Restart. This displays the Restart Load Plan dialog box, as shown in the following figure:

   Figure 12-13 Restart Load Plan Dialog Box

   
   

3. Select PA_AM from the Physical Agent drop-down list.

4. Select the required log level from the Log Level drop-down list.

5. Click OK. This displays the Information dialog box with the Load Plan restarted message, as depicted in the following figure:

   Figure 12-14 Loan Plan restarted Confirmation Message

   
   

6. Click OK.

   This adds another Load Plan, with the same name as that of the stopped ETL, in the Load Plan folder of the Load Plan Executions section. However, this instance of the ETL Process is in Green color with a tilted S, which signifies that the ETL is in progress, as highlighted in the following figure:

   Figure 12-15 Restarted Load Plan

   
   

   Once the ETL process is complete, the Load Plan is displayed in Green color with a completed symbol, as highlighted in the following figure:

   Figure 12-16 Completed Load Plan

   
   

12.3.4 Processing a Stopped or a Failed ETL

The complete ETL process is divided into two major phases: Staging and Mart. The Mart phase starts only when the Staging phase is complete.

If an ETL process fails, you have the option of continuing the process from the failed step or executing it again from the beginning of ETL.

This section explains the steps to resume a failed ETL from the failed step and to execute it again from the beginning of ETL.

This section comprises the following sub-sections:

• Continuing the Failed Incremental ETL

• Restarting the Failed Incremental ETL

12.3.4.1 Continuing the Failed Incremental ETL

If the last execution step of the failed or stopped ETL belongs to the Staging phase, the ETL resumes from the failed or stopped point, as shown in the following figure:

Figure 12-17 Staging Phase: Incremental ETL Resumes from Failed or Stopped Point

If the last execution step of the failed or stopped ETL belongs to the Mart phase, the ETL resumes from the first step of the Mart phase and not from the failed or stopped point, as depicted in the following figure:

Figure 12-18 Mart Phase: Incremental ETL Resumes from the First Step of Mart

The process to continue the failed Incremental ETL from the failed step is exactly the same as that of the process of restarting the Incremental ETL after stopping it.

See the Restarting the ETL section for the step-by-step procedure to continue the failed Incremental ETL from the failed step.

12.3.4.2 Restarting the Failed Incremental ETL

The process to restart the failed Incremental ETL from the beginning is exactly the same as that of the process of running the Incremental ETL.

However, before restarting the ETL, you must log on to the Oracle SQL Developer or SQLPlus (or SQL Prompt) using the Argus ETL User credentials and execute the following statements:

EXEC pkg_sm_stage_util.p_set_cmn_profile_value ('DATABASE', 'ODI_ETL_STATUS', '0');EXEC pkg_sm_stage_util.p_set_cmn_profile_value ('DATABASE', 'ETL_SM_ITERATION_NUMBER', NULL);

COMMIT;

To verify the successful execution of these statements, you can execute the following Select statements:

• SELECT * FROM rm_cmn_profile_global WHERE KEY = 'ODI_ETL_STATUS';

  The entry for the Value column must be 0 after executing this statement, as depicted in the following figure:

  Figure 12-19 Select Statement 1 to Verify Successful Execution

  
  

• SELECT * FROM rm_cmn_profile_global WHERE KEY = 'ETL_SM_ITERATION_NUMBER';

  The entry for the Value column must be blank after executing this statement, as depicted in the following figure:

  Figure 12-20 Select Statement 2 to Verify Successful Execution

  
  

See the Running the ETL section for the step-by-step procedure to restart the failed Incremental ETL from the beginning of ETL.