Creating an ODI Project and Interface: Exporting a Flat File 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 Integrator (ODI) to export a flat file table to another flat file.

Time to Complete

Approximately 30 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 ODI Target Datastore for Use with ODI Interface
 Creating 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 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 import a file to a staging area, perform some minor transformations, and then write the data to a file. You also execute the interface and verify the execution using 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, perform transformations, and load this data into another flat file.

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_RT_RT in the Name field. The Code field is filled automatically. Click OK. The newly created ODIexp_RT_RT project now appears in the Projects tree view. 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 Files. Click OK.

 

 

2.

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


 

3.

On the screen that appears, 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 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 source datastore, perform the following steps:

1.

Expand the Files folder, right-click the newly created Files model, and then select Insert Datastore. On the screen that appears, set 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, and then click OK. 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 the names and data types of each column, refer to the following table. Click OK when you have finished.

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

 

6.

The columns should now look as follows. Click OK to save the model. Expand Files > Files > SRC_SALES_PERSON (SRC_SALES_PERSON.txt) > Columns to view your source model. Verify that the columns were successfully created as shown in the screenshot.

Note: If the columns 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 ODI Target Datastore for Use with ODI Interface

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

1.

In this OBE, you use the sample files that are delivered with the ODI installation. 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 in the following screenshot .

 

2.

In ODI Designer, right-click the newly created source datastore SRC_SALES_PERSON and select Duplicate. On the screen that follows, click Yes. The new Copy of SRC_SALES_PERSON.txt appears in the tree view.

 

3.

Right-click the newly created duplicate Copy of SRC_SALES_PERSON.txt and select Edit. On the screen that appears, set the Name to TRG_SALES_PERSON. Click the button  next to the Resource Name field.

Note: The Files tab and Columns tab do not need to be changed because they are duplicated from the source file.

 

4.

Select the TRG_SALES_PERSON.txt file and click Open. Click OK. The datastores for the source and target files have now been successfully created in the same model and can be used in an ODI interface.

 

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 the Projects tab. Expand your project ODIexp_RT_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_RT_RT. Select the " Staging Area Different From Target" check box and select SUNOPSIS_MEMORY_ENGINE from drop-down list. Click the Diagram tab. If the Help screen appears, click OK.

Note: For this OBE, we will use the Sunopsis Memory Engine as our staging area because we want to perform some transformations on the imported data.

 

3.

Click the Models tab to drag the source and target to Designer. Expand Files > Files and drag 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.

Note: For the purposes of this tutorial, default mappings are used, although a number transformation can be performed in the mapping. The mapping should be performed in the staging area.

 

4.

For this interface, Linda 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 File 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.

 

5.

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

 

6.

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

 

7.

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

 

8.

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. Set the IKM options TRUNCATE to Yes and GENERATE_HEADER to No as shown below. Click Apply.

 

9.

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

 

10.

To verify that your interface was executed successfully, you need to open ODI Operator. Click the ODI Operator icon  on the menu bar to start ODI Operator. In ODI Operator, expand Date > Today > INTexp_FF_FF > Steps > INTexp_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. Double-click step 6 and click the Execution tab. View the number of rows that are inserted into the target flat file. Click OK.

 

11.

Click the icon  to return to ODI Designer. Click OK to save your newly created interface. Expand Interfaces. The INTexp_FF_FF interface is now shown in the Projects tree structure. You have now successfully created an ODI project and an ODI interface. You have also successfully executed the INTexp_FF_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 ODI Model for the Flat File Source
 Create a New ODI Source Datastore for Use with ODI Interface
 Create a New ODI Target Datastore for Use with ODI Interface
 Create 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