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 7-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:
Open the ODI Studio, and click Connect to Repository.
The Oracle Data Integrator Login screen appears.
In the Oracle Data Integrator Login screen:
From the Login Name drop-down list, select the ODI Work Repository name.
In the User field, enter the name of the ODI user.
In the Password field, enter the password for the ODI user.
Enter the SUPERVISOR password as provided while configuring the ODI.
Click OK.
The Oracle Data Integrator screen appears.
Select the Operator tab in the left pane.
Expand the Load Plans and Scenarios section.
Expand LP_ARGUSMART811, and scroll-down to LP_INI_AM.
This option in this section represents the load plan for the initial ETL process for Argus Mart.
Right-click the LP_INI_AM option.
A drop-down menu appears.
Click Run.
The Start Load Plan screen appears.
In the Start Load Plan screen:
From the Context drop-down list, select CTX_ARGUSMART.
From the Logical Agent drop-down list, select LA_AM.
From the Log Level drop-down list, select the desired log level.
Click OK.
The Information dialog box appears with the Load Plan Started confirmation message.
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.
In Load Plan Executions section, Load Plan folder, right-click the Load Plan, which you want to stop.
A drop-down menu appears.
Select Stop Normal.
The Stop Load Plan dialog box appears.
From the Physical Agent drop-down list, select PA_AM.
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.
Note:
You must verify in Argus Mart database if the ETL session has been successfully ended after this step.Restarting the Initial ETL process enables you to start the ETL process from the last execution step where it was stopped or failed.
In Load Plan Executions section, Load Plan folder, right-click the Load Plan, which you want to restart.
The drop-down menu appears.
Click Restart.
The Restart Load Plan dialog box appears.
From the Physical Agent drop-down list, select PA_AM.
From the Log Level drop-down list, select the required log level.
Click OK.
The Information dialog box appears with the Load Plan restarted message.
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.
Once the ETL process is complete, the Load Plan is appear in Green color with a check mark.
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.
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 7.1.3, "Restarting the ETL" for the step-by-step procedure to continue the failed Initial ETL from the failed step.
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.
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.
See the Section 7.1.1, "Running the ETL" section for the step-by-step procedure to restart the failed Initial ETL from the beginning of ETL.
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:
Open the Oracle Data Integrator Studio, and click Connect To Repository.
Log on to the ODI Work Repository using the ODI User credentials.
Select the Designer tab, and expand the Load Plans and Scenarios section.
Double-click the LP_INC_AM load plan.
Select the Steps option in the right pane.
All the steps of the Load Plan appears.
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.
To monitor the progress of the Initial ETL after executing the LP_INI_AM Load Plan, execute the following steps:
Select the Operator tab, in the Load Plan Executions section expand the Load Plan folder.
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.
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.
To view the step where the ETL process failed and also the error message related to the ETL process failure, execute the following steps:
In the Operator tab > Load Plan Executions section, expand the Load Plan folder to view the current status of the 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.
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.
Hover the cursor over the error message to view the complete message.
For more information about the error message, click Execute.
To view the error message, log on to the Oracle SQL Developer using the Argus ETL user credentials, and execute the queries.
The following are the queries that may can use to view the location of the error:
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.
SELECT * FROM etl_mart_log ORDER BY id DESC;
To view the status of the process after restarting a stopped ETL process, execute the following steps:
In the Operator tab > Load Plan Executions section, expand the Load Plan folder.
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.
Open the ODI Console.
The Oracle Data Integrator Console Sign In screen appears.
In the Oracle Data Integrator Sign In screen:
From the Repository drop-down list, select the ODI Work Repository name.
In the User Id field, enter the name of the ODI user.
In the Password field, enter the password for the ODI user.
Click Sign In.
The Oracle Data Integrator Console screen appears.
Select the Browse tab in the left pane.
Navigate to Runtime > Scenarios/Load Plans > Folders > LP_ARGUSMART811 > LP_INI_AM.
The LP_INI_AM option in this section represents the load plan for the initial ETL process for Argus Mart.
Click Execute.
The Execute Load Plan screen appears.
In the Execute Load Plan screen:
From the Logical Agent drop-down list, select LA_AM.
From the Context drop-down list, select CTX_ARGUSMART.
From the Log Level drop-down list, select the desired log level.
Click Execute Load Plan.
The Information dialog box appears with confirmation message — Load Plan Execution submitted successfully.
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.
In the Sessions/Load Plan Executions section > Load Plan Executions folder, select the Load Plan, which you want to stop, and click Stop.
The Stop Load Plan Execution dialog box appears.
From the Stop Type drop-down list, select Normal.
From the Physical Agent drop-down list, select OracleDIAgent.
Click Stop.
The Information dialog box appears with confirmation message — Load Plan was Stopped Successfully.
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.
Note:
You must verify in Argus Mart database if the ETL session has been successfully ended after this step.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:
In the Sessions/Load Plan Executions section > Load Plan Executions folder, select the Load Plan, which you want to restart, and click Restart.
The Restart Load Plan Execution dialog box appears.
From the Physical Agent drop-down list, select OracleDIAgent.
From the Log Level drop-down list, select the required log level.
Click Restart.
The Information dialog box appears with confirmation message— Load Plan restarted successfully.
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.
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.
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 7.3.3, "Restarting the Initial ETL" for the step-by-step procedure to continue the failed Initial ETL from the failed step.
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 7.1.4.2, "Restarting the Failed Initial ETL" for the complete details.
See the Section 7.3.1, "Running the Initial ETL" for the step-by-step procedure to restart the failed Initial ETL from the beginning of ETL.
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:
Log on the Oracle Data Integrator Console, and select the Management tab.
Navigate to Runtime > Scenarios/Load Plans > Folders > LP_ARGUSMART811.
Right-click LP_INI_AM (Load Plan for Initial ETL) or LP_INC_AM (Load Plan for Incremental ETL), and select View.
The steps for the Load Plan appears in the Relationships section in the right pane.
To monitor the progress of the initial ETL after executing the LP_INI_AM Load Plan, execute the following steps:
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.
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.
To view the step where the ETL process failed and also the error message related to the ETL process failure, execute the following steps:
In the Browse tab, navigate to Runtime > Sessions/Load Plan Executions > Load Plan Executions.
The status of the Load Plan appears in red color with the X symbol, which signifies that the ETL session is not in progress.
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.
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.
The error details appears in the right pane.
Or, to view the error message, log on to the Oracle SQL Developer using the Argus ETL user credentials, and execute the queries:
The following are the queries that you can use to view the location of the error:
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.
SELECT * FROM etl_mart_log ORDER BY id DESC;
Once you have restarted a stopped ETL process, you can view the status of the process using the following steps:
In the Browse tab, navigate to Runtime > Sessions/Load Plan Executions > Load Plan Executions section.
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.
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 12.3, "Managing Incremental ETL Process"
ODI Console, see Section 13.3, "Managing Incremental ETL Process"