ETL - Introduction

ETL-process

ETL (Extract, Transform and Load) is a process in data warehousing responsible for pulling data out of the source systems and placing it into a data warehouse.

The data is loaded in the DW system in the form of dimension and fact tables.

ETL involves the following tasks:

Extraction

Extracting the data from source systems (SAP, ERP, other operational systems) and then data is converted into one consolidated data warehouse format which is ready for transformation processing.

Data is extracted from the source system and kept it in the staging area.

Staging area is required when you want to get the data from multiple data sources together or if you want to join two or more systems together. For example, you will not be able to perform a SQL query joining two tables from two physically different databases.

Transform

In data transformation, you apply a set of functions on extracted data to load it into the target system. Data, which does not require any transformation is known as direct move or pass through data.

Transforming the data may involve the following tasks:

• Applying business rules (so-called derivations, e.g., calculating new measures and dimensions)
• Cleaning leaning (e.g., mapping NULL to 0 or "Male" to "M" and "Female" to "F" etc.).
• Filtering (e.g., selecting only certain columns to load).
• Splitting a column into multiple columns and vice versa.
• Joining together data from multiple sources (e.g., lookup, merge).
• Transposing rows and columns.
• Applying any kind of simple or complex data validation (e.g., if the first 3 columns in a row are empty then reject the row from processing).

Load

During Load phase, data is loaded into the end-target system and it can be a flat file or a Data Warehouse system.

See also Dimension and Fact Tables