ETL Tool:
What is ETL?
ETL is short form of Extract, Transform, Load. three database functions that are combined into one tool to pull data out of one database and place it into another database. It is mainly used to migrate data from one database to another, to form data marts and data warehouses and also to convert databases from one format/type to another.
ETL systems are commonly used to integrate data from multiple applications, typically developed and supported by different vendors or hosted on separate computer hardware.
Let’s take a simple example of an university to understand this. Let’s Say, Your University principal wants the list of students who are successfully clearing tests every month. In another words, How many (%) students are failing and in which subject they are failing in?
So the Principal would need an application that would do three main functions:
- Pull data from exam management system and HR system – EXTRACT
- Consolidate the pull data to sum up the student marks and segregate it with keys like student id, subject, marks – TRANSFORM
- Place consolidated data into Principal’s application – LOAD
Why ETL?
Organizations need to make wise decisions to make profits, For which the OLTP data is insufficient and results in poor performance. Thus the need of Data-warehouse comes into picture, Which is a repository to store the data from all sources in a transformed manner which helps in analyzing data.
The question here is “How to extract, change and transfer data generated by source systems to Data-warehouse / Data Marts which would be further used by a reporting tool to generate required reports? ”.
The process of Extracting Transforming and Loading can be done in many ways (like by using using SQL, procedures, ...). However using an apt ETL tool makes it faster ,accurate, easy and efficient. One can focus on the business perspective than on syntax.
This is where “ETL” comes into the picture...
As already mentioned, 3 main processes involved in ETL(as the name suggests!!) are:
Extract:
The first part of an ETL process involves extracting the data from the source systems. In many cases this is the most challenging aspect of ETL, since extracting data correctly sets the stage for how subsequent processes go further. Most data warehousing projects consolidate data from different source systems. Each separate system may also use a different data organization and/or format. Common data source formats are relational databases and flat files.
Transform:
The Transform is the process of converting the extracted data from its previous form into the form it needs to be in, by applying a series of rules or functions to the extracted data, so that it can be load into the target database/Files. Transformation occurs by using rules or lookup tables or by combining the data with other data. Some data sources require very little or even no manipulation of data. In other cases, one or more of the following transformation types may be required to meet the business and technical needs of the target database:
- Selecting only certain columns to load (or selecting null columns not to load). For example, if the source data has three columns (also called attributes), for example roll_no, age, and salary, then the extraction may take only roll_no and salary. Similarly, the extraction mechanism may ignore all those records where salary is not present (salary = null).
- Translating coded values (e.g., if the source system stores 1 for male and 2 for female, but the warehouse stores M for male and F for female)
- Encoding free-form values (e.g., mapping "Male" to "M")
- Deriving a new calculated value (e.g., sale_amount = qty * unit_price)
- Sorting
- Joining data from multiple sources (e.g., lookup, merge) and deduplicating the data
- Aggregation (for example, rollup — summarizing multiple rows of data — total sales for each store, and for each region, etc.)
- Generating surrogate-key values
- Transposing or pivoting (turning multiple columns into multiple rows or vice versa)
- Splitting a column into multiple columns (e.g., converting a comma-separated list, specified as a string in one column, into individual values in different columns)
- Disaggregation of repeating columns into a separate detail table (e.g., moving a series of addresses in one record into single addresses in a set of records in a linked address table)
- Lookup and validate the relevant data from tables or referential files for slowly changing dimensions.
- 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.
Load:
Load is the process of writing the data into the target database, 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 information; frequently, updating extracted data is done on a daily, weekly, or monthly basis. Other data warehouses (or even other parts of the same data warehouse) may add new data in a historical form at regular intervals -- for example, hourly. To understand this, consider a data warehouse that is required to maintain sales records of the last year. This data warehouse overwrites any data older than a year with newer data. However, the entry of data for any one year window is made in a historical manner. The timing and scope to replace or append are strategic design choices dependent on the time available and the business needs. More complex systems can maintain a history and audit trail of all changes to the data loaded in the data warehouse.
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.
- For example, a financial institution might have information on a customer in several departments and each department might have that customer's information listed in a different way. The membership department might list the customer by name, whereas the accounting department might list the customer by number. ETL can bundle all this data and consolidate it into a uniform presentation, such as for storing in a database or data warehouse.
- Another way that companies use ETL is to move information to another application permanently. For instance, the new application might use another database vendor and most likely a very different database schema. ETL can be used to transform the data into a format suitable for the new application to use.
- An example of this would be an Expense and Cost Recovery Systems (ECRS) such as used by accountancy, consultancies and lawyers. The data usually ends up in the time and billing system, although some businesses may also utilize the raw data for employee productivity reports to Human Resources (personnel dept.) or equipment usage reports to Facilities Management.
The typical real-life ETL cycle consists of the following execution steps:
1. Cycle initiation
2. Build reference data
3. Extract (from sources)
4. Validate
5. Transform (clean, apply business rules, check for data integrity, create aggregates or disaggregates)
6. Stage (load into staging tables, if used)
7. Audit reports (for example, on compliance with business rules. Also, in case of failure, helps to diagnose/repair)
8. Publish (to target tables)
9. Archive
10. Clean up
Typical ETL Architecture Pattern
List of ETL Tools:
ETL tools present in market range from open source to paid. These could be categorized based on functions, engine type, Development mode and open source or paid.
No. List of ETL Tools ETL Vendors
1 Abinitio Abinitio
2 IBM Information Server (Datastage) IBM
3 PowerCenter Informatica Informatica
4 Oracle Warehouse Builder (OWB) Oracle
5 Data Services SAP Business Objects
6 SAS Data Integration Studio SAS Institute
7 Elixir Repertoire Elixir
8 Data Migrator Information Builders
9 SQL Server Integration Services Microsoft
10 Talend Studio for Data Integration Talend
11 DataFlow Manager Pitney Bowes Business Insight
12 Pervasive Data Integrator Pervasive Software
13 Open Text Integration Center Open Text
14 Oracle Data Integrator (ODI) Oracle
15 Data Manager/Decision Stream IBM (Cognos)
16 Clover ETL Javlin
17 Centerprise Astera
18 DB2 Infosphere Warehouse Edition IBM
19 Pentaho Data Integration Pentaho
20 Adeptia Integration Suite Adeptia
21 DMExpress Syncsort
22 Expressor Data Integration QlikTech