ETL explained

ETL stands for Extract, Transform and Load, the processes that enable the move of data from multiple sources, reformat and cleanse it, and load it into another file, database, a data mart or a data warehouse for analysis, or onto another system.

We all know that there are valuable data lying around throughout our systems that would be very useful if it could be reused in another program.

The only problem is that the data lies in all sorts of formats that cannot be readily used other applications.

To solve the problem, you can use extract, transform and load (ETL) software, which includes reading data from its source, cleaning it up and formatting it uniformly, and then writing it to a target format to be exploited.

The data used in ETL processes can come from any source: a flat file, a mainframe application, an ERP application, a CRM tool, an Excel spreadsheet, an extraction program, anything really.

Extracting the data

Extraction can be done via a variety of methods. Often, the environment or program in which the data is currently held will have an export function that can be used to get the data into a format that can be easily transformed and processed. There are also specialized tools available to take data from a database environment.

After extraction, the data is transformed, or modified, depending on the specific business logic involved so that it can be sent to the target data store.

There are a variety of ways to perform the transformation, and the work involved varies. The data may require reformatting only, but most ETL operations also involve cleansing the data to remove duplicates and enforce consistency.

In addition, the ETL process could involve transforming from a fixed-record format to a variable one, or vice versa, standardizing name and address fields, verifying telephone numbers or expanding records with additional fields containing demographic information or data from other systems.

The transformation occurs when the data from each source is mapped, cleansed and reconciled so it all can be tied together.

After reconciliation, the data is transported and loaded into the data warehouse for analysis.

Online data transformation

There are many tools available that help in the ETL process. All of them however mandate an investment in software that needs to be installed on your computer systems. The online functions available here can be very useful if you cannot, or don’t want to install any software on your computer. You will still need to extract the information from your existing environment into a file, but the transformation process can in many instances be done online.

Data Conversion Home             Help            Contact us         Link to us         Rate This Page

Copyright ©2005 iConv.com. All rights reserved.