Implementing Extraction Mapping in Informatica (Mapping Designer):
- Open the mapping designer and select the Create Mapping.
- Give in the mapping name as m_Stockexchange as per the naming convention.
- From the navigator drag the source NSE.
- This source will be displayed along with the Source qualifier, which is read only.
- This source qualifier can be created automatically i.e. whenever source is been dragged into the mapping designer, source qualifier would be created automatically or else source qualifier can be created manually.
- Similarly target can be dragged into the mapping designer.
- In the source qualifier the SQL query can be included in order to filter out the records from the sources.
Flow of Data:
Source (NSE and BSE) → PRS stage (NSE_TBL, and BSE_TBL) → Look up (SOURCE_SYSTEM_LKP, MONTH_LKP, COMPANY_LKP, DATE_LKP) → DWH (STOCK_TRADING_FCT)
The above flow of data can be done in two mappings or two sessions.
1- In the first mapping, data is been extracted from NSE and BSE to the tables NSE_TBL, and BSE_TBL respectively in the SOURCE_SILO_DB.
2- The other mapping is to load the data from the SOURCE_SILO_DB to the target TARGET_DW_DB.
EXTRACT mapping for NSE table to NSE_TBL:
- Open the mapping designer and select the Create Mapping.
- Give in the mapping name as m_Stockexchange_NSE as per the naming convention.
- From the navigator drag the source NSE.
- These two sources would be displayed along with the Source qualifier, which is read only.
- This source qualifier can created automatically i.e. whenever source is been dragged into the mapping designer source qualifier would be created automatically or else source qualifier can be created manually.
- Similarly target NSE_TBL can be dragged into the mapping designer.
- In the source qualifier the SQL query can be included in order to filter out the records from the sources.
- Double click on the source qualifier transformation, in the pop window there are tabs Transformations, Ports, Properties, Sources and Metadata Extensions.
- Transformation consists of Select transformation it will list all the transformations that are used in the particular mapping. Here in this it will list only this source qualifier, as we have not used any other transformations. Then the name of the current transformation, which is nothing but the Source Qualifier. Final column is the Description, which enables us to mention the purpose of this transformation. E.g. To get the records from the NSE to NSE_TBL.
Source Qualifier Transformation:
- Ports tab consists of all the ports, their data type, precision, scale, is it an input port and is it an output port that are there in the source table NSE. As we have imported the table all the columns and their respective details would be present in the ports Tab. If we need to include any other column of our choice we can add the column through the last tab ‘Metadata extensions’. E.g. Column name: Sysdate, Data type: Date /time, Precision: 20, Scale:0, Input port check box: checked, Output port check box: checked. How ever for doing this we need use an expression transformation.
- In the properties tab we will be having the following,
- Sql Query – SQL statement defined by the user
- User Defined Join – used to join two different source using the where clause
- Source Filter – Source records filtration
- Number Of Sorted Ports – Number of input ports used for sorting
- Tracing Level – Amount of detail in the session log. Options – Normal, Verbose Initialization and Verbose Data
- Select Distinct – Select Distinct values only
- Pre SQL – SQL statements executed using the source database connection, before a pipeline is run
- Post SQL – SQL statements executed using the source database connection, after a pipeline is run
- Output is deterministic – The source data does not change during the session run
- Output is repeatable – The source data is sorted
Note: Informatica and its related products are Trademarks of Informatica Corporation. For more details, please visit their official website www.Informatica.com.
