Transfer data from one Flat File to another Relational Table.

 

Open your SQL DEVELOPER and connect to the Administrator role.

Create the new schema/user by executing the following SQL commands:

Create user ODI_STAGE identified by oracle1

default tablespace users temporary tablespace temp;

Grant connect, resource, create trigger, create view to ODI_STAGE;

Now connect to your schema by mentionaing ODI_STAGE as user and oracle1 as password.

Now Create table in ODI_STAGE schema by executing following query in SQL Developer.

CREATE table "TRG_SALES_PERSON"(

"SALES_PERSON_ID" NUMBER (8, 0) NOT NULL,

"FIRST_NAME" VARCHAR2 (80),

"LAST_NAME" VARCHAR2 (80),

Constraint "TRG_SALES_PERSON_PK" primary key ("SALES_PERSON_ID")

)

Now create a project in ODI Suit. And name the project as ODIExp_FT_RT and save the changes.

Also import IKM SQL Incremental Update Knowledge module

Now import the Knowledge module LKM File to SQL .

Now go to Oracle technology under Physical Architecture and right click and select New Data Server.

Name the Data Server as ODI_STAGE. And specify the instance as localhost , User as ODI_STAGE and password as oracle1.

Then go to JDBC tab and specify the driver and url.

Save the result and click ok on dialogue that appears for physical schema.

Now right click on Data Server that is newly created and select new physical schema option.

Fill out the required detail that is Directory as ODI_STAGE and Directory (Work Schema) as ODI_STAGE. Save the changes and click ok on dialogue that appers to specify the context.

Now go to Logical Architecture tab and right click on the Oracle technology and select New Logical Schema.

Name the Logical schema as ODI_STAGE and set the physical schema for the context inside logical schema window.

Now go to Designer tab and click on the icon in Model window and select New Model Folder.

Name the Folder as RDBMS and save the changes.

Right click on the folder just created and select New Model option.

Name the Model as Oracle_RDBMS1 and select technology as Oracle and logical schema as ODI_STAGE.

Now right click on model just created and select Reverse Engineer.

Now go to File technology and create and create a physical schema for our file.

Create logical schema for the file.

Now create a model folder in model tab.

Now create a new model inside files folder.

Create a DataStore inside model of file.

Selct the SRC_SALES_PERSON.TXT as Resource Name.

Now switch to Files tab and select File Format as Fixed.

Now select Reverse Engineer icon inside Column tab of DataStore window.

Point the cursor, and then click the ruler at the beginning of each column as shown below. The wizard marks each column.

After the columns are marked, click each column data and set the Name and Data type for each column. Click OK when you have finished.

Right click on data store newly created and select view data option.

You can see the data inside file.

Now go to your Project and select Interface option. Right click on Interface icon and create a new Interface.

Name the interface as INT-EXP-FF-RT .

Now go to Mapping tab and drag and drop the SRC_SALES_PERSON datastore from the Files folder into the Sources container. Drag the TRG_SALES_PERSON datastore from Files into the Target Datastore container.

When Designer asks "Do you want to perform an Automatic Mapping?" click Yes. The Diagram tab should look as follows. Click the Flow tab.

Click the SS_0 (1 - FILE_GENERIC) source datastore. This makes the properties for the source appear below. In the LKM Selector section, select LKM File to SQL from the LKM drop-down list if not already selected.

Click Target (FILE_GENERIC). This makes the properties for the target appear below. For IKM, select IKM SQL Incremental Update from the IKM drop-down list if not already selected.

Now right click on the interface and select the execute option.

The following screen appears. Retain the defaults and click OK. On the screen that follows, click OK.

Click Ok.

To verify that your interface was executed successfully, you need to open ODI Operator. Click the ODI Operator tab. In ODI Operator, click Hierarchal Sessions. Expand Date > Today > INT_exp_FF_RT > Steps > INT_exp_FF_RT, and view the execution results for each step. Double-click any step and click the Description and Execution tabs to see the execution results.

To verify the result connect to ODI_STAGE user and execute the following SQL command:

Select * from TRG_SALES_PERSON;

You can see that data is inserted successfully.

Transfer data from one Flat File to another Flat File.

First of all connect your ODI to work repository. And then go to Topology tab.

In Physical schema window of Topology tab select the technology as Flat_File.

Now expand the Flat_File technology and select and right click on FILE_GENERIC data server. You can also create your own data server.

Now create a new physical shema inside FILE_GENERIC Data Server.

Now go to you’re my computer and select any Drive and create a folder and inside that folder create two file.

1. SRC_SALES_PERSON.TXT
2. TRG_SALES_PERSON.TXT

And the copy the location of your folder in which above two files residing.

Also put some data inside your SRC_SALES_PERSON and save it.

Leave blank your TRG_SALES_PERSON file .

Now inside Physical schema paste the location you have copied inside Directory (Schema) and Directory (Work Schema) and save your application.

Now go to Logical Architecture tab and select New Logical Schema.

Now create a Logical Schema inside Flat_File technology inside the Logical Architecture window.

And select the Physical Schema which you have created for context.

Now go to Physical Schema window and select the logical schema for physical schema in context tab.

Now go to Designer tab and create a new project .

Name the project and ODIExp_FT_FT and save the changes.

Now expand the project and select Knowledge Module.

What is Knowledge Modules ?

Knowledge modules (KMs) in Oracle Data Integrator are components that implement reusable transformation and ELT (extract, load, and transform) strategies across different technologies. One example of this is extracting data through change data capture from Oracle Database 10g and loading the transformed data into a partitioned fact table in Oracle Database 11g, or creating timestamp-based extracts from a Microsoft SQL Server database and loading this data into a Teradata enterprise data warehouse.

Right click on Knowledge module and select Import Knowledge Module.

Select IKM SQL to File Append Knowledge module and click on Ok. And then close the window of imported Knowledge Module.

Again right click on Knowledge module and select Import Knowledge Module. Select LKM File to SQL Append Knowledge module and click on Ok. And then close the window of imported Knowledge Module.

Now go to Model tab and create a New Model.

Name the model as Flat_File_1 and select the Technology as File and Logical Schema which you have created.

Now select the context as Global in Reverse Engineer tab.

Now right click on model which you have created (Flat_File_1) and select New DataStore.

What is DataStore ?

A DataStore is a structure that allows data to be stored. It can be:

§ a table,

§ a file,

§ a message queue

§ Or any other data structure accessible by middleware used by Oracle Data Integrator (JDBC/ODBC, JMS or JNDI).

In our present case DataStore is a file.

Name the DataStore as SRC_SALES_PERSON and select the source file from where data is to be fetch by clicking on magnifier symbol of Resource Name.

Now switch to Files tab and select File Format as Fixed.

Now select Reverse Engineer icon inside Column tab of DataStore window.

Point the cursor, and then click the ruler at the beginning of each column as shown below. The wizard marks each column.

After the columns are marked, click each column data and set the Name and Data type for each column. Click OK when you have finished.

Click OK to save the changes.

Now right click on SRC_SALES_PERSON under Model tab and select Duplicate Selection option. A copy of SRC_SALES_PERSON will be created. Now right click on it and select View Option.

Rename the file as TRG_SALES_PERSON and select TRG_SALES_PERSON.txt as Resource Name. And select File Format as Fixed in file tab.

Now go to your Project and select Interface option. Right click on Interface icon and create a new Interface.

What is Interface ?

An interface is an object in Oracle Data Integrator which populates one datastore, called the target, which data coming from one or more other datastores, known as sources.

The columns in the source datastore are linked to the columns in the target datastore through business rules.

Name the interface and select checkbox below Operation context (Staging Area Different From Target) and select In-Memory Engine: SUNOPSIS_MEMORY_ENGINE

Now go to Mapping tab and drag and drop the SRC_SALES_PERSON datastore from the Files folder into the Sources container. Drag the TRG_SALES_PERSON.txt datastore from Files into the Target Datastore container.

When Designer asks "Do you want to perform an Automatic Mapping?" click Yes. The Diagram tab should look as follows. Click the Flow tab.

Click the SS_0 (1 - FILE_GENERIC) source datastore. This makes the properties for the source appear below. In the LKM Selector section, select LKM File to SQL from the LKM drop-down list if not already selected.

Click Target (FILE_GENERIC). This makes the properties for the target appear below. For IKM, select IKM SQL to File Append from the IKM drop-down list if not already selected.

To test your interface right click on interface you have created and select Execute option.

The following screen appears. Retain the defaults and click OK. On the screen that follows, click OK.

Click Ok.

To verify that your interface was executed successfully, you need to open ODI Operator. Click the ODI Operator tab. In ODI Operator, click Hierarchal Sessions. Expand Date > Today > INT_exp_FF_FF > Steps > INT_exp_FF_FF, and view the execution results for each step. Double-click any step and click the Description and Execution tabs to see the execution results.

Now go to your folder in My Computer and verify whether data has been written to TRG_SALES_PERSON.TXT file or not.