This chapter includes the following sections:
Introduction to Integration Projects
An integration project may be composed of several types of components. These components include organizational objects, such as folders, and development objects such as mappings and variables.Oracle Data Integrator Project Components details the different components involved in an integration project.
A project has also a defined life cycle which can be adapted to your practices. Project Life Cyclesuggests a typical project life cycle.
Oracle Data Integrator Project Components
Components involved in a project include components contained in the project and global components referenced by the project. In addition, a project also uses components defined in the models and topology.
Oracle Data Integrator Project Components
The following components are stored into a project. They appear in the in the Project accordion in the Designer Navigator, under the project's node.
Folders are components that help organizing the work into a project. Folders contain packages, mappings, procedures, and subfolders.
A package is a workflow, made up of a sequence of steps organized into an execution diagram. Packages assemble and reference other components from a project such as mappings, procedure or variable. See Creating and Using Packages, for more information on packages.
A mapping is a reusable dataflow. It is a set of declarative rules that describes the loading of one or several target datastores from one or more source datastores. See Creating and Using Mappings, for more information on mappings and reusable mappings.
A Procedure is a reusable component that groups a sequence of operations that do not fit in the mapping concept.
Examples of procedures:
Wait and unzip a file
Send a batch of files via FTP
Purge a database
A variable's value is stored in Oracle Data Integrator. This value may change during the execution.
A sequence is a variable automatically incremented when used. Between two uses the value is persistent.
User functions allow you to define customized functions or "function aliases," for which you will define technology-dependent implementations. They are usable in mappings and procedures.
See Creating and Using Procedures, Variables, Sequences, and User Functions, for more information about the components described above.
Oracle Data Integrator uses Knowledge Modules at several points of a project design. A Knowledge Module is a code template related to a given technology that provides a specific function (loading data, reverse-engineering, journalizing).
A component of a project may be flagged in order to reflect a methodology or organization. Flags are defined using markers. These markers are organized into groups, and can be applied to most objects in a project. See Organizing and Documenting Integration Projects, for more information on markers.
When a package, mapping, procedure, or variable component has been fully developed, it is compiled in a scenario. A scenario is the execution unit for production. Scenarios can be scheduled for automated execution. See Using Scenarios, for more information on scenarios.
Project Life Cycle
The project life cycle depends on the methods and organization of your development team. The following steps must be considered as guidelines for creating, working with and maintaining an integration project.
- Create a new project and import Knowledge Modules for this project.
- Define the project organization and practices using folders, markers and documentation.
- Create reusable components: mappings, procedures, variables, sequences. Perform unitary tests.
- Assemble these components into packages. Perform integration tests.
- Release the work in scenarios.
- Optionally, organize scenarios into Load Plans. See Using Load Plans.
Creating a New Project
To create a project:
- In Designer Navigator, click New Project in the toolbar of the Projects accordion.
- Enter the Name of the project.
- Keep or change the automatically-generated project code. Because this code is used to identify objects within this project, oracle recommends using a compact string. For example, if the project is called Corporate Datawarehouse, a compact code could be CORP_DWH.
- From the File menu, click Save.
The new project appears in the Projects tree with one empty folder.
Managing Knowledge Modules
Knowledge Modules (KMs) are components of Oracle Data Integrator's integration technology. KMs contain the knowledge required by ODI to perform a specific set of tasks against a specific technology or set of technologies.
Oracle Data Integrator uses six different types of Knowledge Modules:
RKM (Reverse Knowledge Modules) are used to perform a customized reverse-engineering of data models for a specific technology. These KMs are used in data models. See Creating and Using Data Models and Datastores.
LKM (Loading Knowledge Modules) are used to extract data from source systems (files, middleware, database, etc.). These KMs are used in mappings. See Creating and Using Mappings.
JKM (Journalizing Knowledge Modules) are used to create a journal of data modifications (insert, update and delete) of the source databases to keep track of the changes. These KMs are used in data models and used for Changed Data Capture. See Using Journalizing.
IKM (Integration Knowledge Modules) are used to integrate (load) data to the target tables. These KMs are used in mappings. See Creating and Using Mappings.
CKM (Check Knowledge Modules) are used to check that constraints on the sources and targets are not violated. These KMs are used in data models' static check and mappings' flow checks. See Creating and Using Data Models and Datastores, and Creating and Using Mappings.
SKM (Service Knowledge Modules) are used to generate the code required for creating data services. These KMs are used in data models. See the Generating and Deploying Data Services chapter in Administering Oracle Data Integrator.
Project and Global Knowledge Modules
Knowledge Modules can be created and used as Project Knowledge Modules or Global Knowledge Modules. Global Knowledge Modules can be used in all projects, while Project Knowledge Modules can only be used within the project into which they have been imported.
Global KMs are listed in Designer Navigator in the Global Objects accordion, while Project KMs appear under the project into which they have been imported. See Importing Objectsfor more information on how to import a Knowledge Module.
ODI also provides Built-In KMs that are always present and don't need to be imported. All Built-In KMs are of type LKM or IKM and cover the technologies Oracle, File, and Generic. For more information about Built-In KMs see the Connectivity and Knowledge Modules Guide for Oracle Data Integrator.
When using global KMs, note the following:
Global KMs should only reference global objects. Project objects are not allowed.
You can only use global markers to tag a global KM.
It is not possible to transform a project KM into a global KM and vice versa.
If a global KM is modified, the changes will be seen by any ODI object using the Knowledge Module.
Be careful when deleting a global KM. A missing KM causes execution errors.
To distinguish global from project KMs, the prefix GLOBAL is used for the name of global KMs if they are listed with project KMs.
The order in which the global and project KMs are displayed changes depending on the context:
The KM Selector lists in the Mapping Editor displays first the project KMs, then the global KMs. The GLOBAL or PROJECT_CODE prefix is used.
The KM Selector lists in the Model editor displays first the global KMs, then the project KMs. The GLOBAL or PROJECT_CODE prefix is used.
Knowledge Module Naming Conventions
Oracle Data Integrator's KMs are named according to a convention that facilitates the choice of the KM. This naming convention is as follows:
Loading Knowledge Modules
They are named with the following convention: LKM <source technology> to <target technology> [(loading method)].
In this convention the source and target technologies are the source and target of the data movement this LKM can manage. When the technology is SQL, then the technology can be any technology supporting JDBC and SQL. When the technology is JMS, the technology can be any technology supporting JMS connectivity.
The loading method is the technical method used for moving the data. This method is specific to the technology involved. When no method is specified, the technical method used is a standard Java connectivity (JDBC, JMS and such) and data is loaded via the run-time agent. Using a KM that uses a loading method specific to the source and/or target technology usually brings better performances.
Examples of LKMs are given below:
LKM Oracle to Oracle (DBLink) loads data from an Oracle data server to another Oracle data server using the Oracle DBLink.
LKM File to Oracle (SQLLDR) loads data from a file into an Oracle data server using SQLLoader.
LKM SQL to SQL (Built-In) loads data from a data server supporting SQL into another one. This is the most generic loading Knowledge Module, which works for most data servers.
Integration Knowledge Modules
They are named with the following convention: IKM [<staging technology>] to <target technology> [<integration mode>] [(<integration method>)].
In this convention, the target technology is the technology of the target into which data will be integrated. IKMs may have a staging technology when the target is not located on the same server as the staging area. These KMs are referred to as Multi-technology IKMs. They are used when the target cannot be used as the staging area. For example, with the File technology.
The integration mode is the mode used for integrating record from the data flow into the target. Common modes are:
Append: Insert records from the flow into the target. It is possible to optionally delete all records from the target before the insert. Existing records are not updated.
Control Append: Same as above, but in addition the data flow is checked in the process.
Incremental Update: Same as above. In addition, it is possible to update existing records with data from the flow.
Slowly Changing Dimension: Integrate data into a table using Type 2 slowly changing dimensions (SCD).
The integration method is the technical method used for integrating the data into the target. This method is specific to the technologies involved. When no method is specified, the technical method used is a standard Java connectivity (JDBC, JMS and such) and SQL language. Using a KM that uses an integration method specific to a given technology usually brings better performance.
Examples of IKMs are given below:
IKM Oracle Merge integrates data from an Oracle staging area into an Oracle target located in the same data server using the incremental update mode. This KM uses the Oracle Merge Table feature.
IKM SQL to File Append integrates data from a SQL-enabled staging area into a file. It uses the append mode.
IKM SQL Incremental Update integrates data from a SQL-enabled staging area into a target located in the same data server. This IKM is suitable for all cases when the staging area is located on the same data server as the target, and works with most technologies.
IKM SQL to SQL Append integrates data from a SQL-enabled staging area into a target located in a different SQL-enabled data server. This IKM is suitable for cases when the staging area is located on a different server than the target, and works with most technologies.
Check Knowledge Modules
They are named with the following convention: CKM <staging technology>.
In this convention, the staging technology is the technology of the staging area into which data will be checked.
Examples of CKMs are given below:
CKM SQL checks the quality of an integration flow when the staging area is in a SQL-enabled data server. This is a very generic check Knowledge Module that works with most technologies.
CKM Oracle checks the quality of an integration flow when the staging area is in an Oracle data server.
Reverse-engineering Knowledge Modules
They are named with the following convention: RKM <reversed technology> [(reverse method)].
In this convention, the reversed technology is the technology of the data model that is reverse-engineered. The reverse method is the technical method used for performing the reverse-engineering process.
Examples of RKMs are given below:
RKM Oracle reverse-engineers an Oracle data model
RKM Netezza reverse-engineers a Netezza data model
Journalizing Knowledge Modules
They are named with the following convention: JKM <journalized technology> <journalizing mode> (<journalizing method>).
In this convention, the journalized technology is the technology into which changed data capture is activated. The journalizing mode is either Consistent or Simple. For more information about these modes, see Using Journalizing.
The journalizing method is the technical method for capturing the changes. When not specified, the method used for performing the capture process is triggers.
Examples of JKMs are given below:
JKM Oracle to Oracle Consistent (OGG Online) creates the infrastructure for consistent set journalizing on an Oracle staging server and generates the Oracle GoldenGate configuration for replicating data from an Oracle source to this staging server.
JKM Oracle Simple enables CDC for Oracle in simple mode using triggers.
JKM MSSQL Simple Creates the journalizing infrastructure for simple journalizing on Microsoft SQL Server tables using triggers.
Service Knowledge Modules
They are named with the following convention: SKM <data server technology>.
In this convention, the data server technology is the technology into which the data to be accessed with web services is stored.
Choosing the Right Knowledge Modules
Oracle Data Integrator provides a large range of Knowledge Modules out of the box. When starting an integration project, you can start with the built-in KMs introduced in ODI 12c, and import additional Knowledge Modules as needed for your project.
It is possible to import additional KMs after setting up the project, and it is possible to change the KMs used afterwards. The following guidelines can be used for choosing the right KMs when starting a new project:
Start with Generic KMs. The SQL KMs work with almost all technologies. If you are not comfortable with the source/target technologies you are working with, you can start by using the generic SQL KMs, as they use standard SQL. A simple project can start with the following generic KMs: LKM File to SQL, LKM SQL to SQL (Built-In), IKM SQL to SQL Append, IKM SQL Insert, CKM SQL.
Start with simple KMs. If you are not comfortable with the technologies you are integrating, do not start using the KMs using complex integration methods or modes.
Select KMs that match your source/target combinations to increase performance. The more specific the KM to a technology combination, the better the performance. For achieving the best performances, make sure to switch to KMs that match the source/target combination you have, and that leverage the features from these sources/targets.
Select KMs according to your infrastructure limitations. If it is not possible to use the target data servers as the staging area for security reasons, make sure to have multi-technology IKMs available in your project.
Select JKMs and SKMs only if you need them. Do not import JKMs or SKMs if you do not plan to use Changed Data Capture or Data Services. You can import them later when needed.
Review the KM documentation and options. KMs include a Description field that contain useful information. Each of the KM options is also described. All KMs are detailed in the Connectivity and Knowledge Modules Guide for Oracle Data Integrator.
Importing and Replacing Knowledge Modules
Two main operations allow you to manage KMs into a project:
When you create a new project you can use the Built-In KMs. If you want to use new KMs, you must import either a project KM or a global KM. See Project and Global Knowledge Modulesfor more information on the knowledge module's scope.
If you want to start using a new version of an existing global or project KM, or if you want to replace an existing KM in use with another one, then you can replace this KM.
This section includes the following topics:
Importing a Project Knowledge Module
To import a Project Knowledge Module into a project:
In the Projects accordion in Designer Navigator, select the project into which you want to import the KM.
Right-click and select Import > Import Knowledge Modules....
Specify the File Import Directory. A list of the KMs export files available in this directory appears. KMs included in the ODI installation are located in:
Select several KMs from the list and then click OK.
Oracle Data Integrator imports the selected KMs and presents an import report.
Click Close to close this report.
The Knowledge Modules are imported into you project. They are arranged under the Knowledge Modules node of the project, grouped per KM type.
Knowledge modules can be imported in Duplication mode only. To replace an existing Knowledge Modules, use the import replace method described below. When importing a KM in Duplication mode and if the KM already exists in the project, ODI creates a new KM with prefix
Replacing a Knowledge Module
When you want to replace a global KM or a KM in a project by another one and have all mappings automatically use the new KM, you must use the Import Replace mode. See Import Modesfor more information.
To import a Knowledge Module in replace mode:
In Designer Navigator, select the Knowledge Module you wish to replace.
Right-click and select Import Replace.
In the Replace Object dialog, select the export file of the KM you want to use as a replacement. KMs included in the ODI installation are located in:
The Knowledge Module is now replaced by the new one.
When replacing a Knowledge module by another one, Oracle Data Integrator sets the options in mappings for the new module using the option name similarities with the old module's options. When a KM option was set by the user in a mapping, this value is preserved if the new KM has an option with the same name. New options are set to the default value. It is advised to check the values of these options in the mappings.
Replacing a KM by another one may lead to issues if the KMs have different structure or behavior, for example when you replace a IKM with a RKM. It is advised to check the mappings' design and execution with the new KM.
Importing a Global Knowledge Module
To import a global knowledge module in Oracle Data Integrator:
In the Navigator, select the Global Knowledge Modules node in the Global Objects accordion.
Right-click and select Import Knowledge Modules.
In the Import dialog:
Select the Import Type. See Import Modes for more information.
Specify the File Import Directory. A list of the KMs export files available in this directory appears. KMs included in the ODI installation are located in:
Select the file(s) to import from the list.
The global KM is now available in all your projects.
Encrypting and Decrypting a Knowledge Module
Encrypting a Knowledge Module (KM) or Procedure allows you to protect valuable code. An encrypted KM or procedure cannot be read or modified if it is not decrypted. The commands generated in the log by an Encrypted KM or procedure are also unreadable.
Oracle Data Integrator uses a AES Encryption algorithm based on a personal encryption key. This key can be saved in a file and can be reused to perform encryption or decryption operations.
There is no way to decrypt an encrypted KM or procedure without the encryption key. Oracle therefore strongly advises keeping this key in a safe location. Oracle also recommends using a unique key for each deployment.
To Encrypt a KM or a Procedure:
In the Projects tree in Designer Navigator, expand the project, and select the KM or procedure you want to encrypt.
Right-click and select Encrypt.
In the Encryption Options window, you can either:
Encrypt with a personal key that already exists by giving the location of the personal key file or by typing in the value of the personal key.
Get a new encryption key to have a new key generated by ODI.
Click OK to encrypt the KM or procedure. If you have chosen to generate a new key, a window will appear with the new key. You can save the key in a file from here.
To decrypt a KM or a procedure:
- In the Projects tree in Designer Navigator, expand the project, and select the KM or procedure you want to decrypt.
- Right-click and select Decrypt.
- In the KM Decryption or Procedure Decryption window, either
Select an existing encryption key file;
or type in (or paste) the string corresponding to your personal key.
- Click OK to decrypt.
Organizing the Project with Folders
In a project, mappings, procedures, and packages are organized into folders and sub-folders. Oracle recommends maintaining a good organization of the project by using folders. Folders simplify finding objects developed in the project and facilitate the maintenance tasks. Organization is detailed in Organizing and Documenting Integration Projects..