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

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

Figure 5-1 Installation Progress: Configuring ODI Settings

This chapter comprises the following sub-sections:

Minimum Components Required
Before Configuring ODI Settings
Creating Master Repository
Creating Work Repository
Importing Argus Mart Schema Object
Creating and Testing Data Server Connection
Creating New Physical Schema
Validating Load Plan
Managing the ODI Agent
Executing Steps of a Load Plan in Parallel

5.1 Minimum Components Required

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

ODI Studio
ODI Agent

5.2 Before Configuring ODI Settings

There are certain tasks that you need to execute before configuring the ODI settings. All these tasks are explained in this section.

This section comprises the following sub-sections:

Creating the Database Users for Master and Work Repositories
Granting Privileges to the Database Users

5.2.1 Creating the Database Users for Master and Work Repositories

You must create two separate database users for Master and Work repositories, and grant them the necessary privileges. You need to create these users in the Argus Mart instance that maintains the ODI metadata.

To do so, you must log on to the SQL developer as a SYS user and execute the following commands to create the users:

CREATE USER <odi_master> IDENTIFIED BY <password>;
CREATE USER <odi_work> IDENTIFIED BY <password>;

Where <odi_master> refers to the Master Repository User Name and <odi_work> refers to the Work Repository User Name.

Oracle Recommends:

While creating ODI Master and Work schemas, the database administrator must create a new default tablespace for these schemas. In addition, the administrator must ensure that no objects of ODI Master and Work schemas exist in any other tablespace.

5.2.2 Granting Privileges to the Database Users

After you have created two separate database users for Master and Work repositories, you must grant them the necessary privileges using the following commands:

GRANT RESOURCE, CREATE SESSION, CONNECT TO odi_master;
GRANT RESOURCE, CREATE SESSION, CONNECT TO odi_work;
GRANT EXECUTE ON DBMS_LOCK TO odi_work;

Where odi_master refers to the Master Repository User Name and odi_work refers to the Work Repository User Name.

5.3 Creating Master Repository

To create the Master Repository, execute the following steps:

Open the ODI, and select File > New.

The New Gallery dialog box appears.
Click OK.

The Master Repository Creation Wizard screen appears with the Repository Connection selected in the left pane.
In the Database Connection section:
1. In the JDBC Driver field, enter the required JDBC Driver.
  
  You may click the Search icon next to the JDBC Driver field to search for the available list of drivers.
2. In the JDBC Url field, enter the required JDBC URL.
  
  You may click the Search icon close to the JDBC Url field to search for the available list of URL.
3. In the User field, enter the name of the ODI Master Repository User Name.
  
  You have already created the ODI Master Repository User Name (for example, odi_master) in the Section 5.2.1, "Creating the Database Users for Master and Work Repositories".
4. In the Password field, enter the password for the ODI Master Repository User.
  
  You have already created the ODI Master Repository Password in the Section 5.2.1, "Creating the Database Users for Master and Work Repositories".
5. In the DBA User field, enter the name of the ODI DBA User Name.
6. In the Password field, enter the password for the ODI DBA User.
7. In the Repository Configuration section, specify the ID for the Master Repository.
  
  For example, 386. You must not enter 588 in this field as it would result in an error message while importing the AM.zip file. This ID has already been used while creating the AM.zip file, which you will be importing in the subsequent sections of this guide.
8. Click Test Connection.
  
  Figure 5-2 Master Repository Creation Wizard
  
  If successful, the Information dialog box appears with the Successful Connection message.
9. Click OK.
Click Next.

The Authentication screen appears.

Figure 5-3 Authentication Screen
On the Authentication screen:
1. In the Supervisor Password field, enter the password for the SUPERVISOR user.
  
  The Password that entered in this field will be used later in the configuration process.
2. In the Confirm Password field, re-enter the password.
3. Click Next.
  
  The Password Storage screen appears.
  
  Figure 5-4 Password Storage Screen
Click Finish.

A message appears stating that ODI is creating your master repository.

Subsequently, the Information dialog box appears with the confirmation of the successful Master Repository Creation. You can now connect to the Master Repository and create a Work Repository in the Topology Navigator, under the Repositories accordion.
Click OK to complete the creation of the Master Repository.

Once you have created the Master Repository, you also need to create a login name for the repository. See Section 5.3.1, "Creating Repository Login".

5.3.1 Creating Repository Login

To create a login name for the Master or Work Repository, execute the following steps:

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

Figure 5-5 Creating Login for Repository

The Repository Connection Information screen appears.

Figure 5-6 Repository Connection Information
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.
  
  This password was specified in step 5 (a) of the steps to create the Master Repository section.
4. In the Database Connection section, enter the Master Repository User Name and Password.
  
  You have already created the ODI Master Repository User Name (for example, odi_master) and Password in Section 5.2.1, "Creating the Database Users for Master and Work Repositories".
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, AM_Work_Repository).
  
  See Section 5.4, "Creating Work Repository".
  
  You may also click the Search icon next to the Work Repository name text box.
7. Click OK.
  
  The login name is created.

5.4 Creating Work Repository

To create the Work Repository, execute the following steps:

Open the ODI, and connect to the repository using the Master Repository credentials that you have just created, as mentioned in the previous section.
Select the Topology tab.
In the Repositories section, right-click Work Repositories and select New Work Repository.

Figure 5-7 New Work Repository Option

The Specify ODI Work Repository connection properties screen appears.

Figure 5-8 Specify ODI Work Repository connection properties screen
Enter the database details in the JDBC Driver and JDBC Url fields.

You may also click the Search icon next to the fields to search for the required JDBC Driver and JDBC URL.
In the User and Password fields, enter the Work Repository User Name and password respectively.

You have already created the ODI Work Repository User Name (for example, odi_work) in Section 5.2.1, "Creating the Database Users for Master and Work Repositories".
Click Next.

The Specify ODI Work Repository properties screen appears.

Figure 5-9 Specify ODI Work Repository properties screen
In the Id field, enter the ID for the Work Repository.

For example, 564. You must not enter 589 in this field as it would result in an error message while importing the AM.zip file. This ID has already been used while creating the AM.zip file, which you will be importing in the subsequent sections of this guide.
Enter the Name and Password for the Work Repository.

For example, AM_Work_Repository.
From the Work Repository Type drop-down list, select Development.
Click Finish.

The Starting ODI Action dialog box appears with a message stating the ODI is creating your work repository.

Subsequently, the Confirmation dialog box appears with the option to create a login for the work repository.
Click Yes if you want to create a login for the Work Repository.

If you click No, perform the steps of Section 5.3.1, "Creating Repository Login", starting Step 2.

Once done, a Work Repository is created in the Work Repositories folder of the Repositories section.

Figure 5-10 Viewing New Work Repository

After you have created the Work Repository, you also need to create a login for the repository. See Section 5.3.1, "Creating Repository Login".

5.5 Importing Argus Mart Schema Object

Once you have created the Master and Work Repositories, you can now import the AM.zip file using the following procedure:

Open the ODI, and connect to the repository using the Work Repository credentials that you have just created, as mentioned in the previous section.
From the Designer tab, click the down arrow.

A drop-down menu appears.

Figure 5-11 Import Link
Click Import.

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

The Smart Import dialog box appears.
Click the Search icon next to the File Selection field.

The Select an import file dialog box appears.
Navigate to the AM.zip file, saved at the following location:

...\ArgusMart\ODI\AM.zip
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.
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 5-12 Displaying Components Imported from the Zip File
Click Next.

The Summary screen appears with the No issues message if there are no errors in the import process.
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 5-13 Smart Import Report
Click Save.

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

The Save dialog box appears.
Navigate to the path where you want to save the report, and in the File Name field, enter the name for the report.
Click Save.

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

The Information dialog box appears with the path where the report file has been saved.
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 5-14 Verifying the Smart Import

5.6 Creating and Testing Data Server Connection

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

Select the Topology tab.

In the Physical Architecture section, under Oracle folder, double-click DS_AM_ARGUSMART.

Figure 5-15 Creating Data Server Connection

The connection details appears in the right pane, with Definition selected by default.
In the User field, enter the name of the Argus ETL user (AM_ETL_USER).

This user was created in the Section 3.4, "Creating the Database Schema".
In the Password field, enter the password for the Argus ETL User.
Select JDBC in the right pane, and enter database details of the Argus Mart schema in the JDBC Driver and JDBC Url fields, as depicted in the following figure:

Figure 5-16 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.
Click Test Connection.

A confirmation message appears to save data before testing the connection.
Click OK.

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

The Information dialog box appears with the Successful Connection message.
Click OK.

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

5.7 Creating New Physical Schema

To create a new physical schema, execute the following steps:

Select the Topology tab.

In the Physical Architecture section, under Oracle folder, right-click DS_AM_ARGUSMART.

Figure 5-17 Selecting New Physical Schema
Select New Physical Schema.

The Physical Schema screen appears, where Definition is selected by default.
From the Schema drop-down list, select the Argus ETL User (AM_ETL_USER).

This user was created in Section 3.4, "Creating the Database Schema".
From the Schema (Work Schema) drop-down list, select the Argus ETL User (AM_ETL_USER) again.

Figure 5-18 Selecting Argus ETL User in Definition Section
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 5-19 Selecting Context for the Data Server
From the Context drop-down list, select CTX_ARGUSMART.
From the Logical Schema drop-down list, select LS_AM_ARGUSMART.
Click Save on the menu bar.

The new physical schema appears under Physical Architecture section, in Oracle folder.

Figure 5-20 Viewing the New Physical Schema

5.8 Validating Load Plan

To validate the Load Plan, execute the following steps:

Select Operator tab.
In the Load Plans and Scenarios > SCN_LP_ARGUSMART80 section, double-click the LP_INI_AM Load Plan.

Figure 5-21 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 5-22 Load Plan Details
Click Validate.

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

5.9 Managing the ODI Agent

This section explains the tasks that you need to execute to manage the ODI Agent.

This section comprises the following sub-sections:

Managing the Standalone ODI Agent
Creating the Java EE Agent

5.9.1 Managing the Standalone ODI Agent

Execute the following tasks to manage the Standalone ODI Agent:

Setting up the Standalone ODI Agent
Encoding User's Password
Starting the Standalone ODI Agent

5.9.1.1 Setting up the Standalone ODI Agent

After the standalone ODI Agent is installed, you need to set it up using the following steps:

Open the ODI, and connect to the repository using the Work Repository credentials.
Navigate to Topology > Physical Architecture > Agents, and double-click PA_AM. The Agent details appears in the right pane.
In the Host field, enter the Standalone Agent IP Address.

Note:
You can change the default port for Argus Mart using this screen, if required.
Navigate to the location, where ODI is installed, and open the bin sub-folder.

Example: <ODI_AGENT_HOME>\oracledi\agent\bin
Open the odiparams.bat file in a text editor.

Edit the odiparams.bat file according to the list of changes mentioned in Table 5-1. The following are the contents of a sample odiparams.bat file:

set ODI_MASTER_DRIVER=oracle.jdbc.OracleDriver
set ODI_MASTER_URL=jdbc:oracle:thin:@<HOST>:<PORT>:<SID>
set ODI_MASTER_USER=<ODI Master Repository User Name>
set ODI_MASTER_ENCODED_PASS=<encoded password>
REM #
REM # User credentials for agent startup program
REM #
set ODI_SUPERVISOR=SUPERVISOR
set ODI_SUPERVISOR_ENCODED_PASS=<encoded password>
REM #
REM # User credentials for ODI tools
REM #
set ODI_USER=%ODI_SUPERVISOR%
set ODI_ENCODED_PASS=%ODI_SUPERVISOR_ENCODED_PASS%
REM #
REM # Work Repository Name
REM #
set ODI_SECU_WORK_REP=<Work Repository>

The following table lists the required modifications in the odiparams.bat file:

Table 5-1 Required Modifications in the odiparams.bat file

Parameter	Description
ODI_MASTER_DRIVER and ODI_MASTER_URL	Refers to the database details.
ODI_MASTER_USER	Refers to the ODI Master Repository User Name, which you have created using Section 5.2.1.
ODI_MASTER_ENCODED_PASS	Refers to the ODI Master Repository User Password, which must encode using the steps given in Section 5.9.1.2.
ODI_SUPERVISOR	Refers to the ODI SUPERVISOR User Name.
ODI_SUPERVISOR_ENCODED_PASS	Refers to the ODI SUPERVISOR User Password, which must encode using the steps given in Section 5.9.1.2.
ODI_SECU_WORK_REP	Refers to the Work Repository Name. For example, AM_Work_Repository.

5.9.1.2 Encoding User's Password

Execute the following steps to encode the ODI Master Repository and SUPERVISOR user password:

Open the command prompt screen, and change the directory to the ODI_AGENT_HOME\oracledi\agent\bin directory.

Where ODI_AGENT_HOME refers to the location, where ODI is installed.

The password information is always stored as an encrypted string in the odiparams.bat file. You need to encrypt the ODI Master Repository as well as the SUPERVISOR Password using the encode command.
Encode the ODI Master Repository User password using the encode command as follows:
```
C:\oracle\product\<version number>\Oracle_ODI_agent\oracleid\agent\bin> encode password
```
Here password refers to the password for the ODI Master Repository User.
Similarly, encode the SUPERVISOR user password using the encode command, as shown in step 2 of this procedure.

5.9.1.3 Starting the Standalone ODI Agent

After the required modifications are made to the odiparams.bat file, you can start the ODI Agent.

To start the ODI Agent:

Navigating to the bin folder using the CD command.
On the command prompt screen, execute the agent.bat file using the following command:
```
CD/d <ODI_AGENT_HOME>\oracledi\agent\bin
agent.bat -NAME=PA_AM -PORT=20588
```
Where C:\ODI_AGENT_HOME\oracledi\agent\bin refers to the local file system path where the ODI Agent is installed.

5.9.2 Creating the Java EE Agent

Create the Java EE Agent for ODI with OracleDIAgent, using the following tutorial link:

http://www.oracle.com/webfolder/technetwork/tutorials/obe/fmw/odi/odi_11g/setup_jee_agent/setup_jee_agent.htm

Note:

You must create the Java EE Agent for ODI with OracleDIAgent (Case-sensitive) as the name.

5.10 Executing Steps of a Load Plan in Parallel

The Load Plan comprises a list of steps, which can be executed in sequence or in parallel. You can reduce the time taken by the ETL process by selecting to run the steps of a Load Plan in parallel.

This section explains the procedure for parallel execution of the steps of a Load Plan. In addition, this section also suggests the list of tables of a Load Plan that you can select for parallel execution.

Note:

The steps mentioned in this section enable you to reduce the total time taken to complete the ETL process. This is not a mandatory step to configure Argus Mart.

To execute the steps of a Load Plan in parallel, execute the following procedure:

On the Designer tab, in the Load Plans and Scenarios section, double-click the LP_INI_AM Load Plan. (Figure 5-21)

The Load Plan details appears in the right pane.
Select Steps.

All the steps of a Load Plan appears.
Click the down arrow next to the + icon.

A drop-down menu appears.

Figure 5-23 Adding Parallel Step to the List of Steps
Select Parallel Step.

A parallel step is added to the existing list of steps.
Use the Navigation buttons (Up, Down, Right, and Left arrow keys) adjacent to the + icon, to move the Parallel Step, according to the requirements.

You may move all the steps that you want to execute in parallel, below the Parallel Step, and use the Right Arrow key, to enable all those steps for parallel execution.

Figure 5-24 Executing Load Plan Steps in Parallel

The AM.zip file, which you is imported, as explained in Section 5.5, "Importing Argus Mart Schema Object", has the provision to execute the Staging Case Table Truncation and Population in parallel, as highlighted in Figure 5-24.

The process of truncation comprises of various tables, which are divided into different categories. These categories are named as SCN_truncate_stage_tables_CASE01, SCN_truncate_stage_tables_CASE02, and so on. Each category contains a list of tables, which are sorted based on size. The larger tables are executed first as compared to the smaller ones.

Similarly, the process of population also consists of various tables, which are divided into different categories. These categories are named as SCN_populate_stage_tables_CASE01, SCN_populate_stage_tables_CASE02, and so on.