Expression transformation
Purpose
Expression is a passive transformation (number of input and output rows is the same), which lets modify individual ports of a single row, or add or suppress them.
It helps implement the complicated data transforms, applies business logic and performs checks and validations.
Data can be modified using logical and numeric operators or built-in functions. Sample transformations handled by the expression transformer:
data manipulation - concatentation, truncation, round (CONCAT, LTRIM, UPPER, INITCAP)
datatype conversion (TO_DECIMAL, TO_CHAR, TO_DATE)
data cleansing - check nulls, replace chars, test for spaces, test for number (ISNULL, REPLACESTR)
manipulate dates - convert, add, test (GET_DATE_PART, IS_DATE, DIFF_DATES)
scientific calculations and numerical operations - exponential, power, log, modulus (LOG, POWER, SQRT)
ETL specific - if, lookup, decode (IIF, DECODE)
Examples / useful tips
If possible, use numeric operations which are faster than string.
Operators are faster than functions (i.e. || vs. CONCAT).
Use transformation variables to break down complex transformation logic into smaller parts. It is defined by checking a V check-box at the top (then I for Input and O for Output is greyed out).
It is highly recommended to define a naming convention for the input and output ports for expressions. For example, all input ports have an _in suffix, output ports _out and variables _var:
PowerCenter Designer client comes with a good help system with examples. Highlighting a function and pressing F1 launches it.
Ports are evaluated in the following order: input ports first, then variable ports in the display order (from top to bottom), then output ports.