Performance tuning in Informatica

The aim of performance tuning is optimize session performance so sessions run during the available load window for the Informatica Server. Increase the session performance by following.  The performance of the Informatica Server is related to network connections. Data generally moves across a network at less than 1 MB per second, whereas a local disk moves data five to twenty times faster. Thus network connections often affect on session performance. So avoid   work connections.

1. Cache lookups if source table is under 500,000 rows and DON’T cache for tables over 500,000 rows. 
2. Reduce the number of transformations. Don’t use an Expression Transformation to collect fields. Don’t use an Update Transformation if only inserting. Insert mode is the default.
3. If a value is used in multiple ports, calculate the value once (in a variable) and reuse the result instead of recalculating it for multiple ports.
4. Reuse objects where possible.
5. Delete unused ports particularly in the Source Qualifier and Lookups.
6. Use Operators in expressions over the use of functions.
7. Avoid using Stored Procedures, and call them only once during the mapping if possible.
8. Remember to turn off Verbose logging after you have finished debugging.
9. Use default values where possible instead of using IIF (ISNULL(X),,) in Expression port.
10. When overriding the Lookup SQL, always ensure to put a valid Order By statement in the SQL. This will cause the database to perform the order rather than Informatica Server while building the Cache.
11. Improve session performance by using sorted data with the Joiner transformation. When the Joiner transformation is configured to use sorted data, the Informatica Server improves performance by minimizing disk input and output.
12. Improve session performance by using sorted input with the Aggregator Transformation since it reduces the amount of data cached during the session. 
13. Improve session performance by using limited number of connected input/output or output ports to reduce the amount of data the Aggregator transformation stores in the data cache.
14.  Use a Filter transformation prior to Aggregator transformation to reduce unnecessary aggregation.
15.  Performing a join in a database is faster than performing join in the session.  Also use the Source Qualifier to perform the join.
16. Define the source with less number of rows and master source in Joiner Transformations, since this reduces the search time and also the cache.
17. When using multiple conditions in a lookup conditions, specify the conditions with the equality operator first.
18. Improve session performance by caching small lookup tables.
19. If the lookup table is on the same database as the source table, instead of using a Lookup transformation, join the tables in the Source Qualifier Transformation itself if possible.
20. If the lookup table does not change between sessions, configure the Lookup transformation to use a persistent lookup cache. The Informatica Server saves and reuses cache files from session to session, eliminating the time required to read the lookup table.
21. Use :LKP reference  qualifier in expressions only when calling unconnected Lookup Transformations.
22. Informatica Server generates an ORDER BY statement for a cached lookup that contains all lookup ports. By providing an override ORDER BY clause with fewer columns, session performance can be improved.
23. Eliminate unnecessary data type conversions from mappings.

Reduce the number of rows being cached by using the Lookup SQL Override option to add a WHERE clause to the default SQL statement.

Naming Convention Quick Reference

Object Type	Syntax
Folder	XXX_
Mapping	m_fXY_ZZZ__x.x
Session	s_fXY_ZZZ__x.x
Batch	b_
Source Definition
Target Definition
Aggregator	AGG_
Expression	EXP_
Filter	FLT_
Joiner	JNR_
Lookup	LKP_
Normalizer	Norm_
Rank	RNK_
Router	RTR_
Sequence Generator	SEQ_
Source Qualifier	SQ_
Stored Procedure	STP__
Update Strategy	UPD__xxx
Mapplet	MPP_
Input Transformation	INP_
Output Tranformation	OUT_
Database Connections	XXX__

Naming Conventions and usage of Transformations

Port Standards:

Input Ports – It will be necessary to change the name of input ports for lookups, expression and filters where ports might have the same name.  If ports do have the same name then will be defaulted to having a number after the name.  Change this default to a prefix of “in_”.  This will allow you to keep track of input ports through out your mappings.
Prefixed with: IN_

Variable Ports – Variable ports that are created within an expression

Transformation should be prefixed with a “v_”.  This will allow the developer to distinguish between input/output and variable ports.  For more explanation of Variable Ports see the section “VARIABLES”.
Prefixed with: V_

Output Ports – If organic data is created with a transformation that will be mapped to the target, make sure that it has the same name as the target port that it will be mapped to.

Prefixed with: O_

Data Loading from Flat Files

It’s an accepted best practice to always load a flat file into a staging table before any transformations are done on the data in the flat file.

Always use LTRIM, RTRIM functions on string columns before loading data into a stage table.

You can also use UPPER function on string columns but before using it you need to ensure that the data is not case sensitive (e.g. ABC is different from Abc)

If you are loading data from a delimited file then make sure the delimiter is not a character which could appear in the data itself. Avoid using comma-separated files. Tilde (~) is a good delimiter to use.

Failure Notification

Once in production your sessions and batches need to send out notification when then fail to the Support team.  You can do this by configuring email task in the session level.

Transformation Specifications

Before developing the mappings you need to prepare the specifications document for the mappings you need to develop. A good template is placed in the templates folder You can use your own template as long as it has as much detail or more than that which is in this template.

While estimating the time required to develop mappings the thumb rule is as follows.

1.   Simple Mapping – 1 Person Day

2.   Medium Complexity Mapping – 3 Person Days

3.   Complex Mapping – 5 Person Days.

Usually the mapping for the fact table is most complex and should be allotted as much time for development as possible.

General Development Guidelines

The starting point of the development is the logical model created by the Data Architect. This logical model forms the foundation for metadata, which will be continuously be maintained throughout the Data Warehouse Development Life Cycle (DWDLC). The logical model is formed from the requirements of the project. At the completion of the logical model technical documentation defining the sources, targets, requisite business rule transformations, mappings and filters. This documentation serves as the basis for the creation of the Extraction, Transformation and Loading tools to actually manipulate the data from the applications sources into the Data Warehouse/Data Mart.

To start development on any data mart you should have the following things set up by the Informatica Load Administrator

Ø  Informatica Folder. The development team in consultation with the BI Support Group can decide a three-letter code for the project, which would be used to create the informatica folder as well as Unix directory structure.

Ø  Informatica Userids for the developers

Ø  Unix directory structure for the data mart.

A schema XXXLOAD on DWDEV database.