Create a New Project with ODI

STEPS:

• Create a new project with Oracle Data Integrator

• Create new data server and physical schema for a source RDBMS table

• Create new logical schema for the newly created physical schema

• Create new ODI model for the RDBMS table source

• Create a new data server physical schema for a target RDBMS table

• Create new logical schema for the newly created physical schema

• Create new ODI model for the target RDBMS table

• Create new ODI interface T2T Transformations

• Test the interface and verify results

Create a new project with oracle data integrator

Start ODI Studio and select work repository. Provide the user name and password and connect to the repository.

Knowledge Modules (KM)

Knowledge Modules (KM) implement “how” the integration processes occur. Each Knowledge Module type refers to a specific integration task:

Reverse-engineering metadata from the heterogeneous systems for Oracle Data Integrator (RKM).
 
Handling Changed Data Capture (CDC) on a given system (JKM)
 
Loading data from one system to another, using system-optimized methods (LKM). These KMs are used in interfaces. 
 
Integrating data in a target system, using specific strategies (insert/update, slowly changing dimensions) (IKM). 

These KMs are used in interfaces Controlling Data Integrity on the data flow (CKM). These KMs are used in data model's static check and interfaces flow checks. 
 
Exposing data in the form of web services (SKM).

Create new data server and physical schema for source RDBMS table

To create a new Physical Schema, we first need to create a data server. Navigate to ‘Topology’ tab, expand the ‘Technologies’ folder in ‘Physical Architecture’ tab in the left Pane. We can see number of folders inside this tab. Select the ‘Oracle’ folder (Since we are dealing Oracle database tables right now). Right Click on the Oracle folder and select ‘Create New Data Server’

 

Create New physical Schema for this Data Server

Select schema name from the list.

Create new logical schema for the newly created physical schema

Open the ‘Logical Architecture’ under the Topology Tab and right click on the ‘Oracle. Choose ‘New Logical Schema’ option to create a new logical schema for the newly create physical schema.

A new screen will appear. Provide the name of logical schema and choose the Physical schema from the list of physical schema.

 

Create new ODI model for source RDBMS table 

To create a new model for source, navigate back to the ‘Designer’ tab. Under the ‘Models’ tab in left pane, select ‘New Model Folder’ to create a folder for the model.

Right click on the ‘APPS_GL’ folder and select ‘New Model’ to create the source ODI model for source table.

A new screen will appear. Fill in the Name, Code will be populated automatically. Select ‘Oracle’ from the Technology drop down list and select the logical schema for this source table.

Select ‘Reverse Engineer’ tab in the left panel. Click on the Reverse Engineer tab at the upper left column to import the data into your ODI schema from your database.

Select ‘Selective Reverse Engineer’ tab in the left panel. Click on the Selective Reverse Engineer tab used to import particular data into your ODI schema from your database.

Table imported in 'SRC_APPS' model.

Create new data server, physical schema and logical schema for a target RDBMS table

Since our source database and target database are on different server. We need to create a new data server, new physical and logical schema. Same steps will be repeated as source data server, physical and logical schema. Follow the screen shots for creating the target data server, physical schema and logical schema.

Create new ODI model for the target RDBMS table

Create new ODI interface table to Table transformations

Select the ‘Projects’ Tab in ‘Designer’ Pane. Expand ‘First Folder’ and right click on ‘Interfaces’ to create a ‘New Interface’

Drag source table , target table from source and target model to interface mapping tab

We can add source and target tables from 'Quick-Edit' tab in Interface. Click Plus symbol and add source.

 

Join between tables

Click the flow tab and assign IKM for the target table.

Save the interface and Execute.

Test the interface and verify results

To verify the successful execution of the interface, navigate to the Topology Tab. Select Date > Today > and Interface info with date and time will be seen there. The green status depicts the successful execution of the interface.

Regards,
Haree