ETL Tool – Informatica
Informatica is a widely used ETL tool for extracting the source data and loading it into the target after applying the required transformation. In the following section, we will try to explain the usage of Informatica in the Data Warehouse environment with an example. Here we are not going into the details of data warehouse design and this tutorial simply provides the overview about how INFORMATICA can be used as an ETL tool.
Example – Stock Trading:
Note: The exchanges/companies that are explained here is for illustrative purpose only.
Bombay Stock Exchange (BSE) and National Stock Exchange (NSE) are two major stock exchanges in India in which the shares of ABC Corporation and XYZ Private Limited are traded between Mondays through Friday except Holidays. Assume that a software company “KLXY Limited” has taken the project to integrate the data between two exchanges BSE and NSE.
ETL Process – Roles & Responsibilities:
In order to complete this task of integrating the Raw data received from NSE & BSE, KLXY Limited allots responsibilities to Data Modelers, DBAs and ETL Developers. During this entire ETL process, many IT professionals may involve, but we are highlighting the roles of these three personals only for easy understanding.
- Data Modelers analyze the data from these two sources(Record Layout 1 & Record Layout 2), design Data Models, and then generate scripts to create necessary tables and the corresponding records.
- DBAs create the databases and tables based on the scripts generated by the data modelers.
- ETL developers map the extracted data from source systems and load it to target systems after applying the required transformations.
Overall Process:
The complete process of data transformation from external sources to our target data warehouse is explained using the following sections. Each section will be explained in detail.
- Data from the external sources (Source 1 – .CSV (comma separated) file, Source 2 – Oracle table)
- Source(s) table layout details.
- Look up table details.
- Target table layout details.
- Defining Source table and target table in Informatica.
- Implementing extraction mapping in Informatica (Mapping Designer).
- Implementing transformation and loading mapping in Informatica.
- Workflow creation in Informatica (Workflow Manager).
- Verifying records through Informatica (Workflow Monitor)
Note: Informatica and its related products are Trademarks of Informatica Corporation. For more details, please visit their official website www.Informatica.com.
