Informatica expression transformation is most used transformation of informatica power center. From my perspective it is an important transformation…… Moreover it is most sought after informatica transformation to move your brain muscles if someone is having a technical discussion with you !!!!! Take my word; Expression is a heavily used transformation in ETL code development. as well Very likable for those who like doing algorithmic implementations in powercenter ;-).
Basic definition for expression
An attempt to cover advanced aspect of expression transformation, so will be helpful only for those who have basic understanding and experience in ETL development.
Please read few questions given at last to know what you will be able to follow after reading this post. Estimated time to read – 5 mins.
BASIC DEFINITION OF INFORMATICA EXPRESSION -Expression is a power center object which performs single row (non-aggregate) calculations. i.e. – all the rows will pass through expression port and in case expression is not returning anything it will return whatever default value assigned to port.
Advanced Informatica Expression Examples
# Using mapping and session variable in expression transformation. Please see mapping variable in informatica.
# Interpreting expression- Informatica Integration service interprets the expression directly i.e it reads the relational source definition and writes to the target.
# If there is a expression port as LAST_NAME and it’s value is equal to the string ‘Texas’. What should be the return value of the expression LTRIM ( LAST_NAME, ‘E’ )?. Here return value is – ‘Texas’.
# How can we create custom sequence key in Expression?
1. Use sequence generator to generate a base key Eg: 12345
2. Use date value to generate custom key in expression. Eg: 20140512
3. Concatenate both in an expression to generate a unique key in every execution. Final value of sequence will be 2014051212345
# For more examples please refer Few informatica scenarios for interview
Basics of informatica expression transformation
## A very common discussion around “informatica keywords”. More of towards port name in Expression transformation. Can we give a port name as DISTINCT ? Answer: Well we can not use “informatica reserved words” as a port name.
## Sometime discussion happens around expression character limitations as well. Very rare though, is there any limitations in terms of maximum number of characters allowed in expression? Yes limitation is there. The maximum number of characters that one can include in an expression is 32,767.
## How to represent comments in informatica expressions? if one want to add comments within the expression, He can use — or // as comment indicators. Comments can also be added through the dialog box also.
## This is something new, is it possible to display different colors for different syntax in expression for higher readability ? Yes it is possible. The Expression can display syntax in different colors for better readability. For this to work you need to install the riched20.dll on your system. You might need to contact INFA admin for this.
## How can you define expression string in parameter file? See Parameter file or below line.
## To define an expression string in a parameter file, $$ZzzTTTAAA – $$Zzz=IIF(color=‘blue’, 8FD8D8)
## Expression is used to call unconnected lookup, stored procedure transformation and user defined functions.
Basic checks for expression !!!
(1) Please explain basic definition of informatica expression transformation ? (2) What are the different scenarios to be achieved through informatica expression transformation? (3) How to do syntax highlighting in informatica expression transformation ? (4) How to place code comments in informatica expression transformation ? (5) What are the character limitations in informatica expression transformation ? (6) Can we use informatica reserved words in informatica expression transformation ?
Please let us know if you need something to be added here………………. 🙂