Oracle Data Integrator Testing Guide

ODI Setup Guide for Testing Environment
Setup Guide
Table of Contents
1. Introduction. 2
1. Acronyms & Abbreviations. 2
2. Installing ODI 3
2.1 Prerequisite. 3
2.2 Installation Steps. 3
2.2.1 Setup. 3
2.2.2 Welcome Page. 4
2.2.3 Select Installation Type. 5
2.2.4 Prerequisite Checks. 6
2.2.5 Specify Installation Location. 7
2.2.6 Repository Configuration. 8
2.2.7 Creating Agent 9
2.2.8 Installation Summary. 10
2.2.9 Installation Progress. 11
2.2.10 Configuration Process. 12
2.2.11 Installation Completed. 13
3. Configuring ODI 14
3.1 Opening ODI. 14
3.2 Defining new ODI Connection. 15
4. Importing the Master Repository. 20
5. Creating Work Repository. 24
6. Importing Work Repository. 28
7. Settings for Testing Environment 34
7.1 Creating DB Link. 34
7.2 Change Source System Schema. 34
7.3 Change Siebel Schema. 36
7.4 Change Context 38
7.5 Configure Standalone agent on Server 40
7.6 Start Standalone agent on Server 41
8. Testing Machine Setup.. 42
8.1 Import WSDL.. 42
8.2 Start Scenario. 42
8.3 Check Session Status. 43

1. Introduction

This document describes the steps to Install and configure Oracle Data Installer for loading data from SOURCE SYSTEM tables to Siebel EIM tables.

1. Acronyms & Abbreviations

ODI	Oracle Data Integrator
EIM	Enterprise Integration Manager

2. Installing ODI

2.1 Prerequisite

Below are the recommended hardware and system configuration requirements for Oracle Data Integrator Studio and Standalone Agents:

• CPU: dual-core Pentium, 1.5GHz or greater
• Disk Space: 5GB or more
• Available Memory: 2GB or more
• Java: JDK installation required(JDK 1.5 or higher)
• Oracle Data Integrator v 11.1.1.3.0
• Siebel Client 8.2.1(for srvrmgr.exe utility)

One Oracle Schema required for ODI Work Repository and ODI Master Repository.
Schema owners of Source System DB and Siebel DB with following DB privileges

• create/drop table
• create/drop synonym
• insert/update/delete on table
• create/drop view
• create/drop procedure
• create/drop sequence
• create/drop triggers
• create/drop DB Links
• create/drop functions
• create/drop packages

2.2 Installation Steps

2.2.1 Setup

Click on setup.exe from the ODI Installation disk.

Oracle Universal Installer will start

2.2.2 Welcome Page

On the Welcome page click Next to Continue

2.2.3 Select Installation Type

Select ODI Studio and Standalone Installation, click on Next to continue,

2.2.4 Prerequisite Checks

Click on Next button if the prerequisite checks are successful.

2.2.5 Specify Installation Location

Specify the location where ODI needs to be installed and click on Next.

2.2.6 Repository Configuration

Select “Skip Repository Configuration” and click on Next.

2.2.7 Creating Agent

Enter the name of the agent and port, the agent will be created in the ODI repository.
Note: If you use the name other than “oraclediagent” the same needs to be used while creating URL for the soap request. Refer section 8.1.

2.2.8 Installation Summary

Review the installation summary and click on Install.

2.2.9 Installation Progress

Monitor the Installation Progress and once installation is finished click on Next

2.2.10 Configuration Process

Monitor the Configuration progress, once finished click on Next to continue.

2.2.11 Installation Completed

Review the Installation Log and click on Finish button.

3. Configuring ODI

3.1 Opening ODI

To open the ODI user interface, from the Windows button select All Programs à Oracle à Oracle Data Integrator à ODI Studio

While opening ODI for the first time, ODI will ask for the java.exe file path.

Browse for the Java Installation directory and select Java.exe from the jdk folder.

3.2 Defining new ODI Connection

• From ODI File Menu select New

• The New Gallery window will open

• From General Category select ODI and from items select “Create a New ODI Master Login”

• “Repository Connection information” window will Open

Enter Valid details into the Repository Connection Information window as below

Oracle Data Integrator Connection:

• Login name: A generic alias (for example: Testing Master Repository)
• User: SUPERVISOR (use capitals)
• Password: SUPERVISOR (use capitals)

DBMS Connection (Master Repository):

• User: User id / login of the owner of the tables you have created for the master repository
• Password: This user's password
• Drivers' List: choose the driver required to connect to the DBMS supporting the master repository(ex. Oracle JDBC Driver)
• URL: The complete path of the data server hosting the repository. Ex. For Oracle Database the URL is of following format

jdbc:oracle:thin:@<host>:<port>:<sid>
URL Parameters:
<host>: server network name or IP address.
<port>: listener port number (usually 1521).
<sid> : Instance service name (Oracle SID).
4. Click on Test to check the connection is working.
5. Validate by OK.

4. Importing the Master Repository

From ODI File Menu select New

• The New Gallery window will open

• From General Category select ODI, From items select “Master Repository Import Wizard”

Repository Connection window
In the Repository Connection window provide the settings as specified below
Specify the Database Connection parameters as follows:

• Login: User ID/login of the owner of the tables you have created for the Master repository
• JDBC Driver: The driver used to access the technology, which will host the repository. For Oracle database select the Oracle driver.
• JDBC URL: The complete path for the data server to host the repository.
• User: The user id/login of the owner of the tables.
• Password: This user's password.
• ID: A specific ID for the new master repository. Enter Id=2.
• Use a Zip File: Select the Master_Repository.zip file provided.

• Technology: Oracle

Click Test Connection to test the connection to your master repository.
The Information dialog opens and informs you whether the connection has been established.
In the Password Storage options accept the default selection i.e. User Password Storage Configuration specified in Export.

Click on “Finish” button so that the Master Repository wizard will start.

5. Creating Work Repository

In the icon list Topology -> Repositories -> Work Repositories

Click with the right button, then choose New Work repository.

A window appears, asking you to complete the connection parameters for your work repository.
In the connection window, complete the following parameters:

• Technology: Choose the technology of the server to host your work repository.(ex. Oracle)
• JDBC Driver: The driver required for the connection to the DBMS to host the work repository.
• JDBC URL: The complete path of the data server to host the work repository.
• User: User id / login of the owner of the tables you are going to create and host of the work repository.
• Password: This user's password.

Click on Test.Connection to then select Next

In the window Work Repository properties, complete the following parameters:

• ID: give a unique number to your repository, from 1 to 998 included. Do not use 1 as it is already used in development.
• Name: give a unique name to your work repository (for example: WORKREP1).
• Type: Choose "Development" in the list.

Click on Finish button

The Repository Creation process will start.

Disconnect from the Master Repository by selecting ODI Menu à Disconnect <Repository Name>

6. Importing Work Repository

From ODI File Menu Select New…

From the New Gallery window select Item “Create a new ODI Work Login”

The Repository Connection Information window will open.

Enter valid details in the Repository Connection Information window.
Oracle Data Integrator Connection:

• Login name: A generic alias (for example: Repository)
• User: SUPERVISOR (in capitals)
• Password: SUPERVISOR (in capitals)

DBMS connection (Master Repository):

• User: User id/login of the owner of the tables you have created for the master repository (not the work repository).
• Password: This user's password.
• List of drivers: choose the driver required to connect to the DBMS hosting the master repository you have just created.
• URL: The complete path for the data server hosting the master repository.

Work Repository:

• Work repository name: The name you gave your work repository in the previous step (ex. WORKREP1). You can display the list of work repositories available in your master repository by clicking on the button to the right of this field.

Click on Test to check that the connection is working.

Click on the Connect to Repository… link and select the Work Repository connection

From the Designer tab link select Import à Work Repository

In the popup dialogue box which appears select
Import Mode: Synonym Mode Insert or Synonym Insert Update
Import from Zip file: select the workrep.zip file given by the development team.
· Note: This process may take time and ODI screen may be non responsive; Please do not kill the process forcefully. This process may take 30 to 50 minutes for completion.

Once the import is complete the Import Report will be displayed.

7. Settings for Testing Environment

7.1 Creating DB Link

Use sqlplus or any other database tool to Create DATABASE LINK in Work Schema(In this case Siebel Schema) to Access data from Source.
Use the following command to create the DBLink.
CREATE DATABASE LINK <database link name>
{CONNECT TO <oracle user id>
IDENTIFIED BY <remote oracle user's password> }
USING ' <dbstring> ';
Ex.
CREATE DATABASE LINK KERDEV2
CONNECT TO SSDEV116
IDENTIFIED BY SSDEV116#
USING '(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = 10.184.74.142)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = KERDEV2)
)
)'
User the following query to check if the DB link is successfully created
SELECT DB_LINK FROM ALL_DB_LINKS

7.2 Change Source System Schema

Connect to ODI Work Repository

Navigate to Topology à Physical Architecture à Technologies à Oracle

Select Oracle FC Server to edit the settings. Update the DBLink to the dblink created in Section 7.1.
Update the User and password to connect to the correct testing environment of Source System.


Change the Setting on JDBC tab
· JDBC Driver
· JDBC URL


Expand “Oracle FC Server” connection and open the Physical Schema definition. Change the Schema to point to database of Source System Testing Server.

7.3 Change Siebel Schema

Open the “Oracle Release Server” connection to edit the settings. Edit the Oracle Instance, Username(Schema) and password for testing environment, and click on Test Connection button to check if the settings are proper.

Change the Setting on JDBC tab
· JDBC Driver
· JDBC URL

Expand “Oracle Release Server” connection and open the Physical Schema definition. Change the Schema to point to Database of Siebel Testing Server.

7.4 Change Context

Contexts bring together components of the physical architecture (the real Architecture) of the information system with components of the Oracle Data Integrator logical architecture (the Architecture on which the user works).
Create a new Context or Use the existing “Release Context” for running ODI Interfaces.

For Creating New context only enter the Name of the context.

Navigate to Schemas tab to relate the Logical and Physical schemas for Source System DB and Siebel Database server.

Navigate to Logical Architecture à Technologies à Oracle to verify the changes for Logical Schema.

7.5 Configure Standalone agent on Server

Navigate to ODI installation directory path “Oracle_ODI_1\oracledi\agent\bin” and open odiparams.bat file for editing
Change the following parameters
Ø ODI_MASTER_DRIVER: JDBC driver used to connect the master repository. Ex. For Oracle “oracle.jdbc.driver.OracleDriver”
Ø ODI_MASTER_URL: JDBC URL used to connect the master repository. For Oracle Database the URL is of following format
jdbc:oracle:thin:@<host>:<port>:<sid>
URL Parameters:
<host>: server network name or IP address.
<port>: listener port number (usually 1521).
<sid> : Instance service name (Oracle SID).
Ex. jdbc:oracle:thin:@ 10.217.0.19:1521:emsqa1d
Ø ODI_MASTER_USER: Database account used to connect the master repository
Ø ODI_MASTER_ENCODED_PASS: Database account password. The password must be encoded with the encode.[sh|bat] <password> command.
Ø ODI_SECU_WORK_REP: Name of the work repository to connect to. This work repository is the default repository into which the scenarios are started.
Ø ODI_SUPERVISOR: Name of an ODI SUPERVISOR user. This SUPERVISOR user is used by the agent to connect the master repository.
Ø ODI_SUPERVISOR_ENCODED_PASS: This SUPERVISOR user’s password. The password must be encoded.

7.6 Start Standalone agent on Server

Navigate to path “Oracle_ODI_1\oracledi\agent\bin” and run agent,bat file on server to start the agent.

8. Testing Machine Setup

It is possible to invoke an ODI Scenario using Web service. So Soap UI can be used to invoke the ODI scenarios and check the status.

8.1 Import WSDL

Create a new soapUI Project and import the below wsdl file in the project.

8.2 Start Scenario

The invokeStartScen operation of the web service starts a scenario. Hit the service after filling in data as shown in the screen print below.

(The above is just a sample screen shot. The details filled in will vary based on the environment.)
Below is what must be filled for the first ODI Pass:
URL: http:///oraclediagent/OdiInvoke

Parameter	Value
OdiUser	SUPERVISOR
OdiPassword	SUPERVISOR
WorkRepository	WORKREP1
ScenarioName	FINS_BIB_LIMIT_PKG
ScenarioVersion	001
Context	RELEASE_CONTEXT
Synchronous	TRUE
SessionName	User Defined ex. BIB_Testing

Optional Variables: These variables are set at ODI Tools level and can be changed while running scenario from Soap UI.

8.3 Check Session Status

The “getSessionStatus” operation of the agent web service returns the status of one or more sessions in a given repository, identified by their Session Numbers provided in the “SessionIds” element. It manages both running and completed sessions.