ETL process using Informatica:
So far we have explained about the source tables and how data need to be transformed before loading into target tables. In this section, we will see how this is implemented using Informatica.
Quick Overview:
Sources: National Stock Exchange (NSE) and Bombay Stock Exchange (BSE)
PRS: NSE_TBL, BSE_TBL
Target: STOCK_TRADING_FCT
BSE data: Oracle Format
NSE data: Comma Separated .CSV File
Assumptions: Access to the Oracle data base and Informatory repository available; Informatica repository configured.
Flow: Sources → SILO Sources → PRS → DWH Sources (from other DB) → SILO Sources is EXTRACT SILO Sources → PRS is STAGING / TRANSFORM PRS → DWH is LOAD
Defining Source Table and Target Table in Informatica: SOURCE ANALYZER
- Step 1: Click on Start → programs → Informatica power center → Power center designer.
- Step 2: In the navigator right click and connect to the respective repository where a particular ETL need to be created.
- Step 3: Among the tools (Source Analyzer, Warehouse designer, Transformation designer, Mapplet designer and Mapping designer) of the designer, the source analyzer is to import the sources. As one of the source BSE is an oracle table,
It has to be imported from the source DB. - Step 4: To import the source to the source analyzer click on the SOURCES in the tools and then the IMPORT FROM DATABASE.
- Step 5: Select the ODBC data source to connect to the respective data base.
- Step 6: If a new ODBC data source need to be created or modified click on browse to open up the ODBC administrator. Create the appropriate data source and click OK. Then select the new ODBC data source (as in step 5).
- Step 7: Enter the username, owner name and the password of the data base.
- Step 8: Click CONNECT, connection established and if no tables are listed then click on ALL and select the required table. In this case it would be BSE.
- Step 9: Click OK.
- Step 10: On doing this in the navigator of the power center designer, if the sources are expanded the BSE table would be listed along with the other imported sources.
- Step 11: To save the changes, click on REPOSITORY in the tools and click SAVE.
- Step 12: In the source analyzer there will be BSE source, double click on the title bar of the source BSE. EDIT TABLES window will open up, in which there are TABLES, COLUMNS and METADATA EXTENSIONS.
- Step 13: Table name is BSE, Owner name is the owner of the database and Description is Bombay Stock Exchange (BSE) is one of the major stock exchanges in India in which the shares of ABC Corporation and XYZ Private Limited are traded between Mondays through Friday except Holidays.
- Step 14: Click on the COLUMNS if any changes to be done in the columns, data types could be changed if needed, once done with the changes click OK to save the changes.
Note: Informatica and its related products are Trademarks of Informatica Corporation. For more details, please visit their official website www.Informatica.com
