Oracle® Argus Mart

Installation and Administration Guide

Release 8.1.1

E84912-01

September 2017

Oracle Argus Mart Installation and Administration Guide, Release 8.1.1

E84912-01

Copyright © 2011, 2017, Oracle and/or its affiliates. All rights reserved.

This software and related documentation are provided under a license agreement containing restrictions on use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license, transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverse engineering, disassembly, or decompilation of this software, unless required by law for interoperability, is prohibited.

The information contained herein is subject to change without notice and is not warranted to be error-free. If you find any errors, please report them to us in writing.

If this is software or related documentation that is delivered to the U.S. Government or anyone licensing it on behalf of the U.S. Government, then the following notice is applicable:

U.S. GOVERNMENT END USERS: Oracle programs, including any operating system, integrated software, any programs installed on the hardware, and/or documentation, delivered to U.S. Government end users are "commercial computer software" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, use, duplication, disclosure, modification, and adaptation of the programs, including any operating system, integrated software, any programs installed on the hardware, and/or documentation, shall be subject to license terms and license restrictions applicable to the programs. No other rights are granted to the U.S. Government.

This software or hardware is developed for general use in a variety of information management applications. It is not developed or intended for use in any inherently dangerous applications, including applications that may create a risk of personal injury. If you use this software or hardware in dangerous applications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure its safe use. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of this software or hardware in dangerous applications.

Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

Intel and Intel Xeon are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks are used under license and are trademarks or registered trademarks of SPARC International, Inc. AMD, Opteron, the AMD logo, and the AMD Opteron logo are trademarks or registered trademarks of Advanced Micro Devices. UNIX is a registered trademark of The Open Group.

This software or hardware and documentation may provide access to or information about content, products, and services from third parties. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to third-party content, products, and services unless otherwise set forth in an applicable agreement between you and Oracle. Oracle Corporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to your access to or use of third-party content, products, or services, except as set forth in an applicable agreement between you and Oracle.

Contents

Preface

- Known Installation and Configuration Issues

- Where to Find More Information

- Documentation Accessibility

Part I Installation

1 Installing the Argus Mart Application

- 1.1 Before Installing the Argus Mart Application

- 1.2 Installing Argus Mart Components

2 Creating the Argus Mart Database Structure

- 2.1 Overview of the dbinstaller.properties file

- 2.2 Installing Argus Mart

- 2.2.1 Pre-requisites

- 2.2.2 Creating User for the Argus Safety Database

- 2.2.3 Creating the Install User

- 2.2.4 Creating Fresh Argus Mart Schema

- 2.2.5 Validating the Schema

- 2.3 Creating Database Links

- 2.3.1 From Argus Mart to Argus Safety (manually)

- 2.3.2 From Argus Mart to Argus Insight

- 2.4 Creating Argus Mart Read-only User

3 Creating Multiple Enterprises in Multi-tenant Environment

4 Configuring ODI Settings

- 4.1 Minimum Components Required

- 4.2 Installing and Configuring ODI

- 4.3 Creating and Testing Data Server Connection

- 4.4 Creating New Physical Schema

- 4.5 Setting-up the Physical Agent for Context CTX_ARGUSMART

- 4.6 Validating Load Plan

5 Configuring the Argus Mart Application

6 Upgrading Argus Mart

- 6.1 Post-upgrade Steps

7 Extracting, Transforming, and Loading Data

- 7.1 Managing Initial ETL Process: ODI Studio

- 7.1.1 Running the ETL

- 7.1.2 Stopping the ETL

- 7.1.3 Restarting the ETL

- 7.1.4 Processing a Failed ETL

- 7.1.4.1 Continuing the Failed Initial ETL

- 7.1.4.2 Restarting the Failed Initial ETL

- 7.2 Monitoring Initial ETL Process: ODI Studio

- 7.2.1 Viewing the Steps of Load Plan

- 7.2.2 Monitoring the ETL

- 7.2.3 Debugging the Failed ETL

- 7.2.4 Monitoring the Restarted ETL (Resume)

- 7.3 Managing Initial ETL Process: ODI Console

- 7.3.1 Running the Initial ETL

- 7.3.2 Stopping the Initial ETL

- 7.3.3 Restarting the Initial ETL

- 7.3.4 Processing a Failed ETL

- 7.3.4.1 Continuing the Failed Initial ETL

- 7.3.4.2 Restarting the Failed Initial ETL

- 7.4 Monitoring Initial ETL Process: ODI Console

- 7.4.1 Viewing the Steps of Load Plan

- 7.4.2 Monitoring the ETL

- 7.4.3 Debugging the Failed ETL

- 7.4.4 Monitoring the Restarted ETL (Resume)

- 7.5 Running the Incremental ETL

8 Uninstalling the Argus Mart Application

Part II Administration

9 Setting Context in Multi-tenant Environment

10 Secure Unblinding in Argus Mart

11 Rebuilding a Case in Argus Mart

12 Incremental ETL: ODI Studio

- 12.1 Scheduling Incremental ETL

- 12.2 Monitoring Incremental ETL Process

- 12.3 Managing Incremental ETL Process

- 12.3.1 Running the Incremental ETL

- 12.3.2 Stopping the Incremental ETL

- 12.3.3 Restarting the Incremental ETL

- 12.3.4 Processing a Stopped or a Failed ETL

- 12.3.4.1 Continuing the Failed Incremental ETL

- 12.3.4.2 Restarting the Failed Incremental ETL

13 Incremental ETL: ODI Console

- 13.1 Scheduling an ETL

- 13.2 Monitoring Incremental ETL Process

- 13.3 Managing Incremental ETL Process

- 13.3.1 Running the Incremental ETL

- 13.3.2 Stopping the Incremental ETL

- 13.3.3 Restarting the Incremental ETL

- 13.3.4 Processing a Stopped or a Failed ETL

14 Re-initializing the ETL Process

15 Troubleshooting

- 15.1 Dictionary Reload Error

- 15.2 ETL Execution Pre-Requisite Check Failed Error

- 15.3 FR Consistency Log Error

- 15.4 MedDRA Mismatch Error

- 15.5 Multiple Enterprise Creation Messages

- 15.5.1 Source Enterprise Does Not Exist In Mart

- 15.5.2 Enterprise Does Not Exist In Argus Safety

- 15.5.3 Enterprise Does Not Exist For Configuration In Mart

- 15.6 SMQ-CMQ Loop Error

A Configuring Standalone Installation Topology for Standalone Agent

- A.1 Pre-requisite

- A.2 To configure a standalone agent:

B Configuring Standard Installation Topology for Java EE Agent

- B.1 Pre-requisite

- B.2 To configure a Java EE agent:

Preface

Oracle Argus Mart (AM) is a data source software product that can be used for analysis and reporting in medical product safety and pharmacovigilance. The primary data for Argus Mart are the adverse event cases managed by Oracle Argus Safety. Argus Mart product consists of:

- A pre-defined Argus Mart data model containing Signal and Reporting tables

- Pre-built ODI based interfaces that are linked to Oracle PL/SQL based packages

Argus Safety serves as the primary source of data for Argus Mart. The ODI software extracts the data from Argus Safety database, transforms, and loads the data into Argus Mart. When the ODI tool loads the data into Argus Mart, it is available for Argus Mart users for querying, and reporting activities.

Known Installation and Configuration Issues

Oracle maintains a list of installation and configuration issues that you can download from My Oracle Support (MOS). For information about these issues, please see Note ID 1326918.1.

Where to Find More Information

Oracle Help Center

The latest user documentation for Oracle Health Sciences products is available at http://docs.oracle.com/en/industries/health-sciences/.

My Oracle Support

The latest release notes, patches and white papers are on My Oracle Support (MOS) at https://support.oracle.com. For help with using MOS, see https://docs.oracle.com/cd/E74665_01/MOSHP/toc.htm.

Documentation Accessibility

For information about Oracle's commitment to accessibility, visit the Oracle Accessibility Program website at http://www.oracle.com/pls/topic/lookup?ctx=acc&id=docacc.

Access to Oracle Support

Oracle customers that have purchased support have access to electronic support through My Oracle Support. For information, visit http://www.oracle.com/pls/topic/lookup?ctx=acc&id=info or visit http://www.oracle.com/pls/topic/lookup?ctx=acc&id=trs if you are hearing impaired.

Part I

Installation

This part of the guide introduces you with the Oracle Argus Mart software product. In addition, it also gives you an outline of all the tasks that are required to install and configure Oracle Argus Mart, and explains the organization of these tasks in this guide. The Oracle Argus Mart is referred to as AM and Oracle Data Integrator as ODI in all the later sections of the guide.

1 Installing the Argus Mart Application

This chapter explains how to use the installation wizard to install Argus Mart, including ODI Repository and the Schema Creation Tool.

Figure 1-1 Installation Progress: Installing the AM Application

This chapter includes the following topics:

- Before Installing the Argus Mart Application

- Installing Argus Mart Components

1.1 Before Installing the Argus Mart Application

- Verify that the Argus Mart database instance has been created and is running. In addition, verify that the database has been created using the character set of your Argus Safety database.

- You must install the required software components, as mentioned in the following table:

Table 1-1 Argus Mart Software Requirements

Specification

Oracle Data Integrator Server

Database

DBInstaller

Client

Operating System

As certified by the ODI

As certified by the ODI

(Supports bothCDB-PDB/Non CDB)

Microsoft Windows Server 2012 Standard

Microsoft Windows Server 2012 R2 Standard

Windows 10

Windows 8.1

Windows 7

(Both 32 and 64 bit)

Oracle Database

Oracle RAC

Exadata 12c R1 (with 12.1.0.2)

Oracle 12c R1 (12.1.0.2) (Standard Edition 2 (SE2)/Enterprise Edition)

Note: Oracle database Standard Edition 2 (SE2) is supported for single tenant deployment only.

Oracle Data Integrator (ODI)

12.2.1/12.1.3

Note:
Oracle Client Patch required for the Schema Creation Tool

1. Download the patch 19720843: WINDOWS DB BUNDLE PATCH 12.1.0.2.1 from the Oracle Support.

2. Install the patch, and apply the following workaround:
a. Set the oracle_home as your client home location. For example:

SET ORACLE_HOME=C:\app\client32\product\12.1.0\client_1

i. On the client machine, go to %oracle_home%\bin\

ii. From \p19720843_121020_WINNT\19720843\files\bin\, copy the file oranfsodm12.dll, and paste it under %oracle_home%\bin

b. Run sqlldr help=y or sqlldr.exe.

- Make sure that you have installed the following software on the machine where Argus Mart is being installed:

- Microsoft .NET 3.5 Framework

- Oracle Client 12.1.0.2 (32 bit) (Administrator Installation type)
Only one version of Oracle Client should be installed on the server. Argus Mart Installer will not work in case there are multiple versions of Oracle Client are installed on the same machine.

1.2 Installing Argus Mart Components

To run the installation wizard and install the Argus Mart components:

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

2. Download the Argus Mart software from Oracle E-delivery and copy the software to the Argus Mart Server.

3. Click setup.exe.
The Welcome screen of the installation wizard appears with the following options:

1. About Oracle Universal Installer—Specifies information about the Oracle Universal Installer.

2. Installed Products—Displays the list of installed products.

4. On the Welcome screen, click Next.
The Specify Home Details screen appears.

5. In the Name field, enter the name for the product installation.

6. In the Path field, specify the location of the folder where the Argus Mart will be installed.

- To install Argus Mart into the default folder, click Next.

- To install Argus Mart into a different folder, click Browse, select another folder, and click Next.

The Oracle Home Location screen appears.

7. Click Browse, and navigate to the location of Oracle Home that identifies the TNSNAMES.ORA file.
Example: C:\app\product\11.2.0\client_1

8. Click Next.
The Database Details screen appears.

9. Enter the following parameters:

- In the AM Database Server field, enter name or IP address.

- In the AM Database Instance field, enter the database instance name.

- In the AM Database Port field, enter the database listener port number.

When the Installation process is complete, you may validate these database inputs by navigating to the TNSNAMES.ORA file, saved at the following path:

<ORACLE_HOME>\NETWORK\ADMIN

10. Click Next.
The Summary screen appears.

11. Click Install to start the installation.
A message appears — Argus Mart is configuring your new software.

When installation is complete, the End Of Installation screen appears.

12. To verify the successful installation of Argus Mart, click Installed Products.
The Inventory screen appears.

13. Navigate to Contents > Independent Products.
Oracle Argus Mart appears in the list of products.

14. Click Close.

15. Click Exit.
A confirmation dialog box appears.

16. Click Yes to close the Installer wizard screen.

2 Creating the Argus Mart Database Structure

The Argus Mart is now installed in silent-mode through Liquibase.

Liquibase is a refactoring tool that enables Argus Mart to be in synchronization with the closest major release, and subsequently upgrades the product to the required minor versions of the product

The Argus Mart Liquibase install enables you to create the Argus Mart schema structure. It creates a link between the safety database and the new Argus Mart database. The Extract, Transform, and Load (ETL) process uses this link to transfer data from source (Argus Safety) database to the Argus Mart database. When transferred, this data can be used for querying and reporting purposes.

Figure 2-1 Installation Progress: Creating the Argus Mart Data Structure

2.1 Overview of the dbinstaller.properties file

Open the DBInstaller\dbinstaller.properties file, and view or modify the following parameters to make sure that Liquibase runs properly.

Parameter

Description

Modify

Default or Sample Value

db_connect_string

Jdbc Url to connect to the Target Database.

Yes

url=abc.in.oracle.com:1521/811DB

dba_User

Name of the Target Database DBA user

Yes

dba_User=am_dba_user

logLevel

Log level setting related to liquibase for smooth run.
Possible values:

- DEBUG

- INFO

Yes

logLevel=info

appSchema_sm_mart

Argus Mart schema Owner name and password

No

appSchema_mart_user=am_mart/pwd

appSchema_sm_stage

Argus Mart Staging schema Owner

No

appSchema_sm_stage=am_stage

appSchem_sm_app

Argus ILinkMart Application schema Owner name and password

No

appSchema_sm_app=am_app/pwd

appSchema_etl_user

Argus Mart Etl User and password

No

appSchema_etl_user=am_etl/pwd

appSchema_rls_user

Argus Mart VPD schema Owner name and password

No

appSchema_rls_user=am_vpd/pwd

appSchema_bi_user

Argus Mart BI schema Owner name and password

No

appSchema_bi_user=am_bi/pwd

#Mart Database Role
am_vpd_admin_role

Admin role given to AM_ETL_USER

Yes

am_vpd_admin_role=AM_VPD_ADMIN_ROLE

Safety_db

Argus Safety Database TNS

Yes

Safety_DB=AS_DBA_USER/pwd@SAF811DB

Safety_RO_User

Argus Mart read-only user name in Argus Safety

Yes

Safety_RO_User=am_ro_user

#Datafile directory location

Location of the default datafiles directory.

Yes

default_datafile_destination
=c:/app/oradata/AM811DB

#Argus Mart Stage User Datafiles

Location of Argus Mart stage user datafiles.

Yes

- stage_data_ts_datafile=C:\app\oradata\AM81MTF2\AM__STAGE_DATA_01.DBF

- stage_ind_ts_datafile=C:\app\oradata\AM81MTF2\AM_STAGE_INDEX_01.DBF

- stage_lob_ts_datafile=C:\app\oradata\AM81MTF2\AM_STAGE_LOB_01.DBF

#Argus Mart > Mart User Datafiles

Location of Argus Mart > Mart user datafiles.

Yes

- mart_data_ts_datafile=C:\app\oradata\AM81MTF2\AM_MART_DATA_01.DBF

- mart_ind_ts_datafile=C:\app\oradata\AM81MTF2\AM_MART_INDEX_01.DBF

- mart_lob_ts_datafile=C:\app\oradata\AM81MTF2\AM_MART_LOB_01.DBF

#Argus Mart Apr User Datafiles

Location of Argus Mart APR user datafiles

Yes

- apr_user_data_ts_datafile=C:\app\oradata\AM81MTF2\AM_APP_DATA_01.DBF

- apr_user_ind_ts_datafile=C:\app\oradata\AM81MTF2\AM_APP_INDEX_01.DBF

- apr_user_lob_ts_datafile=C:\app\oradata\AM81MTF2\AM_APP_LOB_01.DBF

Default and Temporary tablespaces

Defines default and temporary tablespace name

No

- default_ts=USERS

- temp_ts=TEMP

Tablespace Encryption

Specifies the logic used for default encryption

Yes

- No encryption—Blank

- Encryption done—Text like encryption using 'AES256' default storage (encrypt)

Tablespace Parameters

Specifies the details of the tablespace

Yes

- tablespace_initial_size=10M

- tablespace_autoextend=ON

- tablespace_next_size=10M

- tablespace_block_size=8K

LOB Storage

Location of Argus Mart LOB storage.
Possible Values:

- basicfile

- securefile

Yes

lob_storage=basicfile

2.2 Installing Argus Mart

This section gives you a brief introduction about all the options that are available when you run the Argus Mart Liquibase install.

To start the Argus Mart installation, execute the following steps:

1. Pre-requisites

2. Creating the Safety Read Only user for Argus Mart

3. Creating the Install user in Argus Mart

4. Creating the fresh schema of Argus Mart

5. Validating the schema

2.2.1 Pre-requisites

You must check the following settings before you run the Argus Mart installation:

- GLOBAL_NAMES is set to FALSE to enable the Argus Mart application to create the database links.

- NLS_LENGTH_SEMANTICS is set to CHAR for the Argus Mart Schema Creation Tool to run.

- TNS Name for the Argus Safety Database must be present in the Oracle Database Client > tnsnames.ora file at the following path:
...\network\admin\tnsnames.ora.

2.2.2 Creating User for the Argus Safety Database

1. To create the Safety Read-only user for Mart, execute the following script from the folder DBInstaller\Utilities\Create_Safety_Ro_User:

- For Windows—Use the batch script sm_argus_read_only{user}.bat

- For Linux—Use the shell script sm_argus_read_only{user}

2. When prompted, enter the following parameters:

- Name of the Safety Database instance

- Name of DBA User in Safety Database

- DBA user password in Safety Database

- Name of the Safety Read Only User to be created for Mart

- Password of the Safety Read Only User

- Role to be associated with Safety Read-only User

- Default Tablespace for Safety Read-only User—For example, USERS

- Temporary Tablespace for Safety Read-only User—For example, TEMP

The  process of creating a Safety Read-only user begins.

3. When a confirmation message appears, review the log file and check for any errors from:
\DBInstaller\Utilities\Create_Safety_RO_User

2.2.3 Creating the Install User

Note:
You must execute this script to install Argus Mart database, even if you are using the SYSTEM user. This script provides additional grants to the existing user.

To create an Install user that has privileges same as the SYSTEM user for installation:

1. Execute the following script from the folder DBInstaller\Utilities\Create_Dba_User:

- For Windows—Use the batch script am_create_dba_user.bat

- For Linux—Use the shell script am_create_dba_user

2. When prompted, enter the following parameters:

- Argus Mart Database instance name

- SYS or an equivalent SYSDBA user on this database

- SYSDBA user password

- Name of the Install User—For example, SYSTEM or AM_DBA_USER

- Install user password

If you provide a non-existing user name, then the script creates this as a new user, and provides the necessary grants to this user.

If you provide an already existing user name, then the script provides the necessary additional grants to the existing user.

When done, a message appears as:

Install User created.

3. Press Exit.

Recommendation:
For security reasons, Oracle recommends to drop the Install user from the database after successful installation of Argus Mart as this user will have Install privileges.
To drop this user, connect to the respective database as a privileged user, and execute the following command:

DROP USER <INSTALL_USER> CASCADE;

2.2.4 Creating Fresh Argus Mart Schema

1. Log in to the Argus Mart Server with administrator privileges.

2. Navigate to Programs > Oracle > Argus Mart > Database > DBInstaller.

3. Open Dbinstaller\dbinstaller.properties file.

4. Modify the following parameters:

- Mart Database:

- db_connect_string—connects to the Argus Mart database.
Syntax: db_connect_string=<host>:<port>/<SID>

For example, server.us.xx.com:1521/AM811MT

- dba_user—specifies the name of the Install user to run Argus Mart Liquibase Install.
See Section 2.2.3, "Creating the Install User."

- default_datafile_directory—default location on the database server, where datafiles will be created.

- Mart User of each schema, where password is optional:

- appschema_sm_mart

- appschema_sm_stage

- appschema_sm_app

- appschema_etl_user

- appschema_rls_user

- appschema_bi_user

- Safety Database:

- safety_db—Argus Safety database instance name

- safety_ro_user—Argus Mart Read-only user created in Argus Safety

Note:
For more information on these parameters, see Section 2.1, "Overview of the dbinstaller.properties file."
It is recommended that you preserve the default names for tablespaces and roles.

5. From the command prompt, go to DBInstaller directory.

6. Type dbinstaller.bat, and press Enter.
The Liquibase install begins, and the parameters (as entered) appear on the command prompt screen with password in the hidden mode (****).

7. Keep monitoring the Liquibase progress by querying the Liquibase Log table mart_dbchangelog, created in AM_MART_USER schema.

8. When the process is complete, a confirmation message appears with the latest version of Argus Mart.
The following is created as per the values specified in the dbinstaller.properties file:

- Users

- AM_MART_USER

- AM_STAGE_USER

- AM_APP_USER

- AM_ETL_USER

- AM_VPD_USER

- AM_BI_USER

- Roles

- AM_VPD_ADMIN_ROLE

- Factory Data

- Out of the box Factory data is loaded into tables such as ETL_SIGNAL_TABLE_MAPPING, RM_CMN_PROFILE_GLOBAL, etc.

- Database Links, DB_LINK_ARGUS:

- From AM_STAGE_USER of Argus Mart to ARGUS_APP of Safety

- Tablespaces
Note that the tablespace names begin with APR. The Argus Power Reports (APR) product was renamed to Argus Insight.

The Schema Creation Tool creates the following tablespaces for the Argus Mart database, when you create Argus Mart schema:

AM_APP_DATA_01

AM_APP_INDEX_01

AM_APP_LOB_01

AM_MART_DATA_01

AM_MART_INDEX_01

AM_MART_LOB_01

AM_STAGE_DATA_01

AM_STAGE_INDEX_01

AM_STAGE_LOB_01

2.2.5 Validating the Schema

1. Navigate to Database > DBInstaller > ValidateSchema.

2. Run the batch script validate_schema.bat.

3. When prompted, enter the following parameters:

- Enter instance name: <Argus Mart Database Instance name>

- Enter DBA User Name: <Install user of Argus Mart>

- Enter Password for DBA User: <Install user password in Argus Mart>

- Enter Validation Data File Name: The validation control file name (without the .CTL extension)
For example, VLDN_AM_8.1.1

- Enter the destination where the log file is to be placed:
<C:\AM_811\Database\DBInstaller\ValidateSchema>

- Enter the log file name for recording the schema differences:
<VLDN_AM_8.1.1_diff.log>

- Enter Validation Output File Name: The validation output file name to record the validation progress:
<VLDN_AM_8.1.1_out.log>

4. Enter the password for the Argus Mart SYSTEM or Install user, and press Enter.

5. When the validation process is complete, a confirmation message appears.

- To view any schema discrepancies, such as missing objects, use the Difference Log File.

- To view the list of errors, which occurred during schema validation, use the Output Log File.

Note:
If Argus Mart read-only user is created, then ignore the schema validation differences, where

- Objects are RO% views/columns

- GRANTEE is Argus Mart Read-only user

2.3 Creating Database Links

2.3.1 From Argus Mart to Argus Safety (manually)

During Fresh installation, the database link from Argus Mart to Argus Safety (DB_LINK_ARGUS) is created as a part of the installation process. However, you can manually recreate the link DB_LINK_ARGUS.

1. Navigate to DBInstaller\Utilities\Create_Safety_RO_User:

- For Windows—execute the batch script sm_create_db_link_argus.bat

- For Linux—execute the shell script sm_create_db_link_argus

2. When prompted, enter the following parameters:

- Enter the name of the Safety Database instance: Safety Database Instance Name

- Enter the name of the Mart Database instance: Mart Database Instance Name

- Enter the name of DBA User in Mart DB: Mart Install user

- Enter the password for user Mart DBA User: Mart Install user password

- Enter the name of the Read Only User in Safety DB: Safety Read-only user for Mart

- Enter the password for Read Only user in Safety: Read-only user password

- Enter the name of the stage owner in Mart: Stage user of Mart

- Enter the password for Stage user: Stage user password

3. Verify that the script is successfully connected as <Mart Install user/Mart Install user pwd>@<Argus Mart>, and press Enter.
Wait until a message Created DB_LINK_ARGUS appears for each Stage, Mart, and App user.

4. Press Exit to close the Mart to Argus Database Link Creation window.

5. Verify the log files for status from:
DBInstaller\Utilities\Create_Safety_RO_UserDB_LINK_ARGUS_MMDDYYYY_HH24MISS.log

2.3.2 From Argus Mart to Argus Insight

1. When Argus Insight and Argus Mart are on different database instances, execute the script from the folder DBInstaller\Utilities\Database_Links\ ai_am_db_link:

- For Windows—execute the batch script am_insight_db_link_setup.bat

- For Linux—execute the shell script am_insight_db_link_setup

2. When prompted, enter the following parameters:

- Enter the Argus Mart Database Instance name

- Enter the INSTALL user name in Argus Mart, like AM_DBA_USER

- Enter the password for the above user

- Enter the Argus Mart application user name, like AM_APP_USER

- Enter the password for the above user

- Enter the Argus Mart Schema user name, like AM_MART_USER

- Enter the password for the above user

- Enter the Argus Insight Database Name

- Enter the Argus Insight Application User name, like APR_APP

- Enter the Password for the above user

- Enter the Argus Insight Readonly User name, like APR_LINK_USER

- Enter the Password for the above user

The process of creating the database links begins.

3. The following database links are created (by dropping the existing links if any, with the same name.

- DB_LINK_INSIGHT—From Argus Mart AM_APP_USER > Argus Insight APR_APP

- ARGUS_MART_TO_INSIGHT_MART—From Argus Mart AM_MART_USER > Argus Insight APR_LINK_USER

4. Review the following log file, and check for any errors, when a confirmation message appears.
MART_TO_INSIGHT_DB_LINK_MMDDYYYY_HH24MISS.log

2.4 Creating Argus Mart Read-only User

You can create a read-only schema in Argus Mart. This schema will have read-only (SELECT) access on all the tables and views of the AM_MART schema. Besides, this read-only schema can also be used for customized reporting purpose.

1. Open DBInstaller\Utilities\Create_Mart_Ro_User file.

- For Windows—execute the batch script am_ ro_user.bat

- For Linux—execute the shell script am_ ro_user

2. When prompted, enter the following parameters:

- Enter TNSNAME Entry to connect to the ARGUS MART Database: < ARGUS MART Database name>

- Enter the name of Custom DBA user in Mart Database: < Argus Mart Install user>

- Enter password for install user in Mart Database: <Install user password>

- Enter Read Only user to be created in Mart Database: <Read-only user to be created in Mart>

- Enter password for Read Only user of Mart Database: <Mart Read-only user password>

3. When the process is complete, press Exit.

4. Verify the log files for status information from:
DBInstaller\Utilities\Create_Mart_Ro_User\AM_RO_User_MMDDYYYY_HH24MISS.log

3 Creating Multiple Enterprises in Multi-tenant Environment

When you run ETL to transfer data from the Argus Safety database to Argus Mart, a default enterprise is automatically fetched into Argus Mart. In addition to the default enterprise, Argus Mart also enables you to create multiple enterprises by using:

- The configuration of default enterprise.

- The configuration of any other existing enterprise in Argus Safety Database.

This chapter explains the step-by-step procedure that you need to execute to create multiple enterprises in Argus Mart in a multi-tenant environment.

Note:
In case of a Multi-tenant setup, you can create additional enterprises after initial or incremental ETL as well.

Figure 3-1 Installation Progress: Creating Multiple Enterprises in Multi-tenant Environment

To create multiple enterprises in Argus Mart:

1. Fetch a new enterprise from Argus Safety to Argus Mart by executing the script from:
DBInstaller\Utilities\ create_enterprise

- For Windows—execute the batch script am_create_enterprise.bat

- For Linux—execute the shell script am_create_enterprise

2. When prompted, enter the following parameters:

- Enter the Argus Mart Database instance

- Enter the name of ETL User in the above instance, like AM_ETL_USER

- Enter Password for ETL User in the above instance

- Enter comma separated enterprise short names to be copied over from Safety, like ENT1,ENT2,ENT3

- Enter the enterprise short name in Mart, from which data is to be copied, like ENT_ABBREV

Note that the Default enterprise in Argus Mart will be used to copy the base values, if no use input is provided.

3. Before bringing over the new enterprise to Argus Mart, the following checks are performed, whether:

- the set-up is multi-tenant

- the Factory data is loaded in the specified Argus Mart database

- the new enterprise exists in Argus Safety

- the new enterprise does not already exist in Argus Mart

- the base enterprise from which data is to be copied over, exists in Argus Mart

When all these pre-requisites are satisfied, the process of enterprise addition into Argus Mart begins.

4. Review the following log file, and check for any errors, when a confirmation message appears.
MART_TO_INSIGHT_DB_LINK_MMDDYYYY_HH24MISS.log.

4 Configuring ODI Settings

After you have installed Oracle Data Integrator (ODI), you must configure certain settings to be able to use it to run the ETL process.

All the ODI related data has been zipped into a file, which is a part of the installation package and is available at the following path:

...\ArgusMart\ODI\AM.zip

However, there are certain tasks that you need to execute before and after importing this zip file. All these tasks are covered in sequence in the later sections.

This chapter explains the step-by-step procedure to configure all the ODI related tasks using the ODI Studio. The configuration of these tasks using the ODI Console is not supported for this release.

Figure 4-1 Installation Progress: Configuring ODI Settings

4.1 Minimum Components Required

The following are the minimum components required to setup ODI for Argus Mart:

- ODI Studio

- ODI Agent

4.2 Installing and Configuring ODI

To install and configure the ODI components (ODI Studio and ODI Agent):

Reference:
To view the Oracle® Fusion Middleware Installing and Configuring Oracle Data Integrator Guide, refer to the following:

- For ODI 12.1.3 https://docs.oracle.com/middleware/1213/core/ODING/toc.htm

- For ODI 12.2.1 https://docs.oracle.com/middleware/1221/core/ODING/toc.htm

1. Understand the ODI Topology—To understand the ODI agent topologies for the best suitable installation, refer to the Oracle® Fusion Middleware Installing and Configuring Oracle Data Integrator Guide > Section: Planning the Oracle Data Integrator Installation.

2. Install the ODI—Refer to the Oracle® Fusion Middleware Installing and Configuring Oracle Data Integrator Guide > Section: Installing Oracle Data Integrator.

3. Create the Master and Work Repository Schema—Refer to the Oracle® Fusion Middleware Installing and Configuring Oracle Data Integrator Guide > Section Creating the Master and Work Repository Schema.

4. Create Repository Login—Create a login name for the Master or Work Repository.

1. On the Oracle Data Integrator Login screen, click the + icon.

Figure 4-2 Creating Login for Repository

The Repository Connection Information screen appears.

Figure 4-3 Repository Connection Information

2. On the Repository Connection Information screen:

1. In the Login Name field, enter a login name for the repository.

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

3. In the Password field, enter the password for the SUPERVISOR user.
Enter the same password as provided for the SUPERVISOR while installing the ODI.

4. In the Database Connection section, enter the Master Repository User Name and Password.
Enter the same User Name and Password as provided while installing the ODI.

5. Enter the database details in the Driver List, Driver Name and URL fields.
You may also click the Search icon next to the Driver List and URL fields to search for the required Driver List and URL.

6. In Work Repository section:
— Select Master Repository Only option, if the login is being created for the Master Repository.

— Select the Work Repository option, and enter the name of the Work Repository in the adjacent text box (for example, WORKREP1).

You may also click the Search icon next to the Work Repository name text box.

7. Click OK.
The login name is created.

5. Import Argus Mart Schema Objects

1. Open the ODI, and connect to the repository using the Work Repository credentials that you have just created, as mentioned in the previous section.

2. From the Designer tab, click the down arrow.
A drop-down menu appears.

Figure 4-4 Import Link

3. Click Import.
The Import Selection dialog box appears.

4. Select Smart Import, and click OK.
The Smart Import dialog box appears.

5. Click the Search icon next to the File Selection field.
The Select an import file dialog box appears.

6. Navigate to the AM.zip file, saved at the following location:
...\ArgusMart\ODI\AM.zip

7. Select the AM.zip file, and click Open.
The complete path of the zip file appears in the File Selection field.
Keep the Response file field as blank.

8. Click Next.
The Please wait dialog box appears with a Matching Import Objects message.

Subsequently, the Smart Import dialog box appears listing the components that will be imported from the zip file using the Import Actions screen.

Figure 4-5 Displaying Components Imported from the Zip File

9. Click Next.
The Summary screen appears with the No issues message if there are no errors in the import process.

10. Click Finish.
The Please wait dialog box appears with a Import in progress message.

Subsequently, the Smart Import Report screen appears listing the objects imported using the zip file.

Figure 4-6 Smart Import Report

11. Click Save.
The Save Report dialog box appears.

12. Click the Search icon next to the Name of the target file field.
The Save dialog box appears.

13. Navigate to the path where you want to save the report, and in the File Name field, enter the name for the report.

14. Click Save.
The name of the report file appears along with the complete path in the Name of the target file field.

15. Click OK.
The Information dialog box appears with the path where the report file has been saved.

16. Click OK.
This completes the steps to import the AM zip file.

You may verify this from ODI > Designer tab. The Argus Mart specific folders now appears in the Designer tab under Projects section, such as ARGUSMART<version number>.

Figure 4-7 Verifying the Smart Import

6. Setup the Physical Agent

1. Open the ODI Studio, and login to the Work Repository Connection created in the Step 4.

2. Go to the Topology tab, from the left side pane, navigate to the Physical Architecture > Agents.

3. Double -click PA_AM.

4. From the Definition Vertical tab, in the Host field, enter the IP address of the ODI Server.
If you configure a Standalone Agent, then leave the Agent Name as PA_AM, and configure the Standalone Agent in the next step with same name.If you configure a Java EE Agent, then change the Agent Name to OracleDIAgent (as by default), and the Agent Name for Java EE Agent will be created as OracleDIAgent. For more information, refer to Oracle® Fusion Middleware Installing and Configuring Oracle Data Integrator > 5 Configuring the Domain for the Java EE Agent > 5.3 Creating a Java EE Agent in the Master Repository Using ODI Studio.

Make sure to use the same port number in ODI Studio as well as Agent configured.

7. Configure the Domain for the ODI Agent

Note:
For Argus Mart, configure either one of the ODI Agents (Standalone, or Java EE).

- To configure the domain for the Standalone Agent, refer to the Appendix A, "Configuring Standalone Installation Topology for Standalone Agent."

- To configure the domain for the Java EE Agent, refer to the Appendix B, "Configuring Standard Installation Topology for Java EE Agent."

4.3 Creating and Testing Data Server Connection

To create and test the Data Server connection, execute the following procedure:

1. Select the Topology tab.
In the Physical Architecture section, under Oracle folder, double-click DS_AM_ARGUSMART.

Figure 4-8 Creating Data Server Connection

The connection details appears in the right pane, with Definition selected by default.

2. In the User field, enter the name of the Argus ETL user (AM_ETL_USER).
This user was created in the Section 2.2.4, "Creating Fresh Argus Mart Schema".

3. In the Password field, enter the password for the Argus ETL User.

4. Select JDBC in the right pane, and enter database details of the Argus Mart schema in the JDBC Driver and JDBC Url fields.

Figure 4-9 Entering Database Details

You may also click the Search icon next to the JDBC Driver and JDBC Url fields to search for the required JDBC Driver and JDBC Url.

5. Click Test Connection.
A confirmation message appears to save data before testing the connection.

6. Click OK.
The Test Connection dialog box appears.

7. From the Physical Agent drop-down list, select Local, and click Test.
The Information dialog box appears with the Successful Connection message.

8. Click OK.

9. From the Physical Agent drop-down list, select the Physical Agent, and click Test.
The Information dialog box appears with the Successful Connection message.

10. Click OK.
This completes the steps to create and test the Data Server connection.

4.4 Creating New Physical Schema

To fetch data by ODI ETL, you must create a new physical schema.

1. Select the Topology tab.
In the Physical Architecture section, under Oracle folder, right-click DS_AM_ARGUSMART.

Figure 4-10 Selecting New Physical Schema

2. Select New Physical Schema.
The Physical Schema screen appears, where Definition is selected by default.

3. From the Schema drop-down list, select the Argus ETL User (AM_ETL_USER).
This user was created in Section 2.2.4, "Creating Fresh Argus Mart Schema".

4. From the Schema (Work Schema) drop-down list, select the Argus ETL User (AM_ETL_USER) again.

Figure 4-11 Selecting Argus ETL User in Definition Section

5. From the left-pane, select Context, and click the + symbol.
A new row is added in the empty space below the Context and Logical Schema options.

Figure 4-12 Selecting Context for the Data Server

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

7. From the Logical Schema drop-down list, select LS_AM_ARGUSMART.

8. Click Save on the menu bar.
The new physical schema appears under Physical Architecture section, in Oracle folder.

Figure 4-13 Viewing the New Physical Schema

4.5 Setting-up the Physical Agent for Context CTX_ARGUSMART

To associate the physical agent with the context:

1. Select the Topology tab.

2. To open the context, from the Contexts section, double click CTX_ARGUSMART.

3. Navigate to Agents.

4. For the Logical Agent LA_AM, from the Physical Agent drop-down list, select an agent.
Select the physical agent that was created in while configuring the ODI.

5. Click Save.

4.6 Validating Load Plan

To validate the Load Plan, execute the following steps:

1. Select Operator tab.

2. In the Load Plans and Scenarios > LP_ARGUSMART811 section, double-click the LP_INI_AM Load Plan.

Figure 4-14 Double-clicking the LP_INI_AM Load Plan

If the Object Locking dialog box appears, click No and proceed with the Validation process.

The Load Plan details appears in the right pane.

Figure 4-15 Load Plan Details

3. Click Validate.
A confirmation message appears, if there are no issues associated with the Load Plan.

5 Configuring the Argus Mart Application

Before running the Initial ETL (Extract, Transform, and Load) process, you need to configure the Argus Mart Common Profile Switches to have control over the data that you want to transfer from the Argus Safety database to the Argus Mart database. These Common Profile Switches are configured using the Argus Safety Console.

This section explains these Common Profile Switches along with the step-by-step procedure to configure these profile switches using the Argus Safety Console.

Figure 5-1 Installation Progress: Configuring the Argus Mart Application

To configure the Common Profile Switches using the Argus Safety Console:

1. Log on to the Argus Safety Console and navigate to System Configuration > System Management (Common Profile Switches).

Figure 5-2 System Management Link on Argus Safety Console

The Common Profile screen appears with the list of configuration options in the left pane.

2. Click Argus Mart.
The Modify Argus Mart screen appears with the list of Argus Mart Common Profile Switches that you need to configure, in the right pane.

You can configure these Common Profile Switches using this screen.

See Table 5-1 for description about these profile switches along with their type (Global or Enterprise-specific).

The Global switches are visible only if you are logged in from a default enterprise.

If you are logged in from a non-default enterprise, only the enterprise-specific switches are visible in the list of Common Profile Switches.

3. Enter the required input in the text box (or select the radio buttons in case of the ENABLE SM PROCESSING profile switch) adjacent to the name of each profile switch, and click Save.

Note:
The Global Switches, as mentioned in the table below, impact all enterprises configured for Argus Mart whereas the Enterprise specific Switches impact the enterprise to which user is logged in, to access the Argus Safety console.

The following table lists the Common Profile Switches that you can configure for Argus Mart, their type, and their description:

Table 5-1 Common Profile Switches for Argus Mart

Profile Switch

Type

Description

ENABLE SM PROCESSING

Global switch

This switch is used to enable or disable SM Processing for Argus Mart.

Yes - Enable SM Processing for Argus Mart.

No - Disable SM Processing for Argus Mart.

The value for this switch cannot be changed once initial ETL has been executed.

ENABLE_AI_PROCESSING

Global switch

This switch is used to enable or disable Argus Insight Processing for Argus Mart.

0 - Do not populate Argus Insight data in Argus Mart.

1 - Populate Argus Insight data in Argus Mart.

REVISIONS TO PROCESS

Global switch

This switch refers to the Configuration Flag to process maximum number of revisions in an incremental ETL run.

Setting the value as 0 for this switch represents that the Configuration Flag is not set.

FIRST HUMAN LANGUAGE

Enterprise specific switch

This switch refers to first human language for derived decoded items. This value should not be changed after data mart is initialized.

For information on the First Human Language profile switch columns in SM Tables, refer to the ETL Mapping document.

SECOND HUMAN LANGUAGE

Enterprise specific switch

This switch refers to second human language for derived decoded items. This value should not be changed after data mart is initialized.

For information on the Second Human Language profile switch columns in SM Tables, refer to the ETL Mapping document.

CUSTOM DATASHEET FOR LISTEDNESS

Enterprise specific switch

This switch refers to the specific datasheet value to be used for the SM_EVENT_PRODUCT.LISTEDNESS_CDS_VE column. This value should not be changed after data mart is initialized.

SMQ/CMQ FOR FATAL TERMS

Enterprise specific switch

This switch refers to the specific SMQ/CMQ to be used for determining fatal terms for the FATAL_YN_DV column. This value should not be changed after data mart is initialized.

CUSTOM ROUTINE BEFORE STAGE TABLES POPULATION

Global switch

This switch refers to the full path of the custom routine to be executed before population of the Signal Staging Tables. If this routine fails or is not found, the ETL is not run and an error message is displayed.

CUSTOM ROUTINE BEFORE REPORTING TABLES POPULATION

Global switch

This switch refers to the full path of the custom routine to be executed before population of Signal Reporting Tables. If this routine fails or is not found, the ETL is not run and an error message is displayed.

CUSTOM ROUTINE AFTER REPORTING TABLES POPULATION

Global switch

This switch refers to the full path of the custom routine to be executed after population of Signal Reporting Tables. If this routine fails or is not found, the ETL is not run and an error message is displayed.

CUSTOM ROUTINE BEFORE SIGNAL HELPER TABLES POPULATION

Global switch

This switch refers to the full path of the custom routine to be executed before population of Signal Helper Tables. If this routine fails or is not found, the ETL is not run and an error message is displayed.

CUSTOM ROUTINE AFTER SIGNAL HELPER TABLES POPULATION

Global switch

This switch refers to the full path of the custom routine to be executed after population of Signal Helper Tables. If this routine fails or is not found, the ETL is not run and an error message is displayed.

CUSTOM ROUTINE AFTER ETL

Global switch

This switch refers to the full path of the custom routine to be executed after Initial/Incremental ETL (post ETL commit). If this routine fails or is not found, the ETL is not run and an error message is displayed.

MISSING_CODE_DISPLAY_VALUE

Global switch

This switch refers to the missing display value corresponding to the code.

6 Upgrading Argus Mart

Argus Mart upgrade process has been moved from Schema Creation Tool to Liquibase.Liquibase is a refactoring tool that is utilized to enable a silent installation.

To upgrade Argus Mart Database:

1. Log in to Argus Mart Web Server.

2. Navigate to Programs > Oracle > Argus Mart > Database > DBInstaller.

3. Open DBInstaller.properties file.

4. Modify the following parameters:

- Mart Database:

- db_connect_string—connects to the Argus Mart database.
Syntax: db_connect_string=<host>:<port>/<SID>

For example, db_connect_string=server.xx.us.com:1521/AM811MT

- dba_user—specifies the name of the Install user to run Argus Mart Liquibase Install.
See Section 2.2.3, "Creating the Install User."

- Mart User of each schema, where password is optional:

- appschema_sm_mart

- appschema_sm_stage

- appschema_sm_app

- appschema_etl_user

- appschema_rls_user

- appschema_bi_user

- Safety Database:

- safety_db—Argus Safety database instance name

- safety_ro_user—Argus Mart read-only user created in Argus Safety

Note:
For more information on these parameters, see Section 2.1, "Overview of the dbinstaller.properties file."

5. From the command prompt, go to DBInstaller directory.

6. Type dbinstaller.bat, and press Enter.
The Liquibase upgrade begins, and the parameters (as entered) appear on the command prompt screen with password in the hidden mode (****).

7. Keep monitoring the Liquibase progress by querying the Liquibase Log table mart_dbchangelog, created in AM_MART_USER schema.

8. When the process is complete, a confirmation message appears with the latest version of Argus Mart.

6.1 Post-upgrade Steps

1. Validate Schema using the schema validation control file VLDN_APR_AM_8.1.1.CTL.
See Section 2.2.5, "Validating the Schema."

2. Recreate the Safety RO User of Mart before execution of the next Incremental ETL.
See Section 2.2.2, "Creating User for the Argus Safety Database."

7 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 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:

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

7.1 Managing Initial ETL Process: ODI Studio

7.1.1 Running the ETL

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_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.

6. Right-click the LP_INI_AM option.
A drop-down menu appears.

Figure 7-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 7-3 Status of the Load Plan

7.1.2 Stopping the ETL

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 7-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 7-5 Stopped Initial ETL Session

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

7.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.

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 7-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 7-7 Restarted Load Plan

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

Figure 7-8 Completed Load Plan

7.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.

7.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 7.1.3, "Restarting the ETL" for the step-by-step procedure to continue the failed Initial ETL from the failed step.

7.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 7-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 7-10 Select Statement 2 to Verify Successful Execution

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.

7.2 Monitoring Initial ETL Process: ODI Studio

7.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 7-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 7-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.

7.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 7-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 7-14 Viewing Completed Steps in the ETL Process

7.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 7-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 7-16 Viewing the Failed Step for the ETL Process

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

Figure 7-17 Viewing the Error Message

For more information about the error message, click Execute.

Figure 7-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 7-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;

7.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 7-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 7-21 Viewing the Steps of the Restarted ETL

7.3 Managing Initial ETL Process: ODI Console

7.3.1 Running the Initial ETL

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 7-22 Oracle Data Integrator Console Screen

3. Select the Browse tab in the left pane.

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

Figure 7-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 7-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 7-25 Status of the Load Plan

7.3.2 Stopping the Initial ETL

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 7-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 7-27 Stopped Initial ETL Session

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

7.3.3 Restarting the Initial 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 7-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.

7.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.

7.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 7.3.3, "Restarting the Initial ETL" for the step-by-step procedure to continue the failed Initial ETL from the failed step.

7.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 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.

7.4 Monitoring Initial ETL Process: ODI Console

7.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_ARGUSMART811.

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

Figure 7-29 Navigating to the Load Plans

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

Figure 7-30 Viewing the Steps of the Load Plan

7.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 7-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 7-32 Viewing Completed Steps in the ETL Process

7.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 7-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 7-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 7-35 Viewing the Stopped Session

The error details appears in the right pane.

Figure 7-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 7-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;

7.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 7-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 7-39 Viewing the Steps of the Restarted ETL

7.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 12.3, "Managing Incremental ETL Process"

- ODI Console, see Section 13.3, "Managing Incremental ETL Process"

8 Uninstalling the Argus Mart Application

This section of the guide introduces you to the procedure to uninstall the Argus Mart application.

To uninstall the Argus Mart application, execute the following steps:

1. Double-click Setup.exe to open the Oracle Universal Installer, available at the following location:
<ArgusMart_HOME>\Disk1\install

The Welcome screen appears.

2. Click Deinstall Products.
The Inventory screen appears.

3. In Contents tab, expand Independent Products.
The list of Independent Products appears.

4. Select Oracle Argus Mart <version number> check box.
Note the installation location displayed under the Product Information frame.

For example: Location C:\ArgusMart811\oracle.hsgbu.am specifies installation path as C:\ArgusMart811

5. Click Remove.
A confirmation message appears.

6. Click Yes.
A progress bar appears and subsequently removes Argus Mart from the list of Independent Products.

7. Click Close to exit from the Inventory screen.

8. Click Cancel in the Oracle Universal Installer screen to exit.

9. Delete the folder, where the Argus Mart was installed, from the local file system.
Example: C:\AM

10. Navigate to start > All Programs > Oracle > Oracle Argus Mart.

11. Right-click Oracle Argus Mart.
A drop-down menu appears.

Figure 8-1 Deleting Oracle Argus Mart through Start Menu

12. Click Delete.

13. To Remove Database Components for Argus Mart:

1. Remove Database Schemas from Argus Mart Database.
Connect to SYSTEM or DBA user of Argus Mart Database, and execute the following commands to drop Argus Mart Users, Tablespaces, and other objects.

connect SYSTEM/<password>@<ArgusMartDB>

DROP USER "AM_APP_USER" CASCADE;
DROP USER "AM_BI_USER" CASCADE;
DROP USER "AM_ETL_USER" CASCADE;
DROP USER "AM_MART_USER" CASCADE;
DROP USER "AM_STAGE_USER" CASCADE;
DROP USER "AM_VPD_USER" CASCADE;

DROP ROLE "AM_VPD_ADMIN_ROLE";

DROP PUBLIC SYNONYM "RM_CMN_PROFILE_GLOBAL";

ALTER TABLESPACE "AM_APP_DATA_01" COALESCE;
ALTER TABLESPACE "AM_APP_INDEX_01" COALESCE;
ALTER TABLESPACE "AM_APP_LOB_01" COALESCE;
ALTER TABLESPACE "AM_MART_DATA_01" COALESCE;
ALTER TABLESPACE "AM_MART_INDEX_01" COALESCE;
ALTER TABLESPACE "AM_MART_LOB_01" COALESCE;
ALTER TABLESPACE "AM_STAGE_DATA_01" COALESCE;
ALTER TABLESPACE "AM_STAGE_INDEX_01" COALESCE;
ALTER TABLESPACE "AM_STAGE_LOB_01" COALESCE;

DROP TABLESPACE AM_APP_DATA_01 INCLUDING CONTENTS;
DROP TABLESPACE AM_APP_INDEX_01 INCLUDING CONTENTS;
DROP TABLESPACE AM_APP_LOB_01 INCLUDING CONTENTS;
DROP TABLESPACE AM_MART_DATA_01 INCLUDING CONTENTS;
DROP TABLESPACE AM_MART_INDEX_01 INCLUDING CONTENTS
DROP TABLESPACE AM_MART_LOB_01 INCLUDING CONTENTS;
DROP TABLESPACE AM_STAGE_DATA_01 INCLUDING CONTENTS
DROP TABLESPACE AM_STAGE_INDEX_01 INCLUDING CONTENT
DROP TABLESPACE AM_STAGE_LOB_01 INCLUDING CONTENTS;

2. Remove Argus Mart Schema on Argus Safety Database (AS_MART_USER) from Argus Safety Database created for Argus Mart.
Connect to SYSTEM or DBA user of Argus Safety Database.

connect SYSTEM/<password>@<ArgusSafetyDB>
DROP USER "AS_MART_USER" CASCADE;

14. Remove the TNS entry of the Argus Mart database from the given Oracle Home path (see Section 2.2 > Step 6) located at
..\network\admin\tnsnames.ora

15. Restart the system.

Note:
If you are re-installing Argus Mart on the same server, you must provide the same folder path that was specified during the previous installation process.
For example, if you installed Argus Mart at the C:\AM location and uninstall it using the steps mentioned above, you must enter the same folder path (for example, C:\AM) that was entered in the previous installation process.

Part II

Administration

This part of the Oracle Argus Mart Installation and Administration Guide describes administrative tasks that enables you to manage Oracle Argus Mart.

9 Setting Context in Multi-tenant Environment

In a multi-tenant setup, you can view only one enterprise data at a time for which context has been set.

To set the context for an enterprise, execute the following steps:

1. Connect to the Argus Mart User (AM_MART_USER).

2. Execute the following command:

pkg_rls.set_context(:LoginUserName, :Enterpriseid, 'ARGUS_MART', NULL);

Where,

- LoginUserName refers to the User Name

- Enterpriseid refers to the ID for the enterprise

- ARGUS_MART refers to the Application Name

This completes the steps to set the context for the enterprise.

Example 9-1 Setting Context for an Enterprise

Execute pkg_rls.set_context('admin',3,'ARGUS_MART',NULL);

10 Secure Unblinding in Argus Mart

This chapter explains the concept of Blinded Security for certain table columns in Argus Mart for a drug study.

The value for some of the table columns in Argus Mart is dependent upon the selection of Business Configuration > Studies Configuration > Study is eligible for Unblinding checkbox, in Argus Safety.

If the this checkbox is selected, the actual values for all the blinded columns is displayed in Argus Mart tables.

However, if this checkbox is not selected, the actual values for all the blinded columns are replaced by NULL, Blinded, or any other value in the Argus Mart tables.

In case of the Reporting Mart, there are three views which comprise the Blinded information: v_rm_su_case_product, v_rm_su_case_prod_drugs, and v_rm_su_case_dose_regimens. These views display the actual data only if the Study is eligible for Unblinding checkbox is selected.

Similarly, in case of a Signal Mart, there are certain table columns which comprise the Blinded information. The name of these columns end with _su, which represents Secure Unblinding. These table columns display the actual data only if the Study is eligible for Unblinding checkbox is selected.

11 Rebuilding a Case in Argus Mart

During the incremental ETL execution, you may need to re-populate certain cases from the scratch due to DLP data corruption or migration of cases in chunk.

To rebuild the entire information for the selected cases:

1. Specify ENTERPRISE_ID/CASE_ID of such cases in the table ETL_MANUAL_RBLD_CASE_LIST.

2. During the Incremental ETL, these cases will be deleted from Reporting Mart (RM) and Signal Mart (SM) tables, and rebuilt from the scratch.
Note that the cases that are not part of this table, will be processed only for delta data as before.

3. When a case that is rebuilt has an effective start date earlier than the current value in switches START_DATE_CURRENT_DATA_SUPPORT and START_DATE_LOCKED_REVISION_SUPPORT, these switch values will be over-written with the rebuild case effective start date.

Note:
When the case is rebuilt, any reference data change that is present in the case revisions will be lost.

Recommendation:
Oracle recommends to use this feature cautiously and on need basis only.

12 Incremental ETL: ODI Studio

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

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.

Figure 12-1 Scheduling ETL

The Load Plan Scheduling dialog box appears.

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 7.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.

12.3.1 Running the Incremental ETL

1. Open the Oracle Data Integrator Studio, and click Connect to Repository.
The Oracle Data Integrator Login dialog box appears.

2. In the Oracle Data Integrator Login window:

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.

4. Click OK.
The Oracle Data Integrator screen.

3. From the left pane, select the Operator tab.

4. Expand the Load Plans and Scenarios section.

Figure 12-3 Load Plans and Scenarios

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

5. Right-click the LP_INC_AM option.
The drop-down menu appears.

Figure 12-4 Executing the Incremental ETL

6. Click Run.
The Start Load Plan dialog box appears.

Figure 12-5 Start Load Plan Window

7. 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 a level.

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

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.

Figure 12-6 Status of the Load Plan

12.3.2 Stopping the Incremental ETL

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-7 Stopping the Incremental ETL

2. Select Stop Normal. This displays the Stop Load Plan dialog box.

Figure 12-8 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-9 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 Incremental ETL

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

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-10 Restarting the Incremental ETL

2. Click Restart. This displays the 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.

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, which signifies that the ETL is in progress, as highlighted in the following figure:

Figure 12-11 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-12 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-13 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.

Figure 12-14 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 Incremental 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.

Figure 12-15 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 12-16 Select Statement 2 to Verify Successful Execution

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

13 Incremental ETL: ODI Console

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

13.1 Scheduling an ETL

The ETL can be scheduled through ODI Studio only.

To schedule an ETL, see Section 12.1, "Scheduling Incremental ETL".

13.2 Monitoring Incremental ETL Process

The process of monitoring the Incremental ETL using the Oracle Database Integrator Console 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 Console, see Section 7.4, "Monitoring Initial ETL Process: ODI Console."

13.3 Managing Incremental ETL Process

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

13.3.1 Running the Incremental ETL

1. Open the Oracle Data Integrator Console. This displays the Oracle Data Integrator Console Sign In window.

2. In the Oracle Data Integrator Sign In window:

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

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

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

4. Click Sign In. This displays the Oracle Data Integrator Console Screen, as shown in the following figure:

Figure 13-1 Oracle Data Integrator Console Screen

3. Select the Browse tab in the left pane.

4. Expand the Runtime folder, and navigate to Runtime > Scenarios/Load Plans > Folder > LP_ARGUSMART811 > LP_INC_AM, as highlighted in the following figure:

Figure 13-2 Scenarios/Load Plans

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

5. Click Execute, as highlighted in the following figure:

Figure 13-3 Executing the Incremental ETL

This displays the Execute Load Plan window, as shown in the following figure:

Figure 13-4 Execute Load Plan Window

6. In the Execute Load Plan window:

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

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

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

4. Click Execute. This displays the Information dialog box with the Load Plan Execution submitted successfully confirmation message.

7. Click OK.
You can verify the status of the ETL process by expanding the Load Plan Executions folder in the Sessions/Load Plan Executions section. 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 13-5 Status of the Load Plan

13.3.2 Stopping the Incremental ETL

1. Select the Load Plan, which you want to stop, by expanding the Load Plan Executions folder of the Sessions/Load Plan Executions section and click Stop, as shown in the following figure:

Figure 13-6 Stopping the Incremental ETL

This displays the Stop Load Plan Execution dialog box.

Figure 13-7 Stop Load Plan Execution Dialog Box

2. Select Normal from the Stop Type drop-down list.

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

4. Click Stop. This displays the Information dialog box with the Load Plan was Stopped Successfully confirmation message.

5. Click OK.
You can verify the status of the ETL process by navigating to the Load Plan Executions folder in the Sessions/Load Plan Executions section. 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 13-8 Stopped Incremental ETL Session

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

13.3.3 Restarting the Incremental ETL

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

1. Select the Load Plan, which you want to restart, in the Load Plan Executions folder of the Sessions/Load Plan Executions section and click Restart, as shown in the following figure:

Figure 13-9 Restarting the Incremental ETL

This displays the Restart Load Plan Execution dialog box.

Figure 13-10 Restart Load Plan Execution Dialog Box

2. Select OracleDIAgent from the Physical Agent drop-down list.

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

4. Click Restart. This displays the Information dialog box with the Load Plan restarted message.

5. Click OK.
This adds another Load Plan, with the same name as that of the stopped ETL, in the Load Plan Executions folder of the Sessions/Load Plan Executions section. However, this instance of the Load plan is in Green color, which signifies that the ETL is in progress.

13.3.4 Processing a Stopped or a Failed ETL

The processing of a stopped or a failed ETL in ODI Console is same as the processing of a stopped or a failed ETL in ODI Studio.

For detailed information, see Section 12.3.4, "Processing a Stopped or a Failed ETL".

14 Re-initializing the ETL Process

When you have successfully executed the Initial ETL process on a database, you cannot execute it again till the time you reset the mart environment. To facilitate this, you need to execute a Re-initial script. It is a Batch file, which re-initializes the database, so that you can run the Initial ETL on the database again.

Note:
When you run the re-initial ETL, the data of Argus Mart is truncated and reloaded in RM and SM tables. Revision created due to reference data changes will be lost.

To execute the Re-initial script, execute the following steps:

1. Double-click the am_environment_reset.bat file available at the following path:
...\ArgusMart\Database\Utils\am_environment_reset.bat

This displays a warning message, which serves as a confirmation from you that you want to reset the Mart environment.

2. Enter Y, which represents Yes, in the Do You Want to Continue (Y/N)? field, if you want to continue with resetting the Mart environment to be able to run the Initial ETL on the database again.
Or

Enter N, which represent No, if you want to quit the process of resetting the Mart environment.

If you have entered Y, the Reset Environment to Re-Run Initial ETL Screen is displayed.

3. Enter the TNS Name to connect to the Argus Mart database in the Enter Database TNS field and press Enter.

4. Enter the following parameters for the user who have administrator rights to access the ETL process.

Note:
This user is referred to as Argus ETL User. If the user is not the database owner with the administrator rights for the ETL process, the system displays an error message.

1. Enter the Argus Mart Database Owner in the Enter Argus ETL User field, and press Enter.

2. Enter the password in the Enter Password for User field, and press Enter.

3. Enter the name of the log file in the Enter log file name field, and press Enter.
The system displays a Connecting status message and once connected displays Connected.

5. Press Enter to complete writing the logs.

6. Press Enter to exit from the window.

15 Troubleshooting

This chapter lists some of the error messages that might be displayed while working with Argus Mart, the cause for those messages, and the resolution.

The following is the list of error messages:

- Dictionary Reload Error

- ETL Execution Pre-Requisite Check Failed Error

- FR Consistency Log Error

- MedDRA Mismatch Error

- Multiple Enterprise Creation Messages

- SMQ-CMQ Loop Error

15.1 Dictionary Reload Error

Error Message

The following error message is displayed during the ETL process, whenever the dictionary is reloaded in Argus Safety:

Figure 15-1 Dictionary Reload Error Message

You can also view this error message by logging on to the SQL Developer as the AM_MART_USER, as shown in the following figure:

Figure 15-2 Dictionary Reload Error Message: SQL Developer

Cause of Error

Whenever there is a dictionary reload in Argus Safety, some of the required privileges for the Argus Safety user, which you created, using the Schema Creation Tool during the Installation Process, are lost.

Resolution

You must give the required privileges to the Argus Safety user. To do so, click the Argus User Creation link on the Schema Creation Tool, select the user that you created during the installation process in the Argus Safety User Creation dialog box, enter the name of the log file and click OK to give the required privileges to the user.

You can refer to Section 3.5.1, Creating User for the Argus Safety Database, for more information.

15.2 ETL Execution Pre-Requisite Check Failed Error

Error Message

There are some Pre-requisite checks that you must do before the execution of the ETL. The following is the error message, which is displayed if any of the Pre-requisites checks are not done:

Figure 15-3 Pre-Requisite Check Failed Error

Cause of Error

The following are the possible causes of this error message:

- If you are trying to run the Initial ETL on a database again without executing the Re-initial script.

- If you are trying to execute the Incremental ETL prior to the Initial ETL.

- If you have not configured the First Human Language Profile Switch using the Argus Safety console for the enterprises configured in Argus Mart. You must not leave the value for the First Human Language Profile Switch, as Blank.

- If the previous instance of the ETL is still not complete for the Load Plan.

Resolution

To resolve this error message, you must:

- Ensure that you run the Initial ETL on a database again only if you have already executed the Re-initial script. For more information on Re-initial script, see Chapter 14, "Re-initializing the ETL Process."

- If you are trying to run the Incremental ETL on a database, ensure that the Initial ETL has already been executed on it.

- Ensure that you have not left the value for the First Human Language Profile Switch for the enterprises configured in Argus Mart, as Blank.

- Ensure that the Previous Instance of the ETL process is complete. You can either resume the ETL process if it is in Stopped state, or you can execute the re-initial script on the database and restart the ETL process from the first step.
For more information on resuming or restarting the ETL process, see Chapter 7, "Extracting, Transforming, and Loading Data."

15.3 FR Consistency Log Error

Error Message

You can run the following query using the SQL developer to view the Flexible Re-categorization (FR) Consistency warning message:

SELECT enterprise_id, code_list_id, decode_context, code, fr_type, log_message, log_date_time FROM etl_fr_consistency_log ORDER BY enterprise_id, code_list_id

The following is the error message:

Figure 15-4 FR Consistency Error

Cause of Error

The display value for a codelist is NULL in the rm_code_list_detail_discrete table in Argus Safety.

Resolution

You must update the value for the codelist in the rm_code_list_detail_discrete table in Argus Safety and re-run the ETL.

15.4 MedDRA Mismatch Error

Error Message

You can run the following query using the SQL developer to view the MedDRA mismatch warning message in the etl_mart_log table:

SELECT * FROM am_mart_user.etl_mart_log WHERE table_name = 'p_check_signal_meddra_schema' ORDER BY 1 DESC;

The following is the error message:

Figure 15-5 MedDRA Mismatch Error

Cause of Error

During the ETL process, a check is done between the Argus Safety and the Argus Mart database to ensure that they are using the same MedDRA version. This warning message is displayed when these values do not match.

Resolution

You must update the value for the MedDRA version in the SIGNAL_MEDDRA_VER table to ensure that it matches with the Argus Safety database value.

15.5 Multiple Enterprise Creation Messages

The following is the list of validation messages that are displayed while creating multiple enterprises in Argus Mart:

- Source Enterprise Does Not Exist In Mart

- Enterprise Does Not Exist In Argus Safety

- Enterprise Does Not Exist For Configuration In Mart

15.5.1 Source Enterprise Does Not Exist In Mart

Error Message

Given Source enterprise does not exist in Mart. Close the window and run application again.

Cause of Error

While creating multiple enterprises in Argus Mart, you must enter an enterprise name which can be used as a template to create other enterprises. This enterprise is referred to as the Source Enterprise.

If the name of the Source Enterprise that you have entered while creating multiple enterprises does not exist in Argus Mart, this validation message is displayed.

Resolution

The name of the Source Enterprise that you enter while creating multiple enterprises must exist in Argus Mart.

15.5.2 Enterprise Does Not Exist In Argus Safety

Error Message

From the list provided, no enterprise exists in Argus Safety. Close the window and run application again.

Cause of Error

The name of the enterprises that you enter while creating multiple enterprises in Argus Mart must also exist in the Argus Safety database.

If these enterprise names do not exist in Argus Safety, this validation message is displayed.

Resolution

You must ensure that the name of the enterprises that you enter while creating multiple enterprises in Argus Mart exist in the Argus Safety database.

15.5.3 Enterprise Does Not Exist For Configuration In Mart

Error Message

From the list provided, no enterprise is valid for configuration in Mart. Close the window and run application again.

Cause of Error

This validation message is displayed if all the enterprise names that you have entered on the Multiple Enterprise Creation screen already exist in the Argus Mart database.

Resolution

You must ensure that the enterprise names that you want to create in Argus Mart using the Multiple Enterprise Creation screen do not already exist in Argus Mart.

15.6 SMQ-CMQ Loop Error

Error Message

Error in p_set_Child_Record while processing term code self referencing parent child relationship leading to an infinite loop

Cause of Error

There is an SMQ, which is a Parent in the hierarchy, and has also been referenced as a Child in the hierarchy. This is termed as the Self Referencing Parent Child relationship, which leads to an infinite loop.

Resolution

You must ensure that an SMQ, which serves as a Parent in the hierarchy, must not also be present as a Child in the hierarchy.

A Configuring Standalone Installation Topology for Standalone Agent

A.1 Pre-requisite

Oracle Data Integrator Master and Work Repository Schema are already created.For more details, see

https://docs.oracle.com/middleware/1212/odi/ODING/create_schemas.htm#ODING860 > Chapter 3 Creating the Oracle Data Integrator Master and Work Repository Schema.

A.2 To configure a standalone agent:

1. Start the configuration wizard.
To begin domain configuration, navigate to the ORACLE_HOME/oracle_common/common/bin directory, and start the WebLogic Server Configuration Wizard.

- On Unix—./config.sh

- On Windows—config.cmd

The Configuration Wizard appears.

2. Create Domain—Select Create a new Domain, verify the domain location, and click Next.

3. Templates—From the list of available templates, select Oracle Data Integrator - Standalone Agent - 12.2.1.0 [odi], and click Next.

4. JDK Selection—Select the JDK installed on Host, and click Next.

5. Database Configuration Type—Select RCU.
Enter the Repository Details, and click Get RCU Configuration.

When the Connection Result log displays the message Successfully Done, click Next.

6. Component Datasources—The details of schemas created using RCU appears. Verify the schema details, and click Next.

7. JDBC Test—Test the database connection, and click Next.

8. System Components—Make sure that the System Components is same as the name of the physical agent present in the ODI Studio. That is, the Physical Agent Name in the ODI Studio must be same as System Component (Agent Name).
Click Next.

9. ODI Server Configuration—Select Server Listen Address as the IP address of the Host
Enter Server Listen Port as the port number defined for the physical agent in the ODI Studio.

Click Next.

10. Node Manager—Select Per Domain Custom Location, verify the path, and click Next.

11. Configuration Summary—The Agent Name appears under System Component > ODI.
Click Create.

12. Configuration Progress—The progress of the domain creation process appears.
On successful completion of the Agent, a message stating Domain Created Successfully. appear.

Click Next.

13. End of Configuration—Domain Configuration Succeeded appears with the Domain Name and the Domain Location.
Click Finish to exit the wizard.

B Configuring Standard Installation Topology for Java EE Agent

B.1 Pre-requisite

Oracle Data Integrator Master and Work Repository Schema are already created.For more details, see

https://docs.oracle.com/middleware/1212/odi/ODING/create_schemas.htm#ODING860 > Chapter 3 Creating the Oracle Data Integrator Master and Work Repository Schema.

B.2 To configure a Java EE agent:

1. Start the configuration wizard.
To begin domain configuration, navigate to the ORACLE_HOME/oracle_common/common/bin directory, and start the WebLogic Server Configuration Wizard.

- On Unix—./config.sh

- On Windows—config.cmd

The Configuration Wizard appears.

2. Create Domain—Select Create a new Domain, verify the domain location, and click Next.

3. Templates—From the list of available templates, select the following templates, and click Next.

- Oracle Enterprise Manager - 12.2.1 [em]

- Oracle Data Integrator - Console - 12.2.1.0 [odi]

- Oracle Data Integrator - Agent - 12.2.1.0 [odi]

- Oracle Data Integrator - Agent Libraries - 12.2.1.0 [odi]

4. Application Location—Verify the Domain, and the Application location, and click Next.

5. Domain Mode and JDK Selection—Specify the Username and Password for the Administrator Account, and click Next.

6. Database Configuration Type—Select RCU.
Enter the Repository Details, and click Get RCU Configuration.

When the Connection Result log displays the message Successfully Done, click Next.

7. Component Datasources—The details of schemas created using RCU appears. Verify the schema details, and click Next.

8. JDBC Test—Test the database connection, and click Next.

9. Credentials—Enter the username and password, and click Next.

10. Advanced Configuration—Select the following, and click Next.

- Administration Server

- Node Manager

- Managed Server, Clusters and Coherence

- Deployment and Services

11. Administration Server—Change the Listen Address to the IP address of the host.

12. Node Manager—Select the Node Manager Type as Per Domain Custom Location.
For Node Manager Credentials, enter the Username and Password, and click Next.

13. Managed Server—Specify the managed server details, and click Next.

- Server Name: <Any Name>

- Port: <Port of the Agent as configured in the ODI Studio>

- Server Groups: Select JFR-MAN-SVR

14. (Optional) Clusters—Specify the cluster details, if required, and click Next.

15. Coherence Cluster—Make no changes, and click Next.

16. Machines—Click Add, enter the following details, and click Next.

- Enter Name: <Any Name>

- Node Manager Listen Address: Select IP Address of the Host

- Node Manager Listen Port: Any unused Port

17. Assign Server to Machines—Click > button to move the Admin Server and ODI_Server to the Machine, and click Next.

18. Deployment Targeting—Click > button to move the Deployments to Targets.
Similarly, select the Library and Application folder (one at a time), and move them to the Targets.

Click Next.

19. Service Targeting—Click > button to move Services into Targets.
Move all the available services one by one into Targets.

Click Next.

20. Configuration Summary—Verify the details, and click Create.

21. Configuration Progress—The progress of the domain creation process appears.
On successful completion, click Next.

22. End of Configuration—A message appears as Oracle Weblogic Server Configuration Completed with the Domain Location and the URL for the Admin Server.
(Optional) To start the Admin Server immediately after exiting the wizard, select the check box Start Admin Server.

Click Finish to exit the wizard.

Oracle Legal Notices

Copyright Notice

Copyright © 1994-2017, Oracle and/or its affiliates. All rights reserved.

License Restrictions Warranty/Consequential Damages Disclaimer

This software and related documentation are provided under a license agreement containing restrictions on use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license, transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverse engineering, disassembly, or decompilation of this software, unless required by law for interoperability, is prohibited.

Warranty Disclaimer

The information contained herein is subject to change without notice and is not warranted to be error-free. If you find any errors, please report them to us in writing.

Restricted Rights Notice

If this is software or related documentation that is delivered to the U.S. Government or anyone licensing it on behalf of the U.S. Government, then the following notice is applicable:

U.S. GOVERNMENT END USERS: Oracle programs, including any operating system, integrated software, any programs installed on the hardware, and/or documentation, delivered to U.S. Government end users are "commercial computer software" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, use, duplication, disclosure, modification, and adaptation of the programs, including any operating system, integrated software, any programs installed on the hardware, and/or documentation, shall be subject to license terms and license restrictions applicable to the programs. No other rights are granted to the U.S. Government.

Hazardous Applications Notice

This software or hardware is developed for general use in a variety of information management applications. It is not developed or intended for use in any inherently dangerous applications, including applications that may create a risk of personal injury. If you use this software or hardware in dangerous applications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure its safe use. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of this software or hardware in dangerous applications.

Trademark Notice

Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

Intel and Intel Xeon are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks are used under license and are trademarks or registered trademarks of SPARC International, Inc. AMD, Opteron, the AMD logo, and the AMD Opteron logo are trademarks or registered trademarks of Advanced Micro Devices. UNIX is a registered trademark of The Open Group.

Third-Party Content, Products, and Services Disclaimer

This software or hardware and documentation may provide access to or information about content, products, and services from third parties. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to third-party content, products, and services unless otherwise set forth in an applicable agreement between you and Oracle. Oracle Corporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to your access to or use of third-party content, products, or services, except as set forth in an applicable agreement between you and Oracle.

Pre-General Availability Draft Documentation Notice

If this document is in public or private pre-General Availability status:

This documentation is in pre-General Availability status and is intended for demonstration and preliminary use only. It may not be specific to the hardware on which you are using the software. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to this documentation and will not be responsible for any loss, costs, or damages incurred due to the use of this documentation.

Private Pre-General Availability Draft Documentation Notice

If this document is in private pre-General Availability status:

The information contained in this document is for informational sharing purposes only and should be considered in your capacity as a customer advisory board member or pursuant to your pre-General Availability trial agreement only. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described in this document remains at the sole discretion of Oracle.

This document in any form, software or printed matter, contains proprietary information that is the exclusive property of Oracle. Your access to and use of this confidential material is subject to the terms and conditions of your Oracle Master Agreement, Oracle License and Services Agreement, Oracle PartnerNetwork Agreement, Oracle distribution agreement, or other license agreement which has been executed by you and Oracle and with which you agree to comply. This document and information contained herein may not be disclosed, copied, reproduced, or distributed to anyone outside Oracle without prior written consent of Oracle. This document is not part of your license agreement nor can it be incorporated into any contractual agreement with Oracle or its subsidiaries or affiliates.

Documentation Accessibility

For information about Oracle's commitment to accessibility, visit the Oracle Accessibility Program website at http://www.oracle.com/pls/topic/lookup?ctx=acc&id=docacc.

Access to Oracle Support

Oracle customers that have purchased support have access to electronic support through My Oracle Support. For information, visit http://www.oracle.com/pls/topic/lookup?ctx=acc&id=info or visit http://www.oracle.com/pls/topic/lookup?ctx=acc&id=trs if you are hearing impaired.