Informatica Power Center: Transformations and Optimization Best Practices

#	Category	Things to keep in mind
1	General Recommendations	·         Reduce the number of transformations ·         Calculate once, use many times – For e.g. Use Variable Ports ·         Only connect what’s being used ·         Facilitate reuse – Use reusable transformations, mapplets ·         Filter records early-on and set driving tables appropriately in joins ·         Utilize single pass reads – Read once and load multiple targets ·         Minimize aggregation calls – Use sorted input wherever possible ·         Reduce custom coding, stored procedures, scripting ·         Add proper comments in the Mapping description ·         Use environment variables for the services name in Unix scripts             For e.g-> pmcmd startworkflow –sv $INT_SVC –dn $DOMAIN_NAME ….
2	Lookup Transformation	·         Reduce the dataset being cached - Override if required ·         Remove un-used ports from the Lookup Transformation ·         Place all the conditions with equality operator first ·         If the number of calls can be reduced consider replacing with unconnected uncached lookup ·         Consider using persistent cache for frequently accessed lookups ·         When handling multiple matches, use “Return any matching value” setting whenever possible – Improves lookup performance
3	Aggregator Transformation	·         Group by simple columns ·         Use sorted Input ·         Filter data before aggregation ·         Limit port connection ·         For high volume, increase the index and data cache size – avoid paging to disk
4	Operations and Transformation optimization	·         Numeric operations are faster than string operations ·         Operators are faster than functions (e.g. || vs CONCAT) ·         Use Router transformation instead of multiple filter transformation ·         Use Sorter transformation before an Aggregator transformation ·         Use a Normalizer to pivot rows rather than multiple instance of the target ·         While using Joiner transformation ensure that smallest source is set as the master