Popular ETL Tools-Famous ETL Tools in Market

ETL tools – see the table below – are widely used for extracting, cleaning, transforming and loading data from different systems, often into a data warehouse. The following ETL tools were thoroughly examined on 90 criteria.

Listed below are the products that are included in our ETL tool comparison. The numbers have no significance in terms of how highly the products scored in the evaluation. Click on the name of the ETL tool for more information and discover what the best ETL tool is for your organization.

No.

List of ETL Tools

Version

ETL Vendors

1.	Oracle Warehouse Builder (OWB)	11gR1	Oracle
2.	Data Services	XI 4.0	SAP Business Objects
3.	IBM Infosphere Information Server	9.1	IBM
4.	SAS Data Integration Studio	9.4M1	SAS Institute
5.	PowerCenter Informatica	9.5	Informatica
6.	Elixir Repertoire	7.2.2	Elixir
7.	Data Migrator	7.7	Information Builders
8.	SQL Server Integration Services	10	Microsoft
9.	Talend Studio for Data Integration	5.2	Talend
10.	DataFlow Manager	6.5	Pitney Bowes Business Insight
11.	Pervasive Data Integrator	10.0	Actian (Pervasive Software)
12.	Open Text Integration Center	7.1	Open Text
13.	Oracle Data Integrator (ODI)	11.1.1.5	Oracle
14.	Data Manager/Decision Stream	8.2	IBM (Cognos)
15.	Clover ETL	3.4.1	Javlin
16.	Centerprise	6.0	Astera
17.	DB2 Infosphere Warehouse Edition	9.1	IBM
18.	Pentaho Data Integration	4.1	Pentaho
19	Adeptia Integration Suite	5.1	Adeptia
20.	DMExpress	5.5	Syncsort
21.	Expressor Data Integration	3.7	QlikTech
22.	Relational Junction ETL Manager	5.3	Sesame Software

ETL Process flow

During Extraction, the desired data is identified and extracted from many different sources,  including database systems and applications. Very often, it is not possible to identify the specific  subset of interest; therefore more data than necessary has to be extracted, so the identification of  the relevant data will be done at a later point in time. After extracting data, it has to be physically transported to an intermediate system for further processing. 

Depending on the chosen way of transportation, some transformations can be done during this  process, too. For example, a SQL statement which directly accesses a remote target through a  gateway can concatenate two columns as part of the SELECT statement. Based on the  requirements, some transformations may take place during the Transformation and Execution  Phase. Through Informatica mappings, the necessary changes and updates of the data are made  using transformations. 

Then in the Load phase the data is loaded in the target. After all the transformations, it has to be  physically transported to the target system for loading the data into the Target.

ETL Loading process

In loading stage, some data are loaded to the target directly without applying any transformation logic and some data are loaded to the target after applying the logic or business rules. The load phase loads the data into the end target, usually the Data Warehouse (DW). Depending on the requirements of the organization, this process varies widely. Some data warehouses may overwrite existing information with cumulative, updated data every week, while other DW (or even other parts of the same DW) may add new data in a historized form, for example, hourly. The timing and scope to replace or append are strategic design choices dependent on the time available and the business needs. More complex systems maintain a history and audit trail of all  changes to the data loaded in the DW

As the load phase interacts with a database, the constraints defined in the database schema, as well as in triggers activated upon data load apply (for example, uniqueness, referential integrity, mandatory fields), which also contribute to the overall data quality performance of the ETL  process. 

ETL Transformation process

ETL Transformation process :: The transform stage applies to a series of rules or functions to the extracted data from the source to derive the data for loading into the end target. Some data sources will require very little or even no manipulation of data. In other cases, one or more of the following transformations types to meet  the business and technical needs of the end target may be required: 

• ƒ Selecting only certain columns to load (or selecting null columns not to load) 
• ƒ Translating coded values and automated data cleansing 
• ƒ Encoding free-form values 
• ƒ Deriving a new calculated value 
• ƒ Filtering 
• ƒ Sorting 
• ƒ Joining data from multiple sources 
• ƒ Aggregation 
• ƒ Generating surrogate-key values 
• ƒ Transposing or pivoting columns 
• ƒ Splitting a column into multiple columns 

Applying any form of simple or complex data validation. If validation fails, it may result in a full, partial or no rejection of the data, and thus none, some or all the data is handed over to the next step, depending on the rule design and exception handling. Many of the above transformations may result in exceptions, for example, when a code translation parses an unknown code in the extracted data.

In Source, the data available is First name and Last name. To get the full name, the transformation logic is applied by concatenating both the first and last name. The place where these transformations take place is called the Staging Area.

ETL Data Extraction process

The first part of an ETL process involves extracting the data from the source systems. Most data warehousing projects consolidate data from different source systems. Each separate system may also use a different data organization / format. Common data source formats are relational databases and flat files, but may include non-relational database structures such as Information Management System (IMS) or other data structures such as Virtual Storage Access Method (VSAM) or Indexed Sequential Access Method (ISAM), or even fetching from outside sources such as web spidering or screen-scraping. Extraction converts the data into a format for transformation processing.An intrinsic part of the extraction involves the parsing of extracted data, resulting in a check if the  data meets an expected pattern or structure. If not, the data may be rejected entirely.

ETL Terms

Source System 

Source System is a database, application, file, or other storage facility from which the data in a data warehouse is derived. Some of them are Flat files, Oracle Tables, Microsoft SQL server tables, COBOL Sources, XML files. 

Mapping 

Mapping is the definition of the relationship and data flow between source and target objects. It is a pictorial representation about the flow of data from source to target. 

Metadata 

Metadata describes data and other structures, such as objects, business rules, and processes. For  example, the schema design of a data warehouse is typically stored in a repository as metadata,  which is used to generate scripts used to build and populate the data warehouse. Metadata contains all the information about the source tables, target tables, the transformations, so that it will be useful and easy to perform transformations during the ETL process. A repository contains metadata. 

Staging Area 

Staging area is place where you hold temporary tables on data warehouse server. Staging tables are connected to work area or fact tables. We basically need staging area to hold the data, and perform data cleansing and merging, before loading the data into warehouse. Informatica 

 

Data Cleansing 

It is the process of resolving inconsistencies and fixing the anomalies in source data, typically as part of the ETL process. The data cleansing technology improves data quality by validating, correctly naming and standardizing data. For example, a person's address may not be same in all source systems because of typos and postal code, city name may not match with address. These errors can be corrected by using data cleansing process and standardized data can be loaded in target systems (data warehouse). 

Transformation 

Transformation is the process of manipulating data. Any manipulation beyond copying is a transformation. Examples include cleansing, aggregating, and integrating data from multiple sources. A transformation is a repository object that generates, modifies, or passes data. Target System  Target System is a database, application, file, or other storage facility to which the "transformed  source data" is loaded in a data warehouse. 

What is ETL ?

ETL stands for Extraction, Transformation and Loading. ETL is a process that involves the 

following tasks: 

• Extracting data from source operational or archive systems which are the primary source of data for the data warehouse 
• Transforming the data - which may involve cleaning, filtering, validating and applying  business rules 
• Loading