ODI 11g/12 C - How to Load Data into a Flat File

Oracle Data Integrator supports heterogeneous sources and targets. It is a common requirement while working with ODI to load data into a flat file (.txt or .csv). This blog is an attempt to list down the specific steps to carry out data load into flat files along with information on which data modules should we use for such an operation.

ODI Restrictions

For loading data into files, ODI has following two restrictions.

Flat files can be used a Source or Target but not as Staging Area. When loading data into a file, the Staging Area must to choose different than that of target. In short the target checkbox on the definition tab of integration interface should be different than target. The staging area should be a schema than a file.

Agent should have direct access to the file system ( local or mounted network disk partition) on which the file resides.

ODI Knowledge Modules

Knowledge Modules (KMs) are code templates. Each KM is dedicated to an individual task in the overall data integration process. The code in the KMs appears in nearly the form that it will be executed except that it includes Oracle Data Integrator (ODI) substitution methods enabling it to be used generically by many different integration jobs. The code that is generated and executed is derived from the declarative rules and metadata defined in the ODI Designer module.

The features of KMs are

#1 – A KM will be reused across several interfaces or models. Change once used across all transformations is a mantra.

# 2 – KMs can be analyzed for impact analysis

# 3 – KMs execution require metadata from interfaces, data stores and models

ODI KM Categories

KM fall into 6 different categories

Reverse-engineering KM – Retrieves metadata to the ODI work repository, it is been used in models to perform a customized reverse-engineering

Check KM - Checks consistency of data against constraints. Theses KMs are used in models and sub models and data store for data integrity audit. It is also used in interfaces for flow control or static control.

Loading KM - Loads heterogeneous data to a staging area. It is used in interfaces with heterogeneous sources

Integration KM – Integrates data from the staging area to a target used in interfaces

Journalizing KM – Creates the Change Data Capture framework objects in the source staging area. This KM is used in models, sub models and data stores to create, start and stop journals and to register subscribers.

Service KM - Generates data manipulation web services. It is used in models and data stores

KM Required to Data Load to Flat Files

There are potentially we need one or two KMs.

The choice of Loading KM (LKM) will depend on the choice of staging area. If staging area is same as the source schema then no LKM is required.  If is different than the source schema, an LKM would be in charge of loading source data from a remote server to the staging area. The LKM implements the declarative rules that need to be executed on the source server and retrieves a single result set that it stores in a "C$" table in the staging area.

The Integration KM (IKM) is in charge of writing the final, transformed data to the target table/File. IKM simply execute the Staging and Target transformations, joins and filters on staging tables (c$ and other tables). For staging area to file IKM SQL to File Append IKM is the best suitable IKM.

Oracle Support Document

How To Load Data Into a Flat File Using ODI (Doc ID 976092.1)

I hope you find this information useful.