Thursday, 9 February 2012
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
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
click on context
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
Click on search button in the resource field
And then select the respective file
click open
Then click on Files tab
set the parameters as shown below
and click on reverse engineer button as shown below
Create the columns by selecting the width of the each column
Click on save
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 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
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
click on context
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
Click on search button in the resource field
And then select the respective file
click open
Then click on Files tab
Set the following parameters as shown below
Heading : 1
Record separator : MS Dos
Field separator : ,
Then click on columns
Click on save once the columns are imported
Right click on import knowledge modules
and then import the Loading knowledge module " LKM FILE to SQL"
Perform automatic mapping
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
Tuesday, 7 February 2012
ODI PIVOT-COLUMNS TO ROWS
Hi Gurus,
In "Informatica" we have "Normalizer transformation" and in "Datastage" and "OWB" we have PIVOT stage and "PIVOT Rule" to convert "COLUMNS TO ROWS"
This tutorial will guide you how to convert "COLUMNS TO ROWS" using ODI.
I have a table T with the following structure and Data
And i have a table TT with the following structure
Now i want to do mapping between T and TT
to get the following output data in TT
To do this using ODI
Open the Designer
Expand the project
Right click on interfaces
And click on new interface
Give the name as INF_TT
Drag and drop T from the SRC_SCOTT model on to the source pane
Drag and Drop TT from TRG_SCOTT model on to the target pane
Click ok to perform Automatic mapping
enter the string value 'COLUMN1' for the mapping properties of target column SOURCE and select execute on staging area
Also drag drop the "COLUMN1" column of T data store on VAL column of TT table
Now click on add new data set
Click on + symbol two time to create two new data sets of "UNION" relation as shown below
Click on <New Dataset1>
Now Drag and drop the T table from SRC_SCOTT model to the source pane
perform automatic mapping by clicking on yes
enter the string value 'COLUMN2' for the mapping properties of target column SOURCE and select execute on staging area
Also drag drop the "COLUMN2" column of T data store on VAL column of TT table
Click on <New Dataset2>
Now Drag and drop the T table from SRC_SCOTT model to the source pane
Perform automatic mapping by clicking on yes
enter the string value 'COLUMN3' for the mapping properties of target column SOURCE and select execute on staging area
Also drag drop the "COLUMN3" column of T data store on VAL column of TT table
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
In "Informatica" we have "Normalizer transformation" and in "Datastage" and "OWB" we have PIVOT stage and "PIVOT Rule" to convert "COLUMNS TO ROWS"
This tutorial will guide you how to convert "COLUMNS TO ROWS" using ODI.
I have a table T with the following structure and Data
Now i want to do mapping between T and TT
to get the following output data in TT
To do this using ODI
Open the Designer
Expand the project
Right click on interfaces
And click on new interface
Give the name as INF_TT
Drag and drop T from the SRC_SCOTT model on to the source pane
Drag and Drop TT from TRG_SCOTT model on to the target pane
Click ok to perform Automatic mapping
enter the string value 'COLUMN1' for the mapping properties of target column SOURCE and select execute on staging area
Also drag drop the "COLUMN1" column of T data store on VAL column of TT table
Now click on add new data set
Click on + symbol two time to create two new data sets of "UNION" relation as shown below
Click on <New Dataset1>
Now Drag and drop the T table from SRC_SCOTT model to the source pane
perform automatic mapping by clicking on yes
enter the string value 'COLUMN2' for the mapping properties of target column SOURCE and select execute on staging area
Also drag drop the "COLUMN2" column of T data store on VAL column of TT table
Click on <New Dataset2>
Now Drag and drop the T table from SRC_SCOTT model to the source pane
Perform automatic mapping by clicking on yes
enter the string value 'COLUMN3' for the mapping properties of target column SOURCE and select execute on staging area
Also drag drop the "COLUMN3" column of T data store on VAL column of TT table
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
Sunday, 5 February 2012
ODI PIVOT- ROWS TO COLUMNS
Hi Gurus,
In "Informatica" we have "Normalizer transformation" and in "Datastage" and "OWB" we have PIVOT stage and "PIVOT Rule" to convert "ROWS TO COLUMNS"
This tutorial will guide you how to convert "ROWS TO COLUMNS" using ODI.
Let us consider the following two source table structures TABLE1 and TABLE2
and target table structure TABLE3
To do this
Open the Designer
Expand the project
Right click on interfaces
And click on new interface
Give the name as INF_TABLE3
Click on mapping tab
Drag the LOCID column from TABLE1 table and drop on LOCID column of TABLE2 table , then it will create a join. click on join then you will see the JOIN properties below
Drag and Drop TABLE3 from TRG_SCOTT model on to the target pane
Click ok to perform Automatic mapping
SERVICE_STARTS_MON:
MAX(DECODE(TABLE2.DAY_OF_WEEK,0,TABLE2.SERVICE_STARTS,NULL))
SERVICE_STARTS_TUE:
MAX(DECODE(TABLE2.DAY_OF_WEEK,1,TABLE2.SERVICE_STARTS,NULL))
SERVICE_STARTS_WED:
MAX(DECODE(TABLE2.DAY_OF_WEEK,2,TABLE2.SERVICE_STARTS,NULL))
SERVICE_STARTS_THU:
MAX(DECODE(TABLE2.DAY_OF_WEEK,3,TABLE2.SERVICE_STARTS,NULL))
SERVICE_STARTS_FRI:
MAX(DECODE(TABLE2.DAY_OF_WEEK,4,TABLE2.SERVICE_STARTS,NULL))
SERVICE_STARTS_SAT:
MAX(DECODE(TABLE2.DAY_OF_WEEK,5,TABLE2.SERVICE_STARTS,NULL))
SERVICE_STARTS_SUN:
MAX(DECODE(TABLE2.DAY_OF_WEEK,6,TABLE2.SERVICE_STARTS,NULL))
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