Etl Integrator

Sun SeeBeyond? ETL Integrator is a Data Integration tool which can be used to preform extract - transform - load to build Data Warehousing|datawarehouse or data migration. The tool has a design time and a run time component as explained below. This tool provide connectivity to a vast range of heterogeneous and diversified data sources including non database sources.

ETL Service Engine (SE) ETL Service Engine is a Java Business Integration|JBI (JSR-208) based Service Engine which can expose the ETL operations as web services and is part of OpenESB Java Business Integration#Existing JBI Components|Component Library.

The ability of the ETL Service Engine to expose ETL operations as web services makes the tool suitable for Business Integration application based on Service Oriented Architecture (SOA). This engine is specially designed to work with high volume data with high performance. This open source JBI component can be deployed in any JBI container including OpenESB, ServiceMix? etc.

The ETL Service Engine package an embedded database engine and has the ability to execute SQL on non-database data sources.

ETL Editor for Netbeans IDE (Enterprise Pack) ETL Editor has many predefined transformations as well as cleansing operators/functions. It also allows the user to add user defined functions. This editor is used as design time component which lets the user design the ETL collaborations and create artifacts that can be deployed as ETL Service Engine.

Supported Data Sources

• Oracle (Oracle 8 and higher)
• Sybase
• DB2 (Version 5 and higher)
• SQL Server
• Derby
• Axion
• Spreadsheets
• HTML/Web Tables
• Flat files (CVS, Delimited and Fixed Width)
• Other Databases like Access, MySQL etc. through JDBC driver.

Execution Strategies

Process of extraction, transformation and loading in ETL Integrator engine can be classified as below.

• Direct or Simple (No Extraction or No Load) - All the source tables and the Target table reside in the same database.
• One Pass (No Extraction Required, Transform and then Load) - All the source tables are in the same database.
• Staging (Extract Load and Transform - ELT) - Source tables are scattered across different data sources.
• Pipeline (Federated database|Data Federation) - Uses an embeded virtual database to give a single view to different data sources.

Transformation Operators Apart from Standard SQL operators/function, ETL Integrator support “Java Operators” and Custom/User Defined operators.

Example include, cleansing and validation operators such as parsing business name and address, Zip code, SSN data validation and date transformation operators.

Data Quality To ensure data quality ETL Integrator allows user to configure data validation logic and user can view the rejected data thru web console and resubmit after making correction to reprocess the data. ETL Integrator also automatically generate code to ensure data integrity.

Demos and Tutorials

The http://www.glassfishwiki.org/jbiwiki/Wiki.jsp?page=ETLSE has quite a few demos and tutorials.