Advantage:
Using this, we can load data from flat file to Oracle easily,
To export Flat File to Oracle DB, we have to follow below
steps.
Ø For that,
we have to Create project to create a interface
Ø We have
to import knowledge modules
Ø we need
to define the 2 Data Servers, the 2 physical schemas and associate them with
the relevant logical schemas in ODI Topology Navigator.
Ø Need to
define 2 data models & create 2 data stores.
|
|
Create
project to create interface: Go to Designer Navigator. Select new project |
 Import Knowledge modules: |
|
|
For
this interface, It has to import a flat file into the SQL staging area, and
then export the file to a different flat file. The Knowledge Modules
(KMs) that are required for this are LKM File to SQL and IKM
SQL to SQL Control Append. To import the KMs, click the Project tab
in the left panel and expand the Knowledge Modules folder.
Right-click Loading (LKM) and select Import Knowledge
Modules.  Note: In
this example, the generic SQL KMs are used. However, specific KMs for the
RDBMS technology can be used as well. |
|
|
1. On the screen that follows, in the File
Name, navigate to xml reference directory as shown
in the following screenshot. Click Open. The files to import
should appear in the Import Knowledge Modules window. Press
and hold the CTRL key to select IKM SQL to SQL Control Append and LKM
File to SQL. Click OK.   |
|
|
On Import Report window, click Close.
Expand the Loading (LKM) and Integration (IKM) folders,
and view each imported KM in the tree view as shown below.   Create a
Data Server for FILE DB in Topology Manager Step:
1) Open up Topology Manager
and go to Physical Architecture. Right click on the File technology and select New Data Server. In the Data Server window enter a name in the Name field.  Step:
2) Go to
the JDBC tab. Select the com.sunopsis.jdbc.driver.file.FileDriver in the JDBC Driver List and jdbc
url for jdbc:snps:dbfile  Step:
3) Click on Test and make sure you get a successful
connection.  |
Create
physical schema for file DB (technology):
|
Click Topology
tab. In Topology navigator, click the Physical Architecture tab, select
Technologies->> File. Right-click FILE_GENERIC, and then select New
Physical Schema.
Enter the path to the directory, where your input flat files are located
(C:\Users\Administrator\Desktop).  Create
logical schema for file DB (technology): Click Topology tab. In
Topology navigator, click the Logical Architecture tab, select
Technologies->> File. Right-click and then select New Logical Schema. Specify
the name and map it with Contexts.  Create a
Data Server for Oracle DB in Topology Manager Step:
1) Open up Topology Manager
and go to Physical Architecture. Right click on the Oracle technology and select New Data Server. In the Data Server window enter a name in the Name field.  Step:
2) Go to
the JDBC tab. Select the oracle.jdbc.OracleDriver in the JDBC Driver List and JDBC URL for jdbc:oracle:thin:@190.160.0.85:1521:SAPTRAINING  Step: 3) Click on Test and
make sure you get a successful connection.  Create
physical schema for Oracle DB (technology): Click Topology tab. In
Topology navigator, click the Physical Architecture tab, select
Technologies->> Oracle. Right-click SAP_TRAINING, and then select New
Physical Schema.
Enter the schema details.  Create
logical schema for Oracle DB (Technology): Click Topology tab. In
Topology navigator, click the Logical Architecture tab, select
Technologies->> Oracle. Right-click and then select New Logical Schema. Specify
the name and map it with Contexts.  |
||||
|
Create Data
model for source DB (FILE Technology): Step:
1) Go to the Designer Navigator select model tab
and create new model folder for Files ,
used for easily identification.  Step:
2) Create new model for Files, used for storing
tables & data stores. Specify the Name, Technology and Logical
schema while creating new model.  Create data stores for source DB (FILE
Technology) |
||||
|
Source: Flat File |
|
|||
|
Step:
1) Expand the Flat Files folder. Select text file folder,
right-click and then select New Datastore. On the screen that appears,
set Name to source. Click the button   |
||||
|
Step: 2) Click the Files tab. Set the File
Format to Delimited & File Separate to comma(,) and then click
the Columns tab.  |
||||
|
Step: 3) Check the Columns in the data store. Clicks
save.  Step: 4) Expand flat file folder>text file >source (Source_File.txt) >Columns to view your source model. Verify
that the columns were successfully created as shown in the screenshot.  Create
Data model & Data store for Target(Oracle DB) Target: Oracle
DB Step: 1) Go to the Designer Navigator select model tab and create
new model folder for Oracle DB, used for easily identification.  Step:
2) Create new model for Files, used for storing
tables & data stores. Specify the Name, Technology and Logical
schema while creating new model.  Step:
3) Create data stores for target DB. Expand Oracle DB folder>SAP_TRAINING. Right click & select new data store . Then enter the data store name
& add the column specified data type. Click Save.   Step:
4) Check the columns in Data store. Expand Oracle DB folder>SAP_TRAINING> Columns
to view your source model. Verify that the columns were
successfully created as shown in the screenshot.   |
||||
Create
interface for loading data from source to target table:
Step: 1) Go to Designer,
click the Projects tab. Expand your project SAMPLE _WORK_ON_INTERFCAES_VTH_DIFF
DB, and then expand First Folder. Right-click interfaces and selects
new Interface.
|
Step: 2) Click the Models tab. Drag the source
& target data store into interface mapping. When Designer asks "Do
you want to perform an Automatic Mapping?" click Yes.
The Diagram tab should look as follows. Click the Quick edit tab
& set primary key.   Step: 3) Go to flow tab. Click the source table. 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  Step: 4)
Click the target table. This makes the properties for the target appear
below. This makes the properties for the target appear below.
For IKM, select IKM SQL to
SQL Control Append from the IKM drop-down list if not
already selected. Set the IKM options create target table to true & Flow control to false as shown below.
Click Save icon to save your interface.  Step: 5) To test your interface click Execute icon
  Step: 3) To
verify that your interface was executed successfully, you need to open ODI Operator Navigator.
Click the ODI Operator tab. In ODI Operator, click Session List. Expand Sessions > FLAT
TO Oracle, and view the execution results for each step. Double-click any
step and click the Description and Execution tabs
to see the execution results. Double-click step 7. View the number of rows
that are inserted into the target flat file. Click OK. Close the
tabs.  Step:
3) Go
to Oracle DB model and select target data store(Flat to Oracle). Next right click the target data store and
select the view data for output.  Note:
First Target table have empty records. After execute the interface target
table have 14 records |
|
|
|
|
|
|
|
|
|
