Page 1     2     3     4     5     6     7     8     9     10



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 only 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 and better clarity.

  • 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.

Source Data:

Assume that the data from NSE is in Oracle file format and the data from BSE is a Comma Separated .CSV File and these two files form the source data for our ETL operations throughout this tutorial. Following two Record Layouts show the source data from NSE and BSE respectively.

Record Layout 1: Raw Data From NSE
Date Stock_Symbol Previous_Day_Close Today_Open Today_High Today_Low Last_Traded_Price
23-Mar-07 ABCCORP 950 1000 1020 930 1025
24-Mar-07 ABCCORP 1025 1000 1000 900 1050
25-Mar-07 ABCCORP 1050 1075 1100 1050 1150
23-Mar-07 XYZPRIVATELIMITED 2950 3000 3020 2930 3025
24-Mar-07 XYZPRIVATELIMITED 3025 3000 3000 2900 3050
25-Mar-07 XYZPRIVATELIMITED 3050 3075 3100 3050 3150
23-Apr-07 ABCCORP 1000 1050 1070 980 1075
24-Apr-07 ABCCORP 1075 1050 1050 950 1100
25-Apr-07 ABCCORP 1100 1125 1150 1100 1200
23-Apr-07 XYZPRIVATELIMITED 3000 3050 3070 2980 3075
24-Apr-07 XYZPRIVATELIMITED 3075 3050 3050 2950 3100
25-Apr-07 XYZPRIVATELIMITED 3100 3125 3150 3100 3200

[ Continue to Next Page ... ]



Page 1     2     3     4     5     6     7     8     9     10