8 Extracting, Transforming, and Loading Data

This chapter describes the steps to run the Extract, Transform, and Load (ETL) process using the Oracle Data Integrator (ODI) Studio and ODI Console.

The following table illustrates some of the terms along with the name of the Load Plan that has been used to refer the different types of ETL in the later sections of this chapter:

Table 8-1 Describing ETL Types

Type of ETL	Description	Name of the Load Plan
Initial ETL	The Initial ETL process involves full load of data from Argus Safety and DLP to Argus Mart. It can be described as the first ETL run that is executed for a fresh setup.	LP_INI_AM
Incremental ETL	The Incremental ETL brings changed case data, from last ETL run till start of current ETL run, from Argus Safety and DLP. The LM/CFG data is reloaded only if any change in record(s) is identified. Dictionary data is always reloaded in case of an Incremental ETL. If a new enterprise is added, the Incremental ETL loads complete data of the new enterprise along with delta data of other enterprises.	LP_INC_AM

The following figure depicts your progress in the complete installation process:

Figure 8-1 Installation Progress: Extracting, Transforming, and Loading Data

This chapter comprises the following sub-sections:

• Managing Initial ETL Process: ODI Studio

• Monitoring Initial ETL Process: ODI Studio

• Managing Initial ETL Process: ODI Console

• Monitoring Initial ETL Process: ODI Console

• Running the Incremental ETL

8.1 Managing Initial ETL Process: ODI Studio

The following are the steps required to manage the ETL process using the Database Integrator Studio:

• Running the ETL

• Stopping the ETL

• Restarting the ETL

• Processing a Failed ETL

8.1.1 Running the ETL

To run the Initial ETL, execute the following steps:

1. Open the ODI Studio, and click Connect to Repository.

   The Oracle Data Integrator Login screen appears.

2. In the Oracle Data Integrator Login screen:

   1. From the Login Name drop-down list, select the ODI Work Repository name.

   2. In the User field, enter the name of the ODI user.

   3. In the Password field, enter the password for the ODI user.

      Enter the SUPERVISOR password as provided while configuring the ODI.

   4. Click OK.

      The Oracle Data Integrator screen appears.

3. Select the Operator tab in the left pane.

4. Expand the Load Plans and Scenarios section.

5. Expand LP_ARGUSMART81, and scroll-down to LP_INI_AM.

   This option in this section represents the load plan for the initial ETL process for Argus Mart.

6. Right-click the LP_INI_AM option.

   A drop-down menu appears.

   Figure 8-2 Executing the Initial ETL

   
   

7. Click Run.

   The Start Load Plan screen appears.

8. In the Start Load Plan screen:

   1. From the Context drop-down list, select CTX_ARGUSMART.

   2. From the Logical Agent drop-down list, select LA_AM.

   3. From the Log Level drop-down list, select the desired log level.

   4. Click OK.

      The Information dialog box appears with the Load Plan Started confirmation message.

9. Click OK.

   To verify the status of the ETL process, navigate to the Load Plan Executions section, and expand the Load Plan folder. The status of the Load Plan appears in Green color, which signifies that the ETL session is in progress.

   Figure 8-3 Status of the Load Plan

   
   

8.1.2 Stopping the ETL

To stop the initial ETL, execute the following steps:

1. In Load Plan Executions section, Load Plan folder, right-click the Load Plan, which you want to stop.

   A drop-down menu appears.

   Figure 8-4 Stopping the Initial ETL

   
   

2. Select Stop Normal.

   The Stop Load Plan dialog box appears.

3. From the Physical Agent drop-down list, select PA_AM.

4. Click OK.

   The execution of the Load Plan is stopped.

   To verify the status of the ETL process, navigating to the Load Plan Executions section and expand the Load Plan folder. The status of the Load Plan appears in Red color with the X symbol, which signifies that the ETL session is not in progress.

   Figure 8-5 Stopped Initial ETL Session

   
   
   
   

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

   
   

8.1.3 Restarting the ETL

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

To restart the Initial ETL, execute the following steps:

1. In Load Plan Executions section, Load Plan folder, right-click the Load Plan, which you want to restart.

   The drop-down menu appears.

   Figure 8-6 Restarting the Initial ETL

   
   

2. Click Restart.

   The Restart Load Plan dialog box appears.

3. From the Physical Agent drop-down list, select PA_AM.

4. From the Log Level drop-down list, select the required log level.

5. Click OK.

   The Information dialog box appears with the Load Plan restarted message.

6. Click OK.

   Another Load Plan is added with the same name as that of the stopped ETL, in the Load Plan folder. However, this instance of the ETL Process appears in Green color, which signifies that the ETL is in progress.

   Figure 8-7 Restarted Load Plan

   
   

   Once the ETL process is complete, the Load Plan is appear in Green color with a check mark.

   Figure 8-8 Completed Load Plan

   
   

8.1.4 Processing a Failed ETL

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 continue 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 Initial ETL

• Restarting the Failed Initial ETL

8.1.4.1 Continuing the Failed Initial ETL

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

See Section 8.1.3, "Restarting the ETL" for the step-by-step procedure to continue the failed Initial ETL from the failed step.

8.1.4.2 Restarting the Failed Initial ETL

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

However, before restarting the ETL you must log on to the Oracle SQL Developer 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 may 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.

  Figure 8-9 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.

  Figure 8-10 Select Statement 2 to Verify Successful Execution

  
  

See the Section 8.1.1, "Running the ETL" section for the step-by-step procedure to restart the failed Initial ETL from the beginning of ETL.

8.2 Monitoring Initial ETL Process: ODI Studio

The following are the steps to monitor the ETL process using the Database Integrator Studio:

• Viewing the Steps of Load Plan

• Monitoring the ETL

• Debugging the Failed ETL

• Monitoring the Restarted ETL (Resume)

8.2.1 Viewing the Steps of Load Plan

Before executing the Initial ETL, you may view the steps of the Load Plan for the Initial and the Incremental ETL.

To view the Load Plan steps, execute the following steps:

1. Open the Oracle Data Integrator Studio, and click Connect To Repository.

2. Log on to the ODI Work Repository using the ODI User credentials.

3. Select the Designer tab, and expand the Load Plans and Scenarios section.

   Figure 8-11 Navigating to the LP_INC_AM Load Plan

   
   

4. Double-click the LP_INC_AM load plan.

5. Select the Steps option in the right pane.

   All the steps of the Load Plan appears.

   Figure 8-12 Viewing Steps of the Load Plan

   
   

Similarly, you may also view the steps for the Incremental Load Plan by navigating to Designer tab > Load Plans and Scenarios section, and double-click LP_INC_AM.

8.2.2 Monitoring the ETL

To monitor the progress of the Initial ETL after executing the LP_INI_AM Load Plan, execute the following steps:

1. Select the Operator tab, in the Load Plan Executions section expand the Load Plan folder.

2. Expand the LP_INI_AM load plan to view the ETL process in progress.

   The status of the Load Plan appears in Green color, which signifies that the process is running properly.

   Figure 8-13 Viewing the Status of the ETL Process

   
   

3. Double-click the ETL session, highlighted in the figure above, and select Steps in the right pane.

   The list of steps for the Load Plan appears along with the steps that have been completed successfully.

   Figure 8-14 Viewing Completed Steps in the ETL Process

   
   

8.2.3 Debugging the Failed ETL

To view the step where the ETL process failed and also the error message related to the ETL process failure, execute the following steps:

1. In the Operator tab > Load Plan Executions section, expand the Load Plan folder to view the current status of the ETL process.

   Figure 8-15 Viewing the Failed ETL Process

   
   

   The status of the Load Plan appears in Red color with the X symbol, which signifies that the ETL session is not in progress.

2. Double-click the Load Plan, and select Steps.

   The list of steps for the Load Plan appears in the right pane. The step because of which the ETL process has failed, is highlighted in Red color with the X symbol.

   Figure 8-16 Viewing the Failed Step for the ETL Process

   
   

   Hover the cursor over the error message to view the complete message.

   Figure 8-17 Viewing the Error Message

   
   

   For more information about the error message, click Execute.

   Figure 8-18 Viewing the Error Message using the Execution Section

   
   

   To view the error message, log on to the Oracle SQL Developer using the Argus ETL user credentials, and execute the queries.

   Figure 8-19 Viewing Error Message using SQL Developer

   
   

   The following are the queries that may can use to view the location of the error:

   1. SELECT * FROM etl_stage_log ORDER BY id DESC;

      If you are not able to view any error message after executing this query, you may execute the query mentioned in point 2.

   2. SELECT * FROM etl_mart_log ORDER BY id DESC;

8.2.4 Monitoring the Restarted ETL (Resume)

To view the status of the process after restarting a stopped ETL process, execute the following steps:

1. In the Operator tab > Load Plan Executions section, expand the Load Plan folder.

   Figure 8-20 Viewing the Restarted ETL Process Status

   
   

   The status of the restarted ETL process appears in Green color, which signifies that the ETL is in progress again.

   To view the status of the remaining steps in the process, in the LP_INI_AM folder, double-click the Load Plan, and selecting Steps.

   Figure 8-21 Viewing the Steps of the Restarted ETL

   
   

8.3 Managing Initial ETL Process: ODI Console

The following are the steps to manage the ETL process using the Database Integrator Console:

• Running the ETL

• Stopping the ETL

• Restarting the ETL

• Processing a Failed ETL

8.3.1 Running the ETL

To run the Initial ETL, execute the following steps:

1. Open the ODI Console.

   The Oracle Data Integrator Console Sign In screen appears.

2. In the Oracle Data Integrator Sign In screen:

   1. From the Repository drop-down list, select the ODI Work Repository name.

   2. In the User Id field, enter the name of the ODI user.

   3. In the Password field, enter the password for the ODI user.

   4. Click Sign In.

      The Oracle Data Integrator Console screen appears.

      Figure 8-22 Oracle Data Integrator Console Screen

      
      

3. Select the Browse tab in the left pane.

4. Navigate to Runtime > Scenarios/Load Plans > Folders > LP_ARGUSMART81 > LP_INI_AM.

   Figure 8-23 Scenarios/Load Plans

   
   

   The LP_INI_AM option in this section represents the load plan for the initial ETL process for Argus Mart.

5. Click Execute.

   Figure 8-24 Executing the Initial ETL

   
   

   The Execute Load Plan screen appears.

6. In the Execute Load Plan screen:

   1. From the Logical Agent drop-down list, select LA_AM.

   2. From the Context drop-down list, select CTX_ARGUSMART.

   3. From the Log Level drop-down list, select the desired log level.

   4. Click Execute Load Plan.

      The Information dialog box appears with confirmation message — Load Plan Execution submitted successfully.

7. Click OK.

   To verify the status of the ETL process, in the Sessions/Load Plan Executions section, expand the Load Plan Executions folder. The status of the Load Plan appears in green color with tilted S, which signifies that the ETL session is in progress.

   Figure 8-25 Status of the Load Plan

   
   

8.3.2 Stopping the ETL

To stop the initial ETL, execute the following steps:

1. In the Sessions/Load Plan Executions section > Load Plan Executions folder, select the Load Plan, which you want to stop, and click Stop.

   Figure 8-26 Stopping the Initial ETL

   
   

   The Stop Load Plan Execution dialog box appears.

2. From the Stop Type drop-down list, select Normal.

3. From the Physical Agent drop-down list, select OracleDIAgent.

4. Click Stop.

   The Information dialog box appears with confirmation message — Load Plan was Stopped Successfully.

5. Click OK.

   To verify the status of the ETL process, navigate to Sessions/Load Plan Executions section > Load Plan Executions folder. The status of the Load Plan appears in red color with the X symbol, which signifies that the ETL session is not in progress.

   Figure 8-27 Stopped Initial ETL Session

   
   
   
   

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

   
   

8.3.3 Restarting the ETL

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

To restart the Initial ETL, execute the following steps:

1. In the Sessions/Load Plan Executions section > Load Plan Executions folder, select the Load Plan, which you want to restart, and click Restart.

   Figure 8-28 Restarting the Initial ETL

   
   

   The Restart Load Plan Execution dialog box appears.

2. From the Physical Agent drop-down list, select OracleDIAgent.

3. From the Log Level drop-down list, select the required log level.

4. Click Restart.

   The Information dialog box appears with confirmation message— Load Plan restarted successfully.

5. Click OK.

   Another Load Plan is added with the same name as that of the stopped ETL, in the Sessions/Load Plan Executions section > Load Plan Executions folder. However, this instance of the Load plan appears in green color, which signifies that the ETL is in progress.

8.3.4 Processing a Failed ETL

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 continue 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 Initial ETL

• Restarting the Failed Initial ETL

8.3.4.1 Continuing the Failed Initial ETL

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

See the Section 8.3.3, "Restarting the ETL" for the step-by-step procedure to continue the failed Initial ETL from the failed step.

8.3.4.2 Restarting the Failed Initial ETL

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

However, you need to execute certain steps before restarting the Failed Initial ETL, refer to the Section 8.1.4.2, "Restarting the Failed Initial ETL" for the complete details.

See the Section 8.3.1, "Running the ETL" for the step-by-step procedure to restart the failed Initial ETL from the beginning of ETL.

8.4 Monitoring Initial ETL Process: ODI Console

The following are the steps to monitor the ETL process using the Database Integrator Console:

• Viewing the Steps of Load Plan

• Monitoring the ETL

• Debugging the Failed ETL

• Monitoring the Restarted ETL (Resume)

8.4.1 Viewing the Steps of Load Plan

Before executing the Initial ETL, you may view the steps of the Load Plan for the Initial and the Incremental ETL.

To view the steps of Load Plan, execute the following steps:

1. Log on the Oracle Data Integrator Console, and select the Management tab.

2. Navigate to Runtime > Scenarios/Load Plans > Folders > LP_ARGUSMART81.

3. Right-click LP_INI_AM (Load Plan for Initial ETL) or LP_INC_AM (Load Plan for Incremental ETL), and select View.

   Figure 8-29 Navigating to the Load Plans

   
   

   The steps for the Load Plan appears in the Relationships section in the right pane.

   Figure 8-30 Viewing the Steps of the Load Plan

   
   

8.4.2 Monitoring the ETL

To monitor the progress of the initial ETL after executing the LP_INI_AM Load Plan, execute the following steps:

1. Select the Browse tab, and navigate to Runtime > Sessions/Load Plan Executions > Load Plan Executions.

   A tilted s in green color appears, which signifies that the ETL process is running properly.

   Figure 8-31 Initial ETL in Progress

   
   

2. In the Relationships section, right-click the session, and select View, to view the list of all the steps along with the steps that have been completed.

   Figure 8-32 Viewing Completed Steps in the ETL Process

   
   

8.4.3 Debugging the Failed ETL

To view the step where the ETL process failed and also the error message related to the ETL process failure, execute the following steps:

1. In the Browse tab, navigate to Runtime > Sessions/Load Plan Executions > Load Plan Executions.

   Figure 8-33 Viewing the Failed ETL Process

   
   

   The status of the Load Plan appears in red color with the X symbol, which signifies that the ETL session is not in progress.

2. Double-click the Load Plan.

   The list of steps for the Load Plan appears in the Relationship section. The step because of which the ETL process has failed, is highlighted in red color with the X symbol.

   Figure 8-34 Viewing the Failed Step for the ETL Process

   
   

   Hover the error message to view the complete message.

   Alternatively, navigate to Runtime > Sessions/Load Plan Executions > Sessions, right-click the stopped session, highlighted in Red color with the ! symbol, and select View.

   Figure 8-35 Viewing the Stopped Session

   
   

   The error details appears in the right pane.

   Figure 8-36 Viewing the Error Details

   
   

   Or, to view the error message, log on to the Oracle SQL Developer using the Argus ETL user credentials, and execute the queries:

   Figure 8-37 Viewing Error Message using SQL Developer

   
   

   The following are the queries that you can use to view the location of the error:

   1. SELECT * FROM etl_stage_log ORDER BY id DESC;

      If you are not able to view any error message after executing this query, you can execute the query mentioned in point 2.

   2. SELECT * FROM etl_mart_log ORDER BY id DESC;

8.4.4 Monitoring the Restarted ETL (Resume)

Once you have restarted a stopped ETL process, you can view the status of the process using the following steps:

1. In the Browse tab, navigate to Runtime > Sessions/Load Plan Executions > Load Plan Executions section.

   Figure 8-38 Viewing the Restarted ETL Process Status

   
   

   The status of the restarted ETL process appears in green color, which signifies that the ETL is in progress again.

   To view the status of the steps completed before restarting the ETL, and the steps after restarting the ETL, double-clicking the session in progress.

   The steps appears in the Relationship section in the right pane.

   Figure 8-39 Viewing the Steps of the Restarted ETL

   
   

8.5 Running the Incremental ETL

For step-by-step information related to the Incremental ETL tasks such as Running, Stopping, and Restarting the Incremental ETL through:

• ODI Studio, see Section 13.3, "Managing Incremental ETL Process"

• ODI Console, see Section 14.3, "Managing Incremental ETL Process"