Getting Started with Oracle Data Integrator 11g: A Hands-On Tutorial: This is a brilliant crash course in Oracle Data Integrator that pulls you straight into the platform through practical instructions and real-world situations rather than dry theory. Written by a team of seasoned experts.

To store all its information, ODI requires a repository. The repository is by default a pair of schemas (called Master and Work repositories) stored in a database. Unless ODI is running in a near real time fashion, continuously generating SQL code for the databases to execute the code, there is no need to dedicate a database for the ODI repository. Most customers leverage existing database installations, even if they create a dedicated tablespace for ODI.

Repository overview

The only element you will never find in the repository is the actual data processed by ODI. The data will be in the source and target systems, and will be moved directly from source to target. This is a key element of the ELT architecture. All other elements that are handled through ODI are stored into the repository. An easy way to remember this is that everything that is visible in the ODI Studio is stored in the repository (except, of course, for the actual data), and everything that is saved in the ODI Studio is actually saved into the repository (again, except for the actual data).

The repository is made of two entities which can be separated into two separate database schemas, namely, the Master repository and the Work repository.

We will look at each one of these in more detail later, but for now you can consider that the Master repository will host sensitive data whereas the Work repository will host project-related data. A limited version of the Work repository can be used in production environments, where the source code is not needed for execution.

Lifecycle management and repositories

We now know that there will be different types of repositories. All enterprise application development teams have more than one environment to consider. The code development itself occurs in a development environment, the validation of the quality of the code is typically done in a test environment, and the production environment itself will have to be separate from these two. Some companies will add additional layers in this lifecycle, with code consolidation (if remote developers have to combine code together), user acceptance (making sure that the code conforms to user expectations), and pre-production (making sure that everything works as expected in an environment that perfectly mimics the production environment).

In all cases, each environment will typically have a dedicated Work repository. The Master repository can be a shared resource as long as no network barrier prevents access from Master to Work repository. If the production environment is behind a firewall for instance, then a dedicated Master repository will be required for the production environment.

The exchange of metadata between repositories can be done in one of the following ways:

• Metadata can be exchanged through versioning. All different versions of the objects are uploaded to the Master repository automatically by ODI as they are created. These versions can later be restored to a different Work repository attached to the same Master repository.

• All objects can be exported as XML files, and XML files can be used to import the exported objects into the new repository. This will be the only option if a firewall prevents connectivity directly to a central Master repository.

In the graphical representations shown previously, the leftmost repository is obviously our development repository, and the rightmost repository is the production repository. Why are we using an execution for the test environment? There are two rationales for this. They are as follows:

• There is no point in having the source code in the test repository, the source code can always be retrieved from the versioning mechanisms.

• Testing should not be limited to the validation of the artifacts concocted by the developers; the process of migrating to production should also be validated. By having the same setup for our test and production environments, we ensure that the process of going from a development repository to an execution repository has been validated as well.

The ODI Studio is the graphical interface provided to all users to interact with ODI.

People who need to use the Studio usually install the software on their own machine and connect to a shared repository. The only exception would be when the repository is not on the same LAN as the Studio. In that case, most customers use Remote Terminal Service technologies to ensure that the Studio is local to the repository (same LAN). Only the actual display is then sent over the WAN.

The Studio includes four navigators that are typically used by different users who can share the same objects and the same repository. Some users may not have access to some navigators, depending on their security profiles. The navigators are as follows:

The ODI Agent is the component that will orchestrate all the operations. If SQL code must be executed by a database (source or target), the agent will connect to that database and will send the code (DDL and DML, as needed) for that database to perform the transformations. If utilities must be used as part of the transformations (or, more likely, as part of the data transfer) then the agent will generate whatever configuration files or parameter files are required for the utility, and will invoke this utility with the appropriate parameters—SQL Loader, BCP, Multiload, and NZload are just a small list of such utilities.

There are two types of ODI Agent, namely, the standalone agent (available in all releases of ODI) and the JEE agent (available with ODI 11g and after) that runs on top of WebLogic Server. Each type has its own benefits, and both types of agents can co-exist in the same environment:

A common configuration is to use the JEE agent as a "Master" agent, whose sole purpose it is to distribute execution requests across several child agents. These children can very well be standalone agents. The master agent will know at all times which children are up or down. The master agent will also balance the load across all child agents.

In a pure standalone environment, the Agent is often installed on the target server. Agents are also often installed on file servers, where they can leverage database loading utilities to bulk load data into the target systems. Load balancing can also be done with a standalone master agent. Multiple standalone agents can run on the same server, as long as they each have a dedicated port. This port number is defined in the Topology navigator, where the agent is defined.

The Agent can receive execution orders from different origins as follows:

The Console is an HTML interface to the repository. The Console is installed on a WebLogic Server (other application servers will be supported with later releases of the product).

The Console can be used to browse the repository, but no new developments can be created through this interface.

The Console is useful for viewing lineage and impact analysis without having the full Studio installed on a machine. Operators can also perform most of the tasks they would perform with the Studio, including starting or restarting processes.

The exact information that is available in the Operator Navigator of the Studio will be found in the matching view of the Console: generated code, execution statistics, and statuses of executed processes are all available.

As part of the consolidation of features across all Oracle product lines, ODI now integrates with WebLogic Enterprise Manager.

Administrators can now use one single tool (OEM) to monitor the overall health of their environment, including ODI Agents and ODI processes.

Everyone encounters the same issue. The parameters used to connect to the development server are different from the parameters used in the QA or production servers and there could be more than these few environments. Some companies add environments such as user acceptance, code consolidation, and pre-production; you name it and it is there! Maintaining the connection parameters is a cumbersome activity. Beyond the maintenance itself, there is a risk if these parameters are modified in the code after the code has been validated by the QA team. Ideally, connection parameters and environment-specific parameters should be maintained independently of the code. This is what ODI provides with the notion of Execution Contexts.

To make sure that developers are independent of the physical location of the different systems, ODI enforces the use of Logical Schemas. Logical Schemas are labels or aliases that represent the connections for the developers. At execution time, the agent will translate these logical names into physical ones based on the information stored in the repository. This way, the maintenance of the connection parameters, location of the databases, and schema names is entirely independent of the code itself.

In addition, whenever an ODI process is executed, a Context must be selected. The structure of the metadata will always be the same from one environment to the next (say for instance, development, QA, and production) but the connection information will be different. By selecting an execution context, the agent will know which connection definition to use when completing the code.

In the previous figure, the logical name Datawarehouse is actually pointing to the physical schema db_dwh in the development server as long as we execute the transformations in the development context. It will automatically point to the dwh schema in the QA server for the QA context.

The physical details are maintained in the Physical Architecture of the Topology Navigator and the logical names that point to the physical implementations are defined in the Logical Architecture of the Topology Navigator. The Context accordion lets administrators define as many contexts as needed. At run time, the selection of a Context will define which physical implementation to use for each logical name used in the code.

The physical architecture will contain the details for ODI to log in to the database, the IP address or name of the server on which the database runs, the port numbers to connect to the database, as well as the name of the actual schemas, catalogs, libraries, and other objects found in these databases.

A Knowledge Module (KM) is a template of code that defines what types of SQL queries (insert, select, and so on) and what scripts need to be generated to complete the data extraction, loading, and transformation operations.

There were several objectives behind the inception of these KMs. Some of them are as follows:

Typically, an ETL developer will be able to use a Knowledge Module without any need to understand the underlying requirements (creation of staging tables, location of these tables, intermediate operations on these tables, cleanup of these tables, and so on).

The developer will control what code will be generated by setting yes/no options to insert records, perform updates, create the target table, and so on.

There are six types of Knowledge Modules that will enable various steps in the data integration process:

Knowledge Modules are named after the specific database for which they have been optimized, the utilities that they leverage, and the technique that they implement. For instance, an IKM Teradata to File (TTU) will move data from Teradata into a flat file, and leverage the TTU utilities for that operation, or an LKM File to Oracle (EXTERNAL TABLE) will expose a flat file as an external table for Oracle. Similarly, an IKM Oracle Slowly Changing Dimension will generate code optimized for the Oracle database which implements a Slowly Changing Dimension (Type 2) type of integration.

Most developers will only use out of the box KMs—only the most advanced developers will modify the code templates. For that reason, we will keep KM modifications outside the scope of this book. But the examples we will go through in the rest of this book will help you understand how best to select a KM for the task at hand.

Models in ODI are used to store the metadata imported from the databases. When developers (or data custodian) create a model, they have the choice of importing only the relevant metadata (you may not want to import the definition of all tables and views in your environment). As we have seen earlier with Knowledge Modules for reverse-engineering, metadata can also be imported from applications, where objects can be a business representation of the data rather than an actual physical table.

Once the metadata has been imported, it can be organized and enhanced—Models can be grouped in folders. Submodels can be created within Models to organize tables in logical units.

When submodels are created, the users can define how tables will be organized; either they will be manually moved into the appropriate subfolders, or their location will be determined automatically by ODI based on their names.

Metadata can be enhanced by adding more constraints (such as referential integrity, check constraints) that would not exist in the database.

Metadata can also be directly created in ODI. Each model has a Diagrams folder. Diagrams let users graphically design their table structures. More importantly, they can drag-and-drop object definitions from other technologies, and ODI will automatically translate the datatypes from one technology to the next.

If tables are derived from another technology, they can still be manually modified (by changing the column names, datatypes, adding or removing columns, and constraints). The hidden benefit of the use of diagrams to create tables from other Models is the ability to automate the generation of Interfaces.

ODI knows what table was used as a basis for the new one. If you right-click on the model name, and select the menu option Generate Interfaces IN, ODI will automatically create an interface with the original table as a source and the new table as a target with all columns properly mapped. You will be prompted to place the interface in the project and folder of your choice. We will take a detailed look at Interfaces in the next section. If you choose to select the menu option Generate Interfaces OUT, ODI will generate the reverse interface, with the new table as a source and the original one as a target.

Interfaces are where the transformations are defined. We will spend a lot of time in this book covering the details of Interfaces. Interfaces are created in projects, and the various components are organized in Packages.

There are multiple parts to an interface such as description, mappings, flow, controls, to name a few.

Interface flow tab

The flow tab of the interfaces is where the integration strategies are selected. The Loading Knowledge Modules (LKM) will define which techniques must be used to extract data from remote source systems and how to load the data in the target system. The Integration Knowledge Module (IKM) will define which integration strategy will be used to integrate the data in the target system.

Interfaces will only list the KMs that have been imported into the project where they are created (and starting with ODI 11.1.1.6, the Global KMs that are shared across projects). One key benefit is that it is possible to control which strategies will be used for a given project, hence limiting the choices of the developers to the choices made by the team.

This will ensure the following:

• All developers implement the same best practice

• If changes are required later in the project, they can be done extremely efficiently by changing the standard for a given project

• Ideally, because the choice can be limited, developers know that ODI will always choose the KM they need and do not even have to worry about that step

Packages are designed to orchestrate the individual objects created by the developers—interfaces, variables, procedures will be sequenced in Packages. If a step fails in a package, it is possible to branch out and immediately take action based on the errors that are detected. For execution purposes, Packages are compiled into Scenarios. Scenarios execution can then be organized with Load Plans. We will see all these concepts in more detail in a dedicated chapter.