Creating ODI Project and Interface: Exporting a Relational Table to a Flat File

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 relational table to a flat file.

 

Time to Complete

Approximately 40 minutes

Topics

This OBE tutorial covers the following topics:

 Overview

Scenario

 Verifying the Prerequisites
 Creating a New Project within Oracle Data Integrator
 Creating a New RDBMS Schema for ODI Source Datastore
 Creating a New ODI Source Datastore for Use with ODI Interface
 Creating a New ODI Source Data Server and Physical Schema
 Creating a New ODI Source Model
 Creating a New ODI Model for Flat File Target
 Creating a New ODI Target Datastore for Use With ODI Interface
 Creating a New Interface within Oracle data Integrator
 Summary
 Related information

Viewing Screenshots

 Place the cursor over this icon to load and view all the screenshots for this tutorial. (Caution: Because this action loads all screenshots simultaneously, response time may be slow depending on your Internet connection.)

Note: Alternatively, you can place the cursor over each individual icon in the following steps to load and view only the screenshot associated with that step.

The screenshots will not reflect the specific environment you are using. They are provided to give you an idea of where to locate specific functionality in Oracle Data Integrator.

Overview

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

 

Back to Topic List

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 she 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.

Back to Topic List

Verifying the Prerequisites

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

Software Requirements

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

Software Requirements

If not done before, then you need to start the services and components for Database 10g XE and Oracle Data Integrator 10g(10.1.3.4)

Back to Topic List

 

Creating a New Project within Oracle Data Integrator


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

1.

Start ODI Designer: Start > All programs > Oracle > Oracle Data Integrator > Designer. Select WORKREP from the Login Name drop-down menu, if not already selected. Enter User Name and Password as SUPERVISOR and SUNOPSIS. Click OK to login.

 

2.

In the window that opens, click Projects tab, and then click the Insert Project button  to add a new project.

 

3.

In the screen that opens, set the Name of the project to ODIexp_RT_FF in the name field. The code field will be filled automatically. Click OK. The newly create project ODIexp_RT_FF now appears in the Project tree view. You have now successfully added a new ODI project

 

Back to Topic List

Creating a New RDBMS Schema for ODI Source Datastore

You have to create a schema (ODI_STAGE1) to host the ODI source datastore. To create a new RDBMS Schema for ODI datastore, perform the following steps:

1.

Start the Oracle XE database Home Page: Start > All Programs > Oracle database 10g Express Edition > Go to Database Home page . The Login screen appears. Login into Oracle XE database as System. The default password is oracle1. Click Login.

 

 

2.

In Oracle Database Express Edition Home screen open the SQL drop-down menu and select SQL Commands > Enter Command.

 

 

3.

You create the schemas by executing the following SQL commands:

create user <MY_SCHEMA> identified by <MY_PASS>
default tablespace <MY_TBS> temporary tablespace <MY_TEMP>;
grant connect, resource to <MY_SCHEMA>;


Where:
<MY_SCHEMA> corresponds to the name of the schema you want to create;
<MY_PASS> corresponds to the password you have given to it;
<MY_TBS> corresponds to the Oracle tablespace where the data will be stored;
<MY_TEMP> corresponds to temporary default tablespace;

To create user enter the following command. Click Run.

create user ODI_STAGE1 identified by password
                                      
default tablespace users temporary tablespace temp;

 

4.

Verify that user ODI_STAGE1 was successfully created.

 

 

5.

Grant connect privileges to newly created user by executing the following SQL command:

grant connect, resource to ODI_STAGE1;

Verify that this statement processed successfully.

 

Back to Topic List

Creating a New ODI Source Datastore for Use With ODI Interface

In the next steps you will create a new ODI datastore for the source model. This datastore which will be used within ODI interface. To create a new ODI source datastore, perform the following steps:

1.

If not started, start the Oracle XE database Home Page: Start > All Programs > Oracle database 10g Express Edition > Go to Database Home page . Login into Oracle XE database as ODI_STAGE1.. The password for this user is password. Click Login.

 

3.

In Oracle Database Express Edition Home screen open the SQL drop-down menu and select SQL Commands > Enter Command. You create the table by executing the following SQL commands:

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") )
/

4.

To populate the SRC_SALES_PERSON table, go to SQL Commands window, enter the commands from the table below. Click RUN and then, verify that statements processed successfully.

Note: You can copy this script from the text file SRC_SALES_PERSON_DATA, provided with this OBE. To download this file click here.

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

5.

Verify that table SRC_SALES_PERSON populated with data successfully by executing the statement provided below.

Note: The data must be loaded correctly before proceeding to the next step.

select* from ODI_STAGE1.SRC_SALES_PERSON

 

 

Back to Topic List

Creating a New ODI Source Data Server and Physical Schema

In the next steps you will create a new ODI source data server and the physical schema. To create the ODI source data server and the physical schema, perform the following steps.

1.

Start ODI Topology Manager: Start > All programs > Oracle > Oracle Data Integrator > Topology Manager. Select Master Repository. The default User Name and Password is preset as SUPERVISOR and SUNOPSIS. Click OK to login.

Note: Master repository had to be created it OBE " Creating and Connecting to ODI Master and Work Repositories". To access this OBE click HERE

 

2.

Click the Physical Architecture tab of Topology Manager. Expand Technologies, right-click Oracle, and select Insert Data Server.

 

4.

In DataServer: New window enter the values provided in the table below. Click JDBC tab.

Parameter Value
Name ODI_STAGE1
Instance/dblink (Data Server) localhost
User ODI_STAGE1
Password password

 

5.

Click  for JDBC driver. Select Oracle JDBC Driver. Click OK. Click  for URL, and then enter jdbc:oracle:thin:@localhost:1521:XE for Oracle XE database. Click Test. Click Test again.

Notes:

  • Don't copy and paste in to the JDBC Url field! That may cause problems with entering valid url string.
  • You may need to enter appropriate driver and URL for your RDBMS.

6. Verify successful connection and click OK. Click OK again. The Physical Schema screen appears.

7.

On the Physical Schema screen, select ODI_STAGE1 for Schema (Schema) and Schema (Work Schema) fields. Click Context tab. Click Add new context button  . Set logical schema to ODI_STAGE1. Click OK.

Back to Topic List

Creating a New ODI Source Model

In this section you create a new ODI source model that will be used within ODI interface. To create a new ODI source model , perform the following steps:

1.

Start ODI Designer: click Start > All Programs > Oracle > Oracle Data Integrator > Designer. In Oracle Data Integrator Login screen, for Login Name select work repository WORKREP. Click OK .

2.

Click the Models tab within the left panel of ODI Designer, and then click Insert Model Folder icon  . In the screen that follows, enter the folder name: Oracle RDBMS1.

3.

Right-click Oracle RDBMS1 model folder and select Insert model. In the screen that follows, enter the values provided in the table below. Click Reverse tab ( don't click Reverse button!).

Parameter Value
Name Oracle ODI_STAGE1
Technology Oracle
Logical Schema ODI_STAGE1

 

4.

In the Reverse tab, set Context to Global. Click Reverse. The following screen will appear. Click Yes, and then click OK.

5.

Expand new Oracle RDBMS1 model folder, expand Oracle ODI_Stage1 model. Verify that the datastore SRC_SALES_PERSON have been successfully reversed.

 

Back to Topic List

Creating a New ODI Model for Flat File Target

To create the new model for the flat file target datastore, perform the following steps:

1.

Click the Models tab within the left panel of ODI Designer. Click the Insert Model Folder icon. In the screen that appears, set the Name to Flat Files. Click OK.

 

 

2.

Right-click Flat Files model folder and select Insert model.


 

3.

In the screen that follows, enter the values provided in the table below. Click Reverse tab ( don't click Reverse button!) Select Global context. Click OK.

Parameter Value
Name Flat Files
Technology File
Logical Schema FILE_DEMO_SRC

 

4.

In this OBE, you will use the sample files delivered with the installation of ODI. Browse to the ODI_HOME\demo\file directory. Make a copy of the SRC_SALES_PERSON.txt file and name the copy TRG_SALES_PERSON.txt, as shown in the example on the screenshot below.

Back to Topic List

Creating a New ODI target Datastore for Use With ODI Interface

To create a new ODI target datastore, perform the following steps:

1.

Expand Flat Files folder, right-click the newly created Flat Files model, and then select Insert Datastore. In the screen that appears, set Name to TRG_SALES_PERSON. Click the button  next to Resource Name field. Select file TRG_SALES_PERSON.txt and click Open.

 

2.

Click the Files tab. Set the File Format to Fixed, and then click the Columns tab.


3.

In the Columns tab click Reverse button. The following screen will appear. Click Yes. The Column Setup Wizard will appear.

4.

Drag the mouse, click at the beginning of each column and the wizard will mark each column.

5.

Once the columns are marked, click on each column data and set the Name and Data Type for each column as shown on the screenshots. For names and datatypes of each column refer to the table below. Click OK when done.

Column Name Datatype
C1 SALES_PERSON_ID numeric
C2 FIRST_NAME string
C3 LAST_NAME string
C4 DATE_HIRED string

 

6.

The columns should look as follows. Click OK to save the model. Expand Flat Files > Flat Files > TRG_SALES_PERSON > Columns and view the columns. Expand Oracle RDBMS1 >Oracle ODI_STAGE1 > SRC_SALES_PERSON > Columns folder. View your source and target models.

Note: If columns for TRG_SALES_PERSON have not been created, edit the newly created datastore: right-click the TRG_SALES_PERSON datastore, select Edit, and repeat steps 2,3,4 and 5.

Back to Topic List

Creating a New Interface With Oracle Data Integrator

To create a new interface, perform the following steps:

1.

In ODI Designer, click Projects tab. Expand your project ODIexp_RT_FF, and then expand First folder. Right-click Interfaces and click Insert Interface.

 

 

2.

In the screen that follows, enter interface name INTexp_RT_FF. Select the "Staging Area Different From Target" check box and select ODI_STAGE1 from drop-down menu. Click Diagram tab. If Help screen appears, click OK.

Note: In this example you use the ODI_STAGE1 (the source) as the staging area instead of target file.


 

3.

Click the Models tab to drag and drop the source and target to the designer. Drag the SRC_SALES_PERSON datastore from the Oracle RDBMS1 into the Sources container. Drag the TRG_SALES_PERSON.txt datastore from the Flat Files model into the Target Datastore container. When Designer asks "Do you want to perform and Automatic Mapping?", click Yes. The Diagram tab should look as follows. Click the Flow tab.

 

4.

For this interface, you export a relational table into a flat file target. The KM needed for this is the IKM SQL to File Append. To import the KM, click the Project tab in the left panel and expand Knowledge Modules folder. Right-click Loading (LKM) and select Import Knowledge Modules.

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

 

5.

In the screen that follows, enter ..\impexp in the File import directory field as shown on the screen below. The files to import should appear in the Select the file(s) to import window. Select IKM SQL to File Append. Click OK.

6.

Expand the Integration folder and view each imported KM in the tree view as shown below. Click the Flow tab.

 

7.

Click the Target (FI LE_GENERIC). This will make the properties for the target appear below. For the IKM, click the IKM drop-down menu and select the IKM SQL to File Append. Set the IKM option TRUNCATE to Yes, as shown below. Click Apply.

 

8.

To test your interface click Execute. The following screen will appear. Leave the defaults and click OK. On the next screen click OK.

9.

To verify that your interface was executed successfully, you need to open the ODI Operator. Click the ODI Operator icon  on the menu to start the ODI Operator. In ODI Operator expand: Date > Today > INTexp_RT_FF > Steps > INTexp_RT_FF, and view the execution results for each step. Double-click the Step 3, click the Execution tab. View the number of rows inserted in to the target file. Click OK.

10.

Click the icon  to return to ODI Designer. Click OK to save your newly created interface. The Interface now shown in the Projects tree structure.

Note: If Unlocking Object Window appeared, click Yes to automatically unlock object.

You have now successfully created an ODI project and an ODI interface. You have also successfully executed INT_RT_FF ODI interface to export a relational table to a flat file target.

Back to Topic List

 

Summary

In this lesson, you learned how to:

 Verify the Prerequisites
 Create a New Project with Oracle Data Integrator
 Create a New RDBMS Schema for ODI Source Datastore
 Create a New ODI Source Datastore for Use with ODI Interface
 Create a New ODI Source Data Server and Physical Schema
 Create a New ODI Source Model
 Create a New ODI Model for Flat File Target
 Create a New ODI Target Datastore for Use With ODI Interface
 Create a New Interface with Oracle data Integrator

Back to Topic List

 

Related Information

 To ask a question about this OBE tutorial, post a query on the OBE Discussion Forum.

Back to Topic List

 Place the cursor over this icon to hide all screenshots.

 

 

 

Left Curve
Popular Downloads
Right Curve
Untitled Document