Though informatica active passive transformation looks simple but sometime they get very challenging. if someone put it across intelligently. it is very broader and one of the first attribute of informatica transformation. Generally we come across of it at very beginning of our informatica tool training.
Before we get into those examples. let's see a generic definition given in informatica help.
Informatica Active transformation : Those informatica transformations which changes the number of rows passes through them are known as informatica active transformation. Example:
Informatica Passive transformation: Those informatica transformations which doesn’t change the number of rows pass through it are known as informatica passive transformation. Example:
In this major divide of transformations, there are few which are bit confusing. They take a toll on us initially. if put across in front of us Intricately and those are Expression, look-up and stored procedure. Please share if you have more.
If you understand above intricate transformation well. You can even float around below scenarios with ease.
Look at picture below to understand the scenario. You have two port as A and B with values given in picture. In between there is expression transformation with one filter condition given as PORT A> PORTB. So you are supposed to tell that "how many rows will be there in target after each row passing through filter condition ?
Answer – isn’t it 3 rows. “Most of the time people get confused and in hurry they feel great as it is sort of easy to determine number of rows got filtered. In this scenario rows to target will be (1, 2 and 3). But this is not correct solution. Matter of the fact is that expression transformation is a passive transformation and can’t change the number rows passing through it.
I have seen this being asked many times in industry. Though this example looks little weird once you understand it properly. it is more of trick played instead of putting it straight.
You have a source and lookup table columns as given in picture. In case of performing lookup how many rows will get filtered? In other word explain all the rows populating to target table?
So the confusion is being planted by the person itself who is asking this question. One can definitely slip if he is not aware of the trick being simply put up.
Explanation is very simple. In fact explanation may not be required for many of the people now. Lookup is passive transformation and can’t change number rows. Moreover Lookup transformations return null in case of no match. So there will not be any change in number of rows.
Stored Procedure transformation:
Unlike we think stored procedure transformation is passive transformation which doesn’t change number of inputs rows. Please comment below if you have ever come across any scenario for stored procedure transformation on same lines.