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

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

 

Time to Complete

Approximately 40 minutes

Topics

This OBE tutorial covers the following topics:

 Overview

Scenario

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

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, the 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 that 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 that is performed using ODI is to export data from a flat file and load this data into a relational table. This tutorial walks you through the steps that are needed to create a project and an interface that will export a flat file to a relational table. You also execute the interface and verify the execution in 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, Linda created the new Master repository and Work repository. Now Linda needs to create a project and an interface to export data from a flat file and load this data into a relational table.

Back to Topic List

Verifying the Prerequisites

Before you start the tasks, make sure that the following requirements are met:

If not done before, 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 with 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 list if not already selected. Enter SUPERVISOR in the User field and SUNOPSIS in the Password field. Click OK to login.

 

2.

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

 

3.

On the screen that appears, set the Name of the project to ODIexp_FF_RT in the Name field. The Code field is filled automatically. Click OK. The newly created ODIexp_FF_RT project now appears in the Projects pane. You have now successfully added a new ODI project.

 

Back to Topic List

 

Creating a New ODI Model for the Flat File Source

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

1.

Click the Models tab in the left panel of ODI Designer. Click the Insert Model Folder icon. On the screen that appears, set the Name to Flat_Files. Click OK.

 

 

2.

Right-click the Flat_Files model folder and select Insert Model.


 

3.

On the screen that follows, enter the values provided in the following table. Click the Reverse tab (don't click the Reverse button). Select Global from the Context drop-down list. Click OK.

Parameter Value
Name Flat_Files
Technology File
Logical Schema FILE_DEMO_SRC

 

Back to Topic List

Creating a New ODI Source Datastore for Use with ODI Interface

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

1.

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

Note: In this OBE, you use the sample files that are delivered with the ODI installation.

 

2.

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


3.

On the Columns tab, click the Reverse button. The following screen appears. Click Yes. The Column Setup Wizard appears.

 

4.

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

 

5.

After the columns are marked, click each column data and set the Name and Datatype for each column. For names and datatypes of each column, refer to the following table. Click OK when you have finished.

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 > SRC_SALES_PERSON > Columns and view the newly created columns.

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

Back to Topic List

 

Creating a New RDBMS Schema for the ODI Target Datastore

You must create a schema to host the ODI target datastore. To create a new RDBMS schema for the ODI datastore, perform the following steps:

1.

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

 

 

2.

On the Oracle Database Express Edition Home screen, select SQL Commands > Enter Command from the SQL drop-down list.

 

 

3.

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 that you want to create
<MY_PASS> corresponds to the password that you gave
<MY_TBS> corresponds to the Oracle tablespace where the data will be stored
<MY_TEMP> corresponds to the temporary default tablespace

To create the user, enter the following command. Click Run.

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

 

4.

Verify that user ODI_STAGE2 was successfully created.

 

5.

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

grant connect, resource to ODI_STAGE2;

Verify that this statement processed successfully.

 

Back to Topic List

Creating a New ODI Target Datastore for Use with ODI Interface

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

1.

If not opened, open the Oracle XE Database Home Page: Start > All Programs > Oracle Database 10g Express Edition > Go to Database Home Page. Log in to Oracle XE Database as ODI_STAGE2. The password for this user is password. Click Login.

 

3.

On the Oracle Database Express Edition Home screen, select SQL Commands > Enter Command from the SQL drop-down list. Create the table by executing the following SQL commands:

CREATE table "TRG_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") )
/

Back to Topic List

Creating a New ODI Target Data Server and Physical Schema

In the following steps, you create a new ODI source data server and physical schema. To create the ODI source data server and 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 is SUPERVISOR and the default Password is SUNOPSIS. Click OK to login.

Note: The Master repository was created in the OBE titled Creating and Connecting to ODI Master and Work Repositories. To access this OBE, click HERE.

 

2.

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

 

4.

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

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

 

 

5.

Click  for JDBC Driver. Select Oracle JDBC Driver. Click OK. Click  for JDBC 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 the JDBC Url field! This may cause problems with entering a valid URL string.
  • You may need to enter the 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_STAGE2 for the Schema (Schema) and Schema (Work Schema) fields. Click the Context tab. Click the Add new context button  . Set Logical Schema to ODI_STAGE2. Click OK.

 

Back to Topic List

Creating a New ODI Target Model

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

1.

Start ODI Designer: Start > All Programs > Oracle > Oracle Data Integrator > Designer. On the Oracle Data Integrator Login screen, select WORKREP from the Login Name drop-down list. Click OK.

 

2.

Click the Models tab in the left panel of ODI Designer, and then click the Insert Model Folder icon  . On the screen that appears, enter the folder name as Oracle RDBMS2. Click OK.

 

3.

Right-click the Oracle RDBMS2 model folder and select Insert Model. On the screen that follows, enter the values provided in the following table. Click the Reverse tab (don't click the Reverse button).

Parameter Value
Name Oracle ODI_STAGE2
Technology Oracle
Logical Schema ODI_STAGE2

 

4.

On the Reverse tab, set Context to Global. Click the Reverse button. The following screen appears. Click Yes, and then click OK.

 

5.

Expand the new Oracle RDBMS2 model folder and expand the Oracle ODI_Stage2 model. Verify that the TRG_SALES_PERSON datastore is successfully reversed.

 

 

Back to Topic List

Creating and Testing a New Interface with Oracle Data Integrator

To create a new interface, perform the following steps:

1.

In ODI Designer, click the Projects tab. Expand your project ODIexp_FF_RT, and then expand First Folder. Right-click Interfaces and select Insert Interface.

 

2.

On the screen that follows, enter the interface name as INTexp_FF_RT. If selected, deselect the Staging Area Different From Target check box. Click the Diagram tab. If the Help screen appears, click OK.

Note: In this example, you use the staging area on the target datastore.

 

3.

Click the Models tab to drag the source and target to Designer. Drag the SRC_SALES_PERSON.txt datastore from the Flat_Files model into the Sources container. Drag the TRG_SALES_PERSON datastore from the Oracle RDBMS2 model 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.

Note: For the purposes of this tutorial, default mappings are used.

 

4.

You must set the value for the DATE_UPDATED column in Target Datastore. If not active, click the Property panel button  to activate the Property panel. In Target Datastore, select DATE_UPDATED. To the right of the Mapping window, click the Editor  icon.

Note: The DATE_UPDATED column can also be set using the CURDATE() function or an appropriate RDBMS function.

 

5.

In the window that appears, select Oracle for Technology. Click OK. In the Function window of the Expression Editor, expand Date and Time and double-click SYSDATE as shown in the screenshot. Click OK. Select the Active Mapping check box if not selected. The target column DATE_UPDATED is now mapped to the SYSDATE function. Click the Flow tab.

 

6.

For this interface, 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, 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 KM is used. However, specific KMs for the RBMS technology can be used as well.

 

7.

On the screen that follows, enter ..\impexp in the File import directory field as shown in the following screenshot. The files to import should appear in the Select the file(s) to import window. Press and hold the Ctrl key, and then select IKM SQL Incremental Update and LKM File to SQL. Click OK.

 

8.

Expand the Loading (LKM) and Integration (IKM) folders, and view each imported KM in the tree view as shown below.

 

9.

Click the SS_0 (1-FILE GENERIC) datastore. The properties for the source appear below. For LKM, select LKM File to SQL from the LKM drop-down list if not selected. Set the LKM option DELETE_TEMPORARY_OBJECTS to <Default>:Yes as shown below.

 

10.

Click the Target datastore. Select IKM SQL Incremental Update if not selected. Set the IKM option Flow_Control to No. Set the IKM option DELETE_ALL to Yes. Click Apply.

 

11.

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

 

12.

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

 

13.

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

Note: If the Unlocking Object window appears, click Yes to automatically unlock the object.

You have now successfully created an ODI project and an ODI interface. You have also successfully executed the INTexp_FF_RT 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 ODI Model for the Flat File Source
 Create a New ODI Source Datastore for Use with ODI Interface
 Create a New RDBMS Schema for the ODI Target Datastore
 Create a New ODI Target Datastore for Use with ODI Interface
 Create a New ODI Target Data Server and Physical Schema
 Create a New ODI Target Model
 Create and Testing a New Interface with Oracle Data Integrator

Back to Topic List

 

 Place the cursor over this icon to hide all screenshots.

 

 

 

Left Curve
Popular Downloads
Right Curve
Untitled Document