5 Extracting, Transforming, and Loading Data

This chapter describes the steps required to run and work with the initial extract, transform, and load (ETL) process.

This chapter includes the following topics:

• Prerequisites, Cautions, and Warnings

• Running the Initial ETL

• Running the Initial ETL Again

• Processing a Failed ETL

• Restarting the Initial ETL Process

5.1 Prerequisites, Cautions, and Warnings

Before running the Initial ETL, ensure that Auto extend is set to ON for all the data files in the database that are related to staging and MART.

In addition, note that:

• Because the initial ETL requires a huge amount of temporary space, set the temp space to 100 GB to prevent data errors. After completing the Initial ETL, reduce the temp space to 30 GB.

• After the Initial ETL completes, the balancing log may show differences between the Argus/Stage and MART table counts. This is because of the derivation rules applied to the data mart.

• The system may display the following message:

  Warning !!! - Could not locate MedDRA-J User in the Argus Database.

  Ignore this warning for all MedDRA tables.

• Do not run incremental ETL for more than 50,000 cases. Run the Initial ETL again if the number of cases exceeds 50,000.

5.2 Running the Initial ETL

To run the initial ETL:

1. Log in to the Argus Insight Web Server as a user with administrator privileges.

2. Click Start.

3. Navigate to Programs, Oracle, Argus Insight, and then select Schema Creation Tool.

4. Click Initial ETL. The Oracle Database Connect dialog box opens.

   
   

5. Connect to the Oracle Database:

   1. In the Password field, type the password for the APR_MART user.

   2. In the Database field, type the name of your Argus Insight database.

   3. Click OK.

   The Initial ETL Status dialog box opens.

   
   

6. Click Start ETL to start the initial process of extracting, transforming, and loading data. The system prompts for confirmation that you have completed the required configuration steps.

   
   

7. Click Yes if these items have already been configured. The system displays a status dialog box showing the start time of the ETL, the progress bar, and the current process in execution.

   
   

While the ETL is in progress, you can:

• Click Close to close the dialog box and exit from the Schema Creation Tool. Closing the dialog box does not affect the execution of the ETL process.

• Click Stop ETL to halt the ETL process. For more information about this option, see Section 5.2.3, "Stopping the Execution of ETL."

The system displays a status message when the initial ETL process is completed.

5.2.1 Generating the Balance Logs

When the system successfully completes the Initial ETL process, you should generate and check the logs.

To generate the balance logs:

1. Wait until the system displays the dialog box that reports the initial ETL completed successfully.

2. Click Balancing Logs. The system prompts for confirmation that you want to generate balancing logs for the completed Initial ETL.

3. Click OK. The system opens a command window and generates the balancing logs.

   
   

   When the logs are generated, the system displays a dialog box that lists the location and names of the log files.

4. Click OK to close the dialog box.

5. Open and verify the contents of each Balancing Report.

   The Balancing Reports are located in the following folder:

   drive:\VSS SOURCE\Argus Insight\Main Source\Database Source\DBInstaller

   The log files are named:

   • etl_ini_atos_bal_lm_cfg_rep.log

   • etl_ini_atos_bal_rep.log

   • etl_ini_stom_bal_lm_cfg_rep.log

   • etl_ini_stom_bal_rep.log

5.2.2 Closing the Initial ETL Status Dialog Box

To close the Initial ETL Status dialog box and exit from the Schema Creation Tool:

1. Click Close. The system prompts for confirmation that you want to close the Schema Creation Tool application.

2. Click OK.

5.2.3 Stopping the Execution of ETL

You can choose to stop an ETL in progress.

To halt the execution of the initial ETL process:

1. Click Stop ETL. The system prompts for confirmation that you want to stop the ETL currently in progress.

2. Click OK. The system halts the ETL process and returns to the Initial ETL Status dialog box:

   
   

   At this point, you can select one of the following options:

   • To continue extracting, transforming, and loading the data that was in progress, click Continue.

   • To start the initial ETL from the beginning, click Restart ETL.

   • To exit from the Schema Creation Tool application, click Close.

5.3 Running the Initial ETL Again

To start the ETL process from the beginning:

1. Click Run ETL. The system prompts for confirmation that you want to start the initial ETL from the beginning?

2. Click OK. The Oracle Database Connect dialog box opens.

   
   

3. Enter the password for the APR_MART user, and then click OK. The initial ETL process starts from the beginning.

5.4 Processing a Failed ETL

The initial ETL may fail due to an error. If an error occurs, the system stops processing the ETL and displays the following screen:

You can choose any of the following options for the failed Initial ETL process:

• Click Continue to continue the failed Initial ETL process.

• Click Ignore to ignore the failed Initial ETL process.

• Click Modify Attributes of ETL Data Exclusion if PRE_REQ_CHECK_FLAG switch is set to ABORT.

  Note:

  These modifications must be done before running the Initial ETL process.

5.4.1 Continuing the Failed Initial ETL Process

To continue the Initial ETL process from the failed ETL procedure:

1. Double-click on the ETL error. The system opens a dialog box that contains details of the error.

   
   

2. Review the error information, and then click OK.

3. Right-click on the ETL Error, and click Copy to copy the error data.

   
   

4. Click Continue to continue the failed ETL process. The system prompts for confirmation that you want to start the initial ETL from the stopped process.

5. Click OK. The system continues with the ETL process (if no errors are found).

   
   

5.4.2 Ignoring the Failed Initial ETL Process

To ignore a failed ETL process and continue with the next process in the ETL:

1. Click Ignore. The system prompts for confirmation that you want to skip the failed process and continue executing the Initial ETL with the next process.

2. Click OK. The system starts the Initial ETL from the next process and continues with the ETL process (if no errors are found).

5.4.3 Modifying the Attributes of ETL Data Exclusion

You must modify these attributes before ETL execution.

To modify ETL Data Exclusion attributes:

1. Log in to the Argus Insight application as a user with administrator privileges.

2. Click the Tools tab in the upper-right corner of the Argus Insight Home page to open the ADMINISTRATION TOOLS page.

3. Click the List Maintenance tab.

4. Select Profile Switches from the List Maintenance Items group. The system updates the Attributes group with the profile switches you can modify.

5. Select ETL Data Exclusion and click Modify. The following Modify Attribute dialog box opens:

   
   

6. Click the Value field and enter one of the following values:

   • If you want the ETL process to skip cases with erroneous data and continue processing all other cases, enter IGNORE.

   • If you want the ETL process to abort when it encounters cases with erroneous data, enter ABORT.

7. Click OK to save your changes and return to the List Maintenance tab.

5.5 Restarting the Initial ETL Process

To restart the Initial ETL process starting from after the confirmation message and APR_MART password input:

1. Click Restart ETL. The system prompts for confirmation that you want to start the initial ETL from the beginning.

2. Click OK. The Oracle Database Connect dialog box opens.

   
   

3. Connect to the Oracle Database:

   1. In the Password field, type the password for the APR_MART user.

   2. In the Database field, type the name of your Argus Insight database.

   3. Click OK.

4. Click Start ETL to start the initial process of extracting, transforming, and loading data. The system prompts for confirmation that you have completed the required configuration steps.

   
   

5. Click Yes if these items have already been configured. The system displays a status dialog box showing the start time of the ETL, the progress bar, and the current process in execution:

   
   

   When the system finishes the ETL process, click Close.