ODI Filter Transformation-2

 ODI Interface Example: Loading from Source table EMP with conditions SAL < 2500 and JOB= ’CLERK’  into TRG_EMP_FIL2 target table

Note: To view any image clearly just double click on it

Hi Gurus,

Let us see the steps of ‘How to Load source Data table EMP with conditions salary less than 2500 whose Job is ‘CLERK’ into target table TRG_EMP_FIL2  through Oracle Data Integrator(ODI).

Requirements:

·         Oracle Database 11g

·         SQL Developer

·         Oracle Data Integrator 11g

You should have the above Requirements should be installed on your System before going to attempt this task.

Description:                                                                                              

This Tutorial will help you to ‘Load source Data table EMP with conditions salary less than 2500 whose Job is ‘CLERK’ into target table TRG_EMP_FIL2 through ODI’   .

Explanation:

Connect to SQL Developer, Here My source schema is SRC_SCOTT in which we have the source table ‘EMP’ and My target schema is TRG_SCOTT in which we have the target table ‘TRG_EMP_FIL2’(contains no data) which is shown in the below screenshots:

ODIGURUS.COM

  

  

Connect to Oracle Data Integrator by running the Shortcut Menu or Go to the location where the ODI resides and run it.

Now Login to Oracle Data Integrator by providing the login details

Now in the Designer Navigator expand your project and right click on the Interface and select ‘New Interface’ as shown below:

Give the name for the Interface and click on Mapping tab.

We can see the Source table ‘EMP’ in the source Model ‘SRC_SCOTT_MODEL’ and target table ‘TRG_EMP_FIL2’ in the target Model ‘TRG_SCOTT_MODEL’ as shown in the below screenshots.

Now drag and drop source table ‘EMP’ into source area and  target table ‘TRG_EMP_FIL2’ into target area , after dragging the target table TRG_EMP_FIL2 it will ask you to do the automatic mapping(which is shown in the below screenshot)., cilck ‘YES’ then the target table will automatically mapped with the source table as shown below:

Check whether all the columns of the target table TRG_EMP_FIL2 is mapped with the source table EMP columns.

Now in the source table EMP which is in Source area drag the SAL column ,Filter Properties will open.

In the active Filter Provide the condition ‘EMP.SAL<2500’

Click on ‘Right symbol’ to check whether the provided condition is correct or not.

Here the Above provided condition is correct which is shown in the below:

Also drag the JOB column from the source table EMP in the Source area,Filter Properties will open.

In the active Filter Provide the condition EMP.JOB=’CLERK’

Click on ‘Right symbol’ to check whether the provided condition is correct or not.

Here the Above provided condition is correct which is shown in the below:

Now click on the ‘Flow’ tab and then click on the ‘Flow Diagram of the target table’ ( on red symbol) then you will be able to see the target Properties.

In the Target Properties.,For IKM Selector  select the ‘IKM SQL control append Knowledge Module’( we have to Import this IKM (Integrated Knowledge Module) before going to create the interface.

For the Options ‘Flow Control’ select ‘False’ (It must me true only when we are using Some constraints or conditions.,In this case it should be False otherwise it throws an Error) as shown below:

For the Option ‘Truncate’ select ‘True’ (Because it truncates the target table if it contains any data,If it contains no data then it inserts the Source table data) as shown below:

Goto File menu and click on ‘Save’ to save the Interface.,While saving ODIwill ask to lock the object to prevent other users from editing it.,Click on ‘No’.

Click on ‘Execute’ button(which will be in the Green Symbol on top of the Navigator tools) to run the Interface.

It will show the “Execution” dialogue box which is shown below.,here select the Context as ‘Global’ and agent as ‘Local Agent’ (because we have not yet created any standalone agent).Click on ‘Ok’ to start Execution.

It will show an Information Message as ‘Session Started’.Click on ‘OK’.

Now go to the Operator navigator and expand the Date and expand the Today,click on ‘Refresh icon’(Which is in Blue Symbol on below the Navigator tools), then you will be able to see the Status of the Interface Execution.We can see the Session details like Number of Inserts,Number of Rows,Number of Updates .. from this session details which is shown Below

From the above Session details we can observe that Our target table contains(4 Records,because here No. of inserts is 4).

Now go to SQL developer and check whether the target table TRG_EMP_FIL2 contains 4 records.

Yes,From the above screenshot we can see that our target table TRG_EMP_FIL2 contains 4 records(which is the Source table EMP data with condition SAL <2500 and JOB=’CLERK’)

From this tutorial,We have successfully got to know how to load Source table EMP with condition SAL<2500  whose Job is ‘CLERK’ which is in SRC_SCOTT schema  into Target table TRG_EMP_FIL2 which is in TRG_SCOTT schema using Oracle Data Integrator.

Thanks & Regards
WWW.ODIGURUS.COM

CREATION OF WORK REPOSITORY

 CREATION OF WORK REPOSITORY:

Hai Gurus,

Let us see the steps of ‘How to create Work Repository’ in Oracle Data Integrator.

Requirements:

·         Oracle Database 11g

·         SQL Developer

·         Oracle Data Integrator 11g

·         Master Repository in ODI

You should have the above Requirements should be installed on your System before going to attempt this task.

Description:

This Tutorial will help you to ‘Create the Work Repository’ in Oracle Data Integrator.

Explanation:

Connect to the ‘SYSTEM’ schema and create the user  for Work Repository and provide the Grant Privileges as shown in the below screenshot.

Thanks & Regards
WWW.ODIGURUS.COM

XML to RDBMS Table Loading

Hi Gurus,

Today i am going to show you how to define XML source and how to load it into the RDBMS target.

Thanks & Regards
WWW.ODIGURUS.COM

Fixed Width Flat File - RDBMS table Loading

 Hi Gurus,

Let us discuss how to load data from a Fixed width flat file to RDBMS target table.

I have a flat file with the name DEPT.txt in my windows C:/FILES folder
The flat file DEPT.txt is a Fixed width flat file with the following data


DEPTNO  DNAME              LOC    
10              ACCOUNTING  NEW YORK
20              RESEARCH       DALLAS 
30              SALES                CHICAGO
40              OPERATIONS   BOSTON 

Now inorder to load this file to oracle table of same structure , first you need to set the topology
( Please ignore the following few steps if you have already set the topology and created a model for files)
Run ODI Studio
Go to Topology navigator
Click on physical architecture
Select file technology
Right click on new physical schema

 Give the path of your source files directory in schema and work schema
click on context

 Click on +
Give logical schema as SRC_FILES_LS
Click on save

Create a new model for file technology as shown below
Click on save

Right click on the new file model
Click on new data store
Now the new data store creation wizard opens

Enter the name of the data store as DEPT
Click on search button in the resource field
And then select the respective file
click open
Then click on Files tab

 Click on Files tab
set the parameters as shown below

 Then click on Columns tab
and click on reverse engineer button as shown below
Create the columns by selecting the width of the each column

 Enter the names of each column and select the string data type as shown below
Click on save

You have to import the knowledge module  " LKM FILE to SQL". Please ignore this step if you have already done it earlier.
 Now Expand your integration project
Right click on import knowledge modules
and then import the Loading knowledge module " LKM FILE to SQL"


Then create a new interface as DEPT file data store as your source and TRG_DEPT Oracle table data store as your target.
Perform automatic mapping

 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

Thanks & Regards
WWW.ODIGURUS.COM

Delimiter Flat File - RDBMS Table Loading

 Hi Gurus,

Let us discuss how to load data from a comma delimiter flat file to RDBMS target table.

I have a flat file with the name EMP.txt in my windows C:/FILES folder
The flat file EMP.txt has the following data where each field separated by a comma and the first line is a heading

EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO
7369,SMITH,CLERK,7902,17-12-80,800,,20
7499,ALLEN,SALESMAN,7698,20-02-81,1600,300,30
7521,WARD,SALESMAN,7698,22-02-81,1250,500,30
7566,JONES,MANAGER,7839,02-04-81,2975,,20
7654,MARTIN,SALESMAN,7698,28-09-81,1250,1400,30
7698,BLAKE,MANAGER,7839,01-05-81,2850,,30
7782,CLARK,MANAGER,7839,09-06-81,2450,,10
7788,SCOTT,ANALYST,7566,19-04-87,3000,,20
7839,KING,PRESIDENT,,17-11-81,5000,,10
7844,TURNER,SALESMAN,7698,08-09-81,1500,0,30
7876,ADAMS,CLERK,7788,23-05-87,1100,,20
7900,JAMES,CLERK,7698,03-12-81,950,,30
7902,FORD,ANALYST,7566,03-12-81,3000,,20
7934,MILLER,CLERK,7782,23-01-82,1300,,10

Now inorder to load this file to oracle table of same structure , first you need to set the topology
Run ODI Studio
Go to Topology navigator
Click on physical architecture
Select file technology
Right click on new physical schema

 Give the path of your source files directory in schema and work schema
click on context

 Click on +
Give logical schema as SRC_FILES_LS
Click on save

Create a new model for file technology as shown below
Click on save

Right click on the new file model
Click on new data store
Now the new data store creation wizard opens

Enter the name of the data store as EMP
Click on search button in the resource field
And then select the respective file
click open
Then click on Files tab

Select the file format  as Delimited
Set the following parameters as shown below
Heading : 1
Record separator : MS Dos
Field separator :     ,
Then click on columns

Click on reverse engineer button as shown below
Click on save once the columns are imported

you can right click on the EMP file data store and view data by clicking on view data option

Now Expand your integration project
Right click on import knowledge modules
and then import the Loading knowledge module " LKM FILE to SQL"

Then create a new interface as EMP file data store as your source and TRG_EMP Oracle table data store as your target.
Perform automatic mapping

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

Thanks & Regards
WWW.ODIGURUS.COM