Tutorial corner

Informatica,ETL,oracle,sql/plsql

ETL process in data warehouse

Spread the love

ETL process is an important component of the Data Warehousing Architecture. ETL stands for Extract Transform and Load. We will discuss about the Steps of ETL process , where ETL is used and also check the ETL tool available in the Market.

ETL process

ETL process

 

What is ETL Process

As the name clearly suggest ETL is short form for Extract, Transform,Load the data from one or multiple Source to one or more Target system.This terminology is mainly used in data warehouse, data migration processes

Check here for Informatica powercenter

ETL Process Steps :

ETL Process mainly consist of 3 steps as described in picture also

  • Extract :

It is the process of reading data from the sources. Source can be Database (like Oracle , Mysql) , Files (csv, fixed length , excel) or other system like SAP Below are the several ways of extraction process:

  • Update notification –In such type of extraction source system provides a notification that a records has been changed and gives the description of the changes done. ETL process will automatically pick those records and process them.
  • Incremental extract – In such type of extraction m source system needs to identify changes and send those changed records for further processing.
  • Full extract – In such type of extraction , sources full data dump is provided. ETL processes the data on the basis of current file and old data already loaded in Target.
  • Transform : In this part, processing of extracted data take place. There can be some change in the data/ filtering of data etc. depending upon the business scenarios. This part can include :
    • Translating coded values : like M for ‘Male’ or ‘F’ for ‘Female’
    • Encrypting of data : like credit card no
    • Driving new calculated value :annual salery = salery * 12
    • Validation of data : with help of ref lookup table
  • Load : This part included the loading of processed data into the target system . Again target can be some database or some files . It control the flow of data into the various system/tables.

Where ETL Process is used

ETL are used mainly:

  • To create migrate data from one data base to another type of database
  • To create data marts and data warehouse
  • To transform the data of one system to another system type

Example of ETL Tools:

Some of  well known ETL tool available in market are:

  • Informatica
  • Oracle Data Integerator
  • SAP Data Integerator
  • AB Initio
  • IBM InfoSphere Data Stage

Source : www.wikipedia.org

So here we talked about ETL process in data warehouse , steps of ETL process along with various ETL tool available in market.

Updated: November 30, 2015 — 4:18 pm

The Author

admin

Hello Friends , I am Kajal Mishra .I love to write article on latest technologies like Informatica , ETL , data warehouse , SQL-PL SQL
Copyright 2015 - Tutorial Corner Frontier Theme