Introduction
ETL stands for Extract, Transform and Load, the processes that enables the move of data from multiple sources, reformat and cleanse it, do whatever data conversions are necessary, and load it into another file, database, a data mart or a data warehouse for analysis, or onto another system, for instance a CMS environment like Drupal or Mambo/Joomla. 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, or we'd like to upgrade our system to a different software package. The problem often is that the data lies in formats that cannot be readily used by other applications. Solutions
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. Transformation
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. Most of them mandate an investment in software that needs to be installed on your computer. There are also 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. |
