As an avid user of IBM DataStage for over 8 years I was recently on a client engagement where I had to use Oracle’s Oracle Data Integrator (ODI). At first I was disappointed as I was out of my confront zone. But after a week or two I was very surprised how quickly I was able to pick up the tool and use it so easily. I must admit Oracle did a great job on putting together a great tool such as ODI as it is very simple to learn 
and use. With that said, I put together this blog to go over some of the features in which hopefully it will get your interested to check out the product.
Before I get started just a quick introduction on ODI. ODI is not an ETL tool it is a ELT tool. What’s that you ask? Oracle Data Integrator is also based on a unique E-LT (Extract – Load Transform) architecture which eliminates the need for a standalone ETL server and proprietary engine, and instead leverages the inherent power of your RDBMS engines. This combination provides the greatest productivity for both development and maintenance, and the highest performance for the execution of data transformation and validation processes. Traditional ETL tools operate by first Extracting the data from various sources, Transforming the data in a proprietary, middle-tier ETL engine that is used as the staging area, and then Loading the transformed data into the target data warehouse or integration server. Hence the term ETL represents both the names and the order of the operations performed, as shown in Figure 1-1.
Now that you have a high level understanding of Oracle’s ODI, I would like to go over some of ODI’s features. Today I will be covering the features of Oracle ODI 11.1.1. Please note with Oracle’s ODI newer version some of these features may or may no longer exist or the may be placed in other areas.
Oracle ODI Repositories:
Within Oracle’s ODI there are two types of repositories in which you should be aware of. Here is a quick blurb from Oracle.
Master Repository: This is a data structure containing information on the topology of the company’s IT resources, on security and on version management of projects and data models. This repository is stored on a relational database accessible in client/server mode from the different Oracle Data Integrator modules. In general, you need only one master repository. However, it may be necessary to create several master repositories in one of the following cases:
Project construction over several sites not linked by a high-speed network (off-site development, for example).
Necessity to clearly separate the interfaces’ operating environments (development, test, production), including on the database containing the master repository. This may be the case if these environments are on several sites.
Work Repository: This is a data structure containing information on data models, projects, and their use. This repository is stored on a relational database accessible in client/server mode from the different Oracle Data Integrator modules. Several work repositories can be created with several master repositories if necessary. However, a work repository can be linked with only one master repository for version management purposes.
Another great thing to point out in regards to Oracle’s repositories, within ODI you are able to setup your production repository execution only in which allows your developers to following a strict migration strategy so that code updates are not done within PROD.
Topology:
Guide to Oracle Cloud: 5 Steps to Ensure a Successful Move to the Cloud
Explore key considerations, integrating the cloud with legacy applications and challenges of current cloud implementations.
Now that you have a brief understanding of ODI’s repositories another topic I would like to cover is ODI’s Topology. ODI’s topology is comprised of the following components, “Physical Architecture, Contexts, Logical Architecture, Languages, Repositories and Generic Action.” Here you will be setting up your connections for your sources and targets. Now rather than me going into details about each component of ODI’s topology I ask you to check out Peter Scott from RittmanMead who puts together a very details post explaining the different components of ODI’s topology.
Oracle Data Integrator Topologies for OWB Developers
Security:
Another component that makes up ODI is security. The security is very easy to use and manage within ODI. ODI’s security is comprised of Profiles, Users and Objects. The users section allows you to create the users to have access for ID and you are able to associate certain profiles in which allows your users to have certain privileges within ODI.
Now the security object is a representation of a design-time or run-time artifact handled through Oracle Data Integrator. Examples of objects include agents, projects, models, data stores, scenarios, mappings, and even repositories. Specific objects have a double name, such as Agent/Context and Profile/Method. These objects represent links between objects. These links are also objects. For instance, Agent/Context corresponds to a physical/logical agent association made through the contexts. Privileges on this object enable the ability to change this association in the topology.
An instance is a particular occurrence of an object. For example, the Datawarehouse project is an instance of the Project object.
A method is an action that can be performed on an object, such as edit, delete, and so on. Each object has a predefined set of methods.
Operator:
Another major component of ODI is the operator tab. The ODI operator tab allows you to schedule load plans and scenarios as well as also you can kick off these load plans and scenarios. In addition, you can monitor the progress of your load plans, scenarios, interfaces, and packages. I will touch base on interfaces and packages when I discuss the design ta. Check the following post from gerardnico.com that covers in more details ODI operator’s tab.
Designer:
Lastly, is ODI’s designer tab. This is the heart and soul of ODI. Here you are able to create your interfaces and packages to E-LT your data into your source and target systems.
Designer Navigator is used to design data integrity checks and to build transformations such as for example:
- •Automatic reverse-engineering of existing applications or databases
- •Graphical development and maintenance of transformation and integration interfaces
- •Visualization of data flows in the interfaces
- •Automatic documentation generation
- •Customization of the generated code
- •The main objects you handle through Designer Navigator are Models and Projects.
Well hopefully you enjoyed my post and I hope I was able to give you a high level introduction to Oracle’s Oracle Data Integrator (ODI). For more information on Oracle ODI check out Introduction to Oracle Data Integrator from Oracle. Also, I urge you to check out a video by Tim Garrod from Raymond James who gives his customer success story with Oracle ODI.
References:
http://docs.oracle.com/cd/E21764_01/integrate.1111/e12641/overview.htm http://docs.oracle.com/cd/E21764_01/integrate.1111/e12643/intro.htm http://docs.oracle.com/cd/E21764_01/integrate.1111/e12643/admin_reps.htm http://docs.oracle.com/middleware/1212/odi/ODIDG/security.htm#CHDCBGED http://docs.oracle.com/cd/E21764_01/integrate.1111/e12643/intro.htm#BABFFBFH
