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.
No comments:
Post a Comment