What is Informatica?
Informatica is a tool, supporting all the steps of Extraction, Transformation and Load process. Now a days Informatica is also being used as an Integration tool.
Informatica is an easy to use tool. It has got a simple visual interface like forms in visual basic. You just need to drag and drop different objects (known as transformations) and design process flow for Data extraction transformation and load. These process flow diagrams are known as Mappings. Once a mapping is made, it can be scheduled to run as and when required. In the background Informatica server takes care of fetching data from source, transforming it, & loading it to the target systems/databases.
Informatica can communicate with all major data sources (mainframe/RDBMS/Flat Files/XML/VSM/SAP etc), can move/transform data between them. It can move huge volumes of data in a very effective way, many a times better than even bespoke programs written for specific data movement only. It can throttle the transactions (do big updates in small chunks to avoid long locking and filling the transactional log). It can effectively join data from two distinct data sources (even a xml file can be joined with a relational table). In all, Informatica has got the ability to effectively integrate heterogeneous data sources & converting raw data into useful information.
The important Informatica Components are:
Though ,the mapping will function properly when deployed as a row-based package, the performance of the row-based implementation may be unacceptable when considering the volume of rows being processed and the size of the ETL window.
So, an alternative approach is available that will allow you to deploy the mapping using a set-based implementation. First, you will need to create a staging table that looks exactly like the target table; then, substitute the staging table for the target table in the mapping. The Type 2 SCD function will still do the lookup on the target table, but the inserts will be redirected to the staging table. A second mapping will then be required that inserts the new rows in the staging table into the target table.
For more Detail please visit:::
The important Informatica Components are:
- Power Exchange
- Power Center
- Power Center Connect
- Power Exchange
- Power Channel
- Metadata Exchange
- Power Analyzer
- Super Glue
This section will contain some useful tips and tricks for optimizing informatica performance. This includes some of the real time problems or errors and way to troubleshoot them, best prcatices etc.
Informatica Tips: Set based implementation
In the Type 2 SCD approach ,where you need to look for the existence of data in the target before inserting, if you use set-based, and run the package you will get an ORA-04091 error stating "Table ‘XXX’ is mutating, trigger/function may not see it." This error occurs because you are inserting into, and the function is selecting from, the target table in the same set-based statement. (fig1)
Though ,the mapping will function properly when deployed as a row-based package, the performance of the row-based implementation may be unacceptable when considering the volume of rows being processed and the size of the ETL window.
So, an alternative approach is available that will allow you to deploy the mapping using a set-based implementation. First, you will need to create a staging table that looks exactly like the target table; then, substitute the staging table for the target table in the mapping. The Type 2 SCD function will still do the lookup on the target table, but the inserts will be redirected to the staging table. A second mapping will then be required that inserts the new rows in the staging table into the target table.
For more Detail please visit:::
