Interested to Buy Any Domain ? << Click Here >> for more details...
Answer Posted / abhinaw prakash
Valid Stored Procedure Use cases
Complex Calculations
If we have a complex calculation that needs to be done for
each record repeatedly we may use stored procedure
transformation. Complex statistical calculations where a
certain measure is being calculated for each of the record
is a good example where one may want to use this.
Performance
There are cases where you may want to use this to improve
your performance. Let’s say one calculation requires
accessing or looking up multiple records or values from
several different tables. If you were to perform this
calculation in Informatica, you will have to perform
multiple lookup operations in many tables. Depending upon
the types of your lookup, this may involve caching the
entire data or at least making multiple network level calls
to transport these data over the network layer. Instead, if
we use a stored procedure transformation, we can perform the
entire calculation within the database, thereby, eliminating
any need to transport data over the network.
Security
Although rare but sometimes client may have a proprietary
algorithm implemented in database. Client does not want to
let others know about the actual algorithm in use, however,
wishes that we take advantage of this algorithm. If the
algorithm is implemented in database stored procedure and
proper access restrictions are placed, then we will not be
able to read the SP to see the algorithm, but still can call
this from Informatica to take the advantage of the
algorithm. Example, proprietary trading algorithms in banks
and brokerage farms.
Reusability
Reusability is a reason why you may want to use a stored
procedure transformation. Suppose you have already written a
complex code in database programming language, tested the
code exhaustively and you are pleased with the performance
of this piece of code. Now that your Organization is
implementing Informatica, you may not wish to rewrite this
entire piece of code in Informatica again. You can reuse
your existing code through stored procedure transformation.
| Is This Answer Correct ? | 3 Yes | 0 No |
Post New Answer View All Answers
Is there any target staging area in informatica not staging area
How to differentiate between the active and passive transformations?
How to extract sap data using informatica? What is abap? What are idocs?
Partition, what happens if the specified key range is shorter and longer
What is a difference between complete, stop and abort?
4 yrs etl devloper roles and responsibilities?any body give solutions
How do you remove duplicate records in informatica? And how many ways are there to do it?
What is a connected transformation?
In what scenario we use to improve session performance by pushdown optimization?can any one give example?
1)you have multiple source system where u receive files ,how do you actually load into mapping using transformation,what are the transformation you use? 2)you have files in ftp location ,how do you get it into mapping with you ETL concept?
What is rank index in rank transformation?
hi friends .i designed mapping in windows but i want to run mapping in linux.should i install the server components in linux?
tell me the rules and responsblites in our project(my project is development)
what kind of issue you will get in environment? (we ll call help desk to raise ticket rite?
If i have source as flat file. how can i store the header and trilor into one target and data into one more target. |------>target1(header+trailor) source------ |------>target2(data) can any one please help me
