FILTER INTERFACE

Hi Gurus,

Let us study how to develop filter transformation using ODI.
Create a new interface with the name "INF_TRG_EMP_FIL1"
CLICK ON MAPPING TAB

 Drag and drop EMP  from the SRC_SCOTT model on to the source pane
Drag and Drop TRG_EMP_FIL1 from TRG_SCOTT model on to the target pane
Click ok to perform Automatic mapping

To create a filter Drag and drop the SAL column of EMP source on to the empty space
Click on the filter symbol which apears
Now you can see the filter properties in the property inspector
enter the filter condition as EMP.SAL<2500
Click on save
Click on FLOW tab

Click on Target to see the IKM target properties
Set the "flow_control" option to false
Set the "Truncate" option to true
Click on save
Click on execute

Go to operator log
Check the log for this session
Open it and check the number of inserts

Assignments:
1. Create a mapping with filter condition as EMP.SAL>1000 between EMP data source of SRC_SCOTT model and TRG_EMP_FIL2 data store of TRG_SCOTT model

 2. Create a mapping with filter condition as EMP.SAL>=3000 between EMP data source of SRC_SCOTT model and TRG_EMP_FIL3 data store of TRG_SCOTT model

Thanks & Regards
WWW.ODIGURUS.COM

MY FISRT MAPPING IN ODI-TABLE - TABLE MAPPING

 Hi Gurus,

Now you are going to create first mapping in ODI.
Run ODI Studio.
Connect to repository.
Click on Designer
Expand your DEMO project

Right click on interfaces
Click on New interface
Give the name of the interface as "INF_TRG_EMP"
Click on mapping Tab

Now Drag and Drop "EMP" datastore from "SRC_SCOTT" model on to the source pane as shown below.
Also drag and drop the "TRG_EMP" data store from "TRG_SCOTT" model on to the target pane as shown below
Click yes to Automatic mapping

 Click on Flow Tab
Click on Target on the flow to see IKM Target properties
Set "FLOW_CONTROL" option to FALSE
Set "Truncate" option to TRUE
Click on Save

Right click on interface
Click on Execute
Click ok
Click ok

 Click on operator
Expand Date
Expand Today
Double click to open the log of the session
Green mark indicates success of the session
Red-Failed
yellow-Error

 You can see the number of inserts as 14 records

Thanks & Regards
WWW.ODIGURUS.COM

CREATING INTEGRATION PROJECT

 Hi Gurus,

Let me show you have to create an integration project.
But before that please refer the following links for more details about ODI PROJECT

Project Life Cycle

Oracle Data Integrator Project Components

To create a new integration project in ODI follow the following steps

 Run the ODI Studio

Connect to repository

Click on Designer

Click on New project as shown below

 Now the new project creation wizard opens
Enter some name for project 
Click on save

Now you have to import the required knowledge modules for your integration project.
For example you you want to map source and target belongs to the same oracle data server and if your loading strategy is either "control append" or "truncate/insert" then import the knowledge module
"IKM SQL Control Append"

Expand the PROJECT
Right click on Knowledge modules
And click on import knowledge modules
Click on search button
Browse to the 'xml-reference folder' under 'oracledi directory'
Click open
Then select the knowledge module
"IKM SQL Control Append"
and click ok

 You need not to save the report of import knowledge module.
Click ok
Now your project is ready to develop mappings

 

Thanks & Regards
WWW.ODIGURUS.COM

Lookup transformation using ODI

 Hi Gurus,

Let us study how to create lookup transformation using ODI.

Run th ODI
Open Designer

I have two models namely SRC_SCOTT and TRG_SCOTT
I have EMP and DEPT tables in my SRC_SCOTT model and
I have TRG_EMP_DEPT_OJOIN table in TRG_SCOTT model.
I have to populate TRG_EMP_DEPT_OJOIN table EMP table and lookup on DEPT table.

Create new interface with the name INF_TRG_EMP_DEPT_OJOIN
Drag and drop  EMP table from the SRC_SCOTT Model to sources pane
Drag and drop TRG_EMP_DEPT_OJOIN from TRG_SCOTT model to target pane

Create a new lookup by clicking on the Magnified glass button as show below

 Select EMP table  from the left and DEPT table from the right
Click on next

 
Select DEPTNO from source and DEPTNO from Lookup
And click on Join
Select staging area
Click on finish

You can see the lookup table under source pane
Clcik on mapping properties of target column DNAME
click on expression editor as shown below

 Drag and drop DNAME from lookup table DEPT to the expression space
click apply
click ok

 repeat the above steps to map the target column LOC

 Click on Flow tab of the interface
Click on staging area
Now you can see the IKM properties
Set FLOW_CONTROL to false
set TRUCATE_TABLE to true

Click on save
right click on interface INF_TRG_EMP_DEPT_OJOIN and click on execute
Open the operator and view the log

Thanks & Regards

WWW.ODIGURUS.COM

CREATING AN INTEGRATION PROJECT

 Hi Gurus,

Today Let us study how to create an Integration Project



Thanks & Regards
WWW.ODIGURUS.COM

Creating Models

 Hi Gurus,

Let us see how to create models using designer in ODI Studio.
To practice this exercise you should finish the following exercise
Setting the topology

Run the ODI Studio
Click on Designer
Click on New model as shown in the below window

 Now the Model Creation wizard opens
Give some name to the model: SCOTT
Select technology as "ORACLE"
Select the logical schema : SRC_SCOTT_LS
Click on Selective Reverse Engineering

 Check All
now you are able to see the tables to import
Click on top left Green button to reverse engineer
Click ok to save

 Repeat the above steps to create another model for TRG_SCOTT as shown below

After creating Models you can create an Integration Project

Thanks & Regards
 WWW.ODIGURUS.COM

Setting The Topology

 Hi Gurus,



Today Let me show you how to set the topology for oracle schemas.

Assuming that you have the following schemas defined in your oracle database.
1. SRC_SCOTT

2. TRG_SCOTT

Run the ODI Studio

Click  on connect to repository.
Select the appropriate ODI Login
Click OK
Now ODI is connected to Repository and you would able to see the ODI tools Desiner,Topology, Operator and Security manager

Click on topology
Expand the technolgy tree
Select oracle technology

Right Click on Oracle technology
Click on New Data server
Now the Data server creation wizard opens

Enter same name for your data server: ORACLE_DS
Give the user name and password of System user of your database: system/sysdba
Then click on JDBC

Click on search button to select the appropriate JDBC Driver
Enter the JDBC url to connect to your database
My database is running on my local system (localhost) on port number 1521 and SID is "odigurus"

Click on Test connection
Click OK to save
Click on Test

If the test connection is successful then you need to create physical schemas
Right click on the Created Data Server
Click on New physical schema

Now the Physical schema creation wizard opens
Select "SRC_SCOTT" as shown below
Click on Context

 Click "+" symbol

Give some logical schema name: SRC_SCOTT_LS
Click on Save

 Repeat the Above steps to create a physical schema for TRG_SCOTT

Now you have perfectly defined the topology for the Oracle schemas SRC_SCOTT and TRG_SCOTT
You can go to designer and start creating models for these two physical schemas

Thanks & Regards

WWW.ODIGURUS.COM