ODI11g: Creating an ODI Project and Interface: Exporting an RDBMS Table to a Flat File

 

<Do not delete this text because it is a placeholder for the generated list of "main" topics when run in a browser>

Purpose

This tutorial walks you through the steps that are needed to create a project and an interface in Oracle Data Intergrator (ODI) to export a relational table to a flat file.

Time to Complete

Approximately 40 minutes

Overview

A common task that is performed using ODI is to export data from a relational table and load this data into a flat file. This tutorial walks you through the steps that are needed to create a project and an interface that will export a relational table to a flat file. You also execute the interface and verify the execution with ODI Operator.

Scenario

Linda works as a database administrator for Global Enterprise. In Global Enterprise, Linda is responsible for performing database management and integration tasks on various resources within the organization. In particular, Linda is responsible for data loading, transformation, and validation. To begin working on her projects, Linda created the new Master repository and Work repository. Now Linda needs to create a project and an interface to export data from a relational table and load this data into a flat file.

Software and Hardware Requirements

The following is a list of software requirements:

Prerequisites

Before you start the tasks, make sure that your system environment meets the following requirements:

.

Have installed Oracle Database 11g. If not done before, start the services and components for Oracle Database 11g

.

Have installed Oracle Data Integrator 11gR1

.

Before attempting this OBE, you should have successfully completed the following OBEs:

  • ODI11g: Creating and Connecting to ODI Master and Work Repositories.
  • ODI11g: Creating an ODI Project and Interface : Exporting a Flat File to a Flat File.

To access these OBEs, click HERE.

 

Creating a New Project with Oracle Data Integrator

To create a new project within Oracle Data Integrator, perform the following steps:

.

Start ODI Designer: Start > Programs > Oracle > Oracle Data Integrator > ODI Studio . Select WORKREP1 from the Login Name drop-down list if not already selected. Enter SUPERVISOR in the User field and SUNOPSIS in the Password field. Click OK to login.






.

In the designer tab, click the Projects tab, click New Project icon , and then click New Project.


.

On the screen that appears, set the Name of the project to ODIexp_FT_RT in the Name field. The Code field is filled automatically. Click Save icon . The newly created ODIexp_FT_RT project now appears in the Projects tree view. You have now successfully added a new ODI project.






.

You export a flat file directly to a relational table target. The knowledge modules required for this are LKM File to SQL and IKM SQL Incremental Update. To import the KMs, expand the Project tab in the left panel, right-click the Knowledge Modules folder and select Import Knowledge Modules.

Note: In this example, the generic SQL KM is used. However, specific KMs for the RDBMS technology can be used as well.


.

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. Select IKM SQL to File Append. Click OK.



.

On Import Report window, click Close.




Creating RDBMS Schema for ODI Source Datastore

You need to create a schema to host the ODI source datastore. In these OBEs you use the RDBMS user/schema ODI_STAGE for the tables used in these examples.

Note: You created the schemas for the flat file target model in the OBE: "Creating an ODI Project and Interface: Exporting a Flat File to a Flat File".

Note: If you completed the OBE " Creating an ODI Project and Interface: Exporting a Flat File to a RDBMS Table" earlier, you should already have ODI_STAGE schema created. In this case, you can skip Step1 and Step 2.

To create a new RDBMS schema for the ODI datastore, perform the following steps:

.

Start SQL Developer. You will create the new schema/user by executing the following SQL commands:

create user ODI_STAGE identified by ODI_STAGE

default tablespace users temporary tablespace temp;


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







.

If not done before, in SQL Developer create new connection called ODI_STAGE. Enter User name as ODI_STAGE with password ODI_STAGE. For SID enter ORCL. Click Test to verify connection, and then click Connect.





.

Create your source table and populate it with data by executing the SQL commands provided below. Expand connection ODI_STAGE > Tables > TRG_SALES_PERSON and verify that the table is created successfully.
Note: You may find these commands in the text file frovided with this OBE. To access this file, click HERE.

CREATE table "SRC_SALES_PERSON" (
"SALES_PERSON_ID" NUMBER(8,0) NOT NULL,
"FIRST_NAME" VARCHAR2(80),
"LAST_NAME" VARCHAR2(80),
"DATE_HIRED" VARCHAR2(80),
"DATE_UPDATED" DATE NOT NULL,
constraint "TRG_SALES_PERSON_PK" primary key("SALES_PERSON_ID") )
/

begin
insert into ODI_STAGE.SRC_SALES_PERSON values
(11,'Andrew','Andersen','22/02/1999',sysdate);
insert into ODI_STAGE.SRC_SALES_PERSON values
(12,'John','Galagers','20/04/2000',sysdate);
insert into ODI_STAGE.SRC_SALES_PERSON values
(13,'Jeffrey','Jeferson','32422',sysdate);
insert into ODI_STAGE.SRC_SALES_PERSON values
(20,'Jennie','Daumesnil','28/02/1988',sysdate); insert into ODI_STAGE.SRC_SALES_PERSON values
(21,'Steve','Barrot','24/09/1992',sysdate);
insert into ODI_STAGE.SRC_SALES_PERSON values
(22,'Mary','Carlin','14/03/1995',sysdate);
insert into ODI_STAGE.SRC_SALES_PERSON values
(30,'Paul','Moore','36467',sysdate);
insert into ODI_STAGE.SRC_SALES_PERSON values
(31,'Paul','Edwood','18/03/2003',sysdate);
insert into ODI_STAGE.SRC_SALES_PERSON values
(32,'Megan','Keegan','29/05/2001',sysdate);
insert into ODI_STAGE.SRC_SALES_PERSON values
(40,'Rodolph','Bauman','29/05/2000',sysdate);
insert into ODI_STAGE.SRC_SALES_PERSON values
(41,'Stanley','Fischer','37233',sysdate);
insert into ODI_STAGE.SRC_SALES_PERSON values
(42,'Brian','Schmidt','25/08/1992',sysdate);
insert into ODI_STAGE.SRC_SALES_PERSON values
(50,'Anish','Ishimoto','30/01/1992',sysdate);
insert into ODI_STAGE.SRC_SALES_PERSON values
(51,'Cynthia','Nagata','28/02/1994',sysdate); insert into ODI_STAGE.SRC_SALES_PERSON values
(52,'William','Kudo','28/03/1993',sysdate);
end







Creating a New ODI Source Dataserver and the Physical Schema

After you created the new database source datastore, you need to create a new ODI sourcet data server and the Physical schema.

Note: Skip this section, if you completed the OBE " Creating an ODI Project and Interface: Exporting a Flat File to a RDBMS Table" earlier.

To create the ODI source data server and physical schema, perform the following steps:

.

In ODI, Open ODI Topology Navigator and then select the Physical Architecture tab. Expand Technologies, right-click Oracle and select New Data Server.

 

.

In the Data Server: New window, enter the values provided in the table below. Click the JDBC tab.

Parameter

Value

Name

ODI_STAGE

Instance/dblink (Data Server)

ORCL

User

ODI_STAGE

Password

ODI_STAGE



.

Click for JDBC Driver. Select Oracle JDBC Driver. Click OK. Click for JDBC Url, select jdbc:oracle:thin:@<host>:<port>:<sid>, and then click OK. Edit the Url to read: jdbc:oracle:thin:@localhost:1521:ORCL for Oracle Database.
Note: Do not copy and paste in the JDBC Url field. This may cause problems with entering a valid URL string.




.

Click Test Connection. In the window that opens, click Yes to save your data. In the Information window, click OK. Click Test to verify successful connection. Click OK.





.

Click Expand Oracle technology node, right-click ODI_STAGE dataserver, and then select New Physical Schema.


.

In Schema (Schema) and Schema (Work Schema) field enter your ODI_STAGE schema. Click Save button. Close ODI_STAGE.ODI_STAGE physical schema window. In the Information window, click OK.




.

Open Logical Architecture tab, expand Technologies > Oracle. Right-click Oracle technology and then select New Logical Schema.


.

Name logical schema ODI_STAGE. In the Global context, connect this logical schema to ODI_STAGE physical schema as shown below. Click Save button and then close the tabs.


 

Creating a New ODI Source Model

Create a new ODI target model that will be used within your ODI Interface. To create a new ODI target model, perform the following steps:

.

Open ODI Designer. Click Models tab and select New Model. On the screen that appears, enter the values provided in the following table. Click the Reverse Engineer tab.


Parameter

Value

Name

Oracle_RDBMS2

Technology

Oracle

Logical Schema

ODI_STAGE

 

.

On the Reverse Engineer tab, set the Context to Global . Click Save button and then close Oracle_RDBMS2 tab.



.

In the Models tab, right-click the Oracle_RDBMS2 model. Select Reverse Engineer. To verify that the SRC_SALES_PERSON datastore is successfully reversed expand the model as shown below.




 

Creating a New ODI Interface to Perform RDBMS table to Flat File Transformation

To create a new ODI Interface to perform RDBMS table to flat file transformation, pefrom the following steps:

.

In ODI Designer, click the Projects tab. Expand your project Export-RT-FT, and then expand First Folder. Right-click Interfaces and select New Interface. In Optimization Context field, select Global.


.

On the screen that follows, enter the interface name as INT-EXP-RT_FT . For Staging Area select Oracle: ODI_STAGE. Click Mapping tab.




.

Click the Models tab to drag the source and target to the diagram. Drag the TRG_SALES_PERSON.txt datastore from the Flat_File1 model into the Target container. Drag the SRC_SALES_PERSON datastore from the Oracle_RDBMS2 model into the Source Datastore container. When Designer asks “Do you want to perform an Automatic Mapping?” click Yes.


.

Click the Flow tab. Click Target (FILE_GENERIC). The properties for the target appear below. For the IKM, select IKM SQL to File Append from the IKM drop-down list. Set the IKM options: TRUNCATE to true, GENERATE_HEADER to false as shown below. Click Save icon .


.

To test your interface, click Execute button . The following screen appears. Retain the defaults and click OK. On the next screen, click OK.




.

Open ODIOperator and verify that your interface was executed successfully. In Operator, click Session List tab, select All Executions, and the then click refresh button . View execution results for interface INT-EXP-RT-FT.



.

Double-click Step 2 and click the Execution tab. View the number of rows inserted into the target datastore (15). Close the tab.




Summary

In this tutorial, you have learned how to:

Resources

 

Hardware and Software Engineered to Work Together About Oracle |Oracle and Sun | Oracle RSS Feeds | Careers | Contact Us | Site Maps | Legal Notices | Terms of Use | Your Privacy Rights