BI Architecture and the MicroStrategy Platform » Business intelligence architecture » Extraction, transformation, and loading process

Extraction, transformation, and loading process

The extraction, transformation, and loading (ETL) process represents all the steps necessary to move data from different source systems to an integrated data warehouse.

The ETL process involves the following steps:

1 Data is gathered from various source systems.
2 The data is transformed and prepared to be loaded into the data warehouse. Transformation procedures can include converting data types and names, eliminating unwanted data, correcting typographical errors, filling in incomplete data, and similar processes to standardize the format and structure of data.
3 The data is loaded into the data warehouse.

This process can be explained with the example of a bank that wants to consolidate a variety of information about a particular customer, including the customer’s ATM activity, loan status, and account balances. Each of these different sets of data is likely gathered by different source systems. Since each source system can have its own naming conventions, the data that comes from one system may be inconsistent with the data that comes from another system.

In this case, the ETL process extracts the data from the different banking source systems, transforms it until it is standardized and consistent, and then loads the data into the data warehouse.