Infrastructure at your Service

Matthieu Munch

Oracle Data Integrator – The EL-T tool from Oracle

Oracle Data Integrator is the ETL tool that Oracle has developed to enter into the world of Business Intelligence. Taking advantage of its incredible experience in database managing, Oracle has created an ETL tool that is flexible to use, powerful and that allows the integration of data coming from another sources.

ODI_P1

 Agility of use

Oracle Data Integrator uses various types of graphical interfaces during all stages of data flow creation. Here is an example of data transformation flows. The different interfaces are very intuitive and easy to use.
ODI_P11

On this picture, you can see that the user interface is split in 3 big windows.
– The project element
– The Workflow design
– The Predefined components
The project element window centralizes all components, database connection or workflow you have created for the project. All information is centralized on one location.
The workflow design uses the drag and drop technology. In the same time, you can use predefined components that are SQL instructions or script. Using these components avoids writing many complex lines of code that can lead to syntax errors. Although each performed module or task can be controlled and customized.

The Loading Knowledge Module and the Integration Knowledge Module

One of the most powerful function from ODI is that you can choose the data loading and the Data Integration methodology without using code.
For example you can use an Oracle to Oracle push or pull method for loading the data or you can choose a multi connect or a specific SQL script. These options allow a better data transfer between the source and the target table.

ODI_P12
The Integration Knowledge Module allows to choose the data upgrade or integration strategy. You have multiple actions that you can do: Oracle incremental update, Oracle update … All these functions are still modeled in these knowledge modules.

ODI_P13

The Check Knowledge Module

This module is used to check the constraints of the datastore and to create an error table. This module can use the Oracle Check module or a specific SQL script.

ODI_P14

Powerful tool

Each operation on data such as select, create, lookup, … have been integrated into the modules, thus avoiding to write many complex lines of code that can lead to syntax errors. Although each performed module or task can be controlled and customized.

ODI_P3

In addition, ODI uses a very powerful debugger tool that allows checking each task from the data transform process.

ODI_P4

Many external data sources access

A good ETL tool must now connect to most database or data types that exist on the market. That’s why ODI provides access to a many database, including big data, making it one of the most connected tools on the market.

ODI_P5ODI_P5Bis

E-LT, the Business Intelligence 2.0

ODI is based on a new technological concept called E –LT (Extract – Load, Transform). Most of the data integration tools use the technology ETL (Extract – Load – Transform) In other terms, the data first have to be extracted from their base and copied in a temporary database. Then in a second time, data are transformed and stocked in the real data warehouse. These processes cost time, RAM and CPU.

The E-L.T. technology changes the processes sort. It takes advantages from the hardware and software technologies used in the source and target database. The data is extracted and directly copied in the data warehouse. The data transform processes are directly made on the data located in the data warehouse. This new process avoids another data transfer that cost time. At least, this technology allows refreshing frequently the data because we can choose the dataflow we want to update.

ODI_P6

Classical ETL schema                                                     E-LT schema

ODI_P7            ODI_P8

Oracle Data Integrator and Oracle Golden Gate

More and more customer’s solutions are using Oracle Golden Gate and Oracle Data Integrator at the same time. What advantages do we have to use these two technologies together?

  • First advantage, Oracle Golden Gate allows data replication with a granularity size up to the table. The data transfer to ODI can be focused on the relevant data.
  • Second advantage: Oracle Golden Gate job transfer can be triggered. That allows launching an “on demand” data updating process and in certain cases a “real time” data updating process.

ODI_P9

Oracle Data Integrator and Enterprise Data Quality

The data quality is becoming a very big problem for the companies, due to the explosion of data quantity to analyze. For a company, to analyze the good data is primordial. A business analyze made on bad data can lead the company to take bad decisions. That’s why ODI can use EDQ flows to be sure that the data that have been validated.

ODI_P10

Conclusion

In conclusion, we can say that Oracle Data Integrator is one of the most powerful data integration software on the market. In addition, the possibility of using an ODI instance in the cloud allows it to be very versatile. And finally, the association ODI – Oracle Golden Gate with the E-L.T. technology can be very powerful for having operational data in your data warehouse in a minimum of time. In a next blog, I will present you Oracle Enterprise Data Quality, a module for managing you data quality.

One Comment

Leave a Reply

Matthieu Munch
Matthieu Munch

Consultant