Author: Steve Karam

Data Integration is the process of combining data from different resources and providing a unified view of this data. Oracle provides a number of data integration tools. Oracle data integration tools enable you to integrate data from various sources into a central repository. These tools allow heterogeneous access from a wide range of data sources. For example you can access data from CSV, DB2, MySQL, SQL Server, WSDL, XML etc.

Extract, transform and load (ETL):

ETL is one of the most common methods of extracting data from different sources and integrating it into a central warehouse. In Extract process the data is extracted from different sources. In Transform process the extracted data is cleaned and standardized into one set format. In Load process, data loading mechanism is used to load transformed data into data warehouse.

Extract, load and transform (ELT):

ELT is similar to ETL except that you decide whether transformation will take place on the source database or target database. ELT tools use code and data movement methods in order to eliminate the need for a staging database system.

Enterprise Information Integration (EII):

Enterprise Information Integration enables us to view all data within an organization through a single interface. EII represents data into a single set of structures and naming conventions. The data is brought together when required and hence fewer resources are spent in extracting and loading data and more resources are available for bringing data from various sources.

Oracle Data Integrator (ODI):

Oracle Data Integrator is a powerful and inclusive data integration platform that meets all data integration requirements like high-volume and high-performance batches, event-driven batches, trickle-feed integration processes and SOA-enabled data services.

ODI provide knowledge modules with the help of which you can pull data from various sources, clean it and load to a number of targets. ODI performs data integration similar to ELT processing by using process flows and data mapping. First data is extracted from multiple sources, then transformation is applied and finally transformed data is loaded into a final destination. The knowledge modules define whether the transformations will occur on source systems or on the target environment. Also native hooks are built into the ODI software and provide data services to SOA suite.

Oracle Business Intelligence Publisher (BI Publisher):

Oracle Business Intelligence Publisher was previously known as XML Publisher. With the help of Oracle BI Publisher you can define multiple data sources like XML flat files, web services or direct database connections.

Complex reports can be produced with this on-demand data. You can create data models against any data source and build custom applications leveraging existing data sources and infrastructure. Familiar desktop tools enable you to easily design report layouts. Oracle BI Publisher can generate tens of thousands of documents per hour. Reports can be viewed online or scheduled for delivery to a wide range of destinations.

Enterprise Service Bus (ESB):

Oracle Enterprise Service Bus is a part of Oracle SOA Suite. ESB is able to extract data from web services. Then transformation mapping is applied on it hence converting it to usable information. Finally the data can be loaded into data warehouse.

Oracle Transparent Gateways:

Oracle Transparent Gateways helps you create database links that point to external database resources like SQL Server and access data from such sources. Oracle scripts are used to extract the data as if it were a standard Oracle table for processing.

You can write queries to join tables within one database to Oracle tables in an integrated fashion. Thus you can perform real-time queries without physical consolidation.

SQL* Loader:

SQL*Loader is used for moving data from external files into the Oracle database. It can load both simple and complex flat files into staging tables for processing. SQL*Loader supports various load formats.

PL/SQL Code:

You can write PL/SQL code to clean, manage and integrate your data.

Final Words:

To conclude I would say that Oracle data integration tools have made data in a number of databases highly accessible. Read Again!!

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Tricky Password Features in Oracle 11g!!
   Quick & Efficient Switchover in a RAC Environment!!
   A Guide to Oracle Bitmap Index Techniques!!
   Fast Data Loading in Oracle: Exciting prospects!!