Informatica Construction Guidelines

Background

The purpose of this document is to define and promote uniformity in the style of programming in Informatica Power Center. The conventions and recommendations presented here are not final, and would continue to evolve. Suggestions for improvements are encouraged.

Intended Audience

This document is intended for Informatica Power Center developers and reviewers.

1. Coding Guidelines and Tips

v When constructing transformations, the programmer must enter the description for the transformation in the box provided on the first tab of the ‘Edit Transformations’ window.

v For coding logic in ports, the following general guidelines should be followed:

Ø Use RTRIM and/or LTRIM function for string comparisons in cases where the length of the string with which the variable is being compared is less than the variable length.

Ø Use IS_SPACES function for checking if fields are blank.

Ø Use IS_DATE function for checking valid dates.

Ø Ensure that IS_DATE function check is done before a date function(such as TO_DATE, DATE_DIFF, DATE_COMPARE etc.) is used. This will prevent skipping of records if the value passed to the date function is not a valid date.

Ø IS_NUMBER function accepts scientific notation for numbers. Hence if validations required that a value be numeric(and not in scientific notation), use exception handling. Eg: A numeric validity check for a value ‘product_price’ which is an unsigned decimal value(like 52.50) is carried out as shown below.

IIF

( NOT (IS_NUMBER(product_price)) OR

(INSTR(product_price, '+') OR

INSTR(product_price, '-') OR

INSTR(product_price, ' ') OR

INSTR(product_price, 'e') OR

INSTR(product_price, 'E')),

‘Not_Number’, ’Number’)

Ø Use IS_NUMBER before using TO_NUMBER as TO_NUMBER returns an incorrect result if the value passed to it is not numeric.

Ø Use ’’ to check for empty string variables.

Ø All reserved words must appear in uppercase.

Ø Choose ports used in the calculation of a value of a particular port from the ‘Ports’ tab of in the expression editor.

Ø Use two hyphens “--" for adding inline comments while coding edit logic in expression editor.

Ø Ensure that no comparison with ‘Null’ is present(A comparison with Null gives a result of Null). Use exception handling if required.

v All variable ports should be defined before the ports in which they are used. An exceptions to this is when the value of the variable port for the previous record is required.

2. Bugs/Limitations of Power Center 5.1

v Fields(of type varchar) which have length greater than 2000 appear with a size of 2000. This will be rectified in version 6(Track against C.R. 43012).

v DB2 stored procedures are not supported in version 5.1. Version 5.1.2 supports DB2 stored procedures.

3. Performance Tips for Construction

v While using the Lookup Transformation:

Ø Place Conditions with an Equality Operator (=) First. If the Lookup transformation specifies several conditions, the programmer can improve lookup Performance by placing all the conditions that use the equality operator first in the list of Conditions that appear under the Condition tab.

Ø If the lookup table is small (it only has a few rows) enable caching as the session runs much faster if caching is enabled.

v Ensure that joins are done at database level where ever possible(avoid using Joiner Transformation if the join can be done in the source qualifier)

v Set tracing level to ‘Terse’ for in the ‘Properties’ Tab of a transformation to boost performance.

v Where ever possible use operators instead of functions. Informatica server reads expressions written with operators faster than expressions with functions. Eg: Replace CONCAT(Firstname,’MSL’) with Firstname || ‘MSL’

v Use filtering early in the data flow. Instead of using a Filter transformation halfway through the mapping to remove a sizable amount of data, use a source qualifier filter to remove those same rows at the source.

v If an unconnected lookup is used in several ports in an expression transform, call the unconnected lookup once in a variable port and use the variable port in place of the unconnected lookup. This improves performance as the lookup is called only once(in the variable port).

4. Object Naming Conventions

The following is the chart of conventions for naming objects:

Sr. No.	Informatica Objects		Naming Convention
1.	Mappings		M__ Where Sequence No
2.	Mapplets (If implemented )		MPL__
3.	Sessions		S_
4.	Transformations		ETL Transformations will be named according to the following convention __ where TXN: 3 Character Transformation Type Prefix as specified in the tables below. REC: 3 Character Record Type Prefix as specified in the Tables below. SQ: Unique Sequence No.
			Transformation Prefixes
			Filter		FLT_
			Expression		EXP_
			Router		RTR_
			Aggregator		AGG_
			Join		JNR_
			Source Qualifier		SQF_
			Lookup		LKP_
			Sequence Generator		SQG_
			Update Strategy		UPS_
			Rank		RNK_
			Normalizer		NRM_
5.	Transformation Ports(except unconnected lookup)	Variables		Error code variables are named according to the following convention v_
		Count Ports in aggregators		Count port names are named according to the following convention cnt_
		Flag Variables		Flag variables are named according to the following convention v_flg_
6.	Unconnected lookups	Input ports		Input Ports a must be given a meaningful name. Eg. A input port which takes fields begin date of care and end date of care as inputs may be named as ‘CARE_DATE’

5. Merging Mappings

In the case of complex informatica mappings where the amount of logic coded into ports is large, create a single skeleton map which has all the required input and input/output ports present. The skeleton mapping is copied into folders of each developer. Multiple developers can then code the logic into the variable and output ports. The logic from the developer’s maps is merged into the master skeleton map to arrive at the complete Informatica mapping. This is achieved by exporting the individual mappings in XML format and manual copy and paste into the Master/Skeleton map XML file.

The Master XML file (consisting of the various sub-components) will be imported into the Informatica repository to arrive at the final complete mapping.

v The Master XML file should be named as .XML.

v XML file names will be in uppercase.

v Sub-component XML files should be named as

_<>.XML

6. Version control for Informatica maps

Versions control can be implemented using any of the following methods:

v Informatica maps may be stored in VSS by taking an XML export of the map and checking it into VSS.

v Folder version control facility available in Informatica can be used to store versions of the maps.