Informatica Best practices:Category: General, Informatica Posted:Jun 16, 2015 By: Alvera Anto
What do you mean by lookup transformation? Lookup transformations should fetch data that will look against the source data. It is useful when used effectively. If not used effectively performance mapping is impaired. These are the problems with Lookup and we can resolve it in the following ways:
1.1. Unwanted columns
By deleting the unwanted columns we can increase the cache size.
1.2. Size of the source versus size of lookup
By using uncached lookup the source row can be minimized.
1.3. JOIN instead of Lookup
There are no active transformations between the source qualifiers. You can override SQL for source qualifier and join the lookup tables.
1.4. Conditional call of lookup
Why not use unconnected lookups instead of filters for connected lookups. For this we need to change the SQL override for concatenation for one column to a large column.
1.5. SQL query
Try to find about the execution plan for the Lookup SQL and add indexes to the query so that the data is fetched faster.
1.6. Increase cache
If problem doesn’t get solved try to solve at cache level for performance enhancements.
- Workflow performance
There are some few considerations to be made to improve performance:
- Think before using Update strategy as it can add data directly into the target.
- Use a pre-SQL delete statement to delete specific rows from the target before loading into the target.
- PowerCenter can be used to work on large volume of data. This shall induce parallelism for the workflow.
- While using a transformation sort the data for the join keys before using joiner.
- Filtering must be done at database level within the mapping.
- Email the success or failure status of the task once it is done.
- The built-in feature helps in flexibility for the Session logs.
For Informatica Training needs, Visit http://www.zarantech.com/course-list/informatica-training/. Call 515-309-1546 or email [email protected]