Dynamic Lookup Transformation in Informatica

This Video explains the limitations of Static lookup and then explains how dynamic lookup transformation provides that solution with a practical Scenario.
The Initial PPT explains the generic concept followed by Implementation in powercenter
#Informaticapowercenter #dynamiclookup #TechCoach

[@samis3871]

I wish i had come across these videos much earlier. . A great and crisp explaination .. thanks a lot.. keep up the good work and keep creating such videos !!

[@TechCoach]

Thanks Sami for the kind words. I am working on this new channel and would really appreciate if you watch and subscribe to it. kzbin.info/www/bejne/f4GVh6Fsn7Brfsk

[@priyakshikashyap4837]

Such a beautiful explanation.. thanks much..😊

[@NaveenKumar-df9rq]

I used composite key .So, that it will treat each row as unique and insert those records and store them So,that we can identify them by querying the max values

[@sandeepnalvaya6327]

This is really well explained!! Kudos!

[@TechCoach]

Thanks Sandeep for the kind words I have a small request I am working on this new youtube channel, I Would love it if you watch and subscribe to it as well. kzbin.info/www/bejne/fZ-5n2ajhaeFsJY

[@arvinddeshmukh7099]

Nice one..clear explanation!!

[@mkr7345]

Awesome .... explanation . Great job

[@TechCoach]

Thanks a lot for the kind words :)

[@pragyakhare1762]

Thanks for the wonderful explanation!!

[@TechCoach]

@pragya thanks for the kind words. Please consider subscribing to my other channel as well. It will really help me. kzbin.info/www/bejne/bHiUqXqfYqlopdk

[@riteshpatil8956]

Very good explain thanks ,pls come with more tricky and complex scenario based on real time

[@pranithairuventi8721]

Very nice explanation which gives us the proper understanding .Thank u

[@TechCoach]

Thanks Pranitha for the kind words, It means a lot to me. I have a small request, I am working on this new channel and would really appreciate if you watch and subscribe to it. kzbin.info/www/bejne/f4GVh6Fsn7Brfsk

[@fahadmahmood7150]

Very Nice explanation ..Got complete understanding of dynamic and static look up cache..!!

[@TechCoach]

Thanks a lot for the kind words buddy :)

[@vishvajeet5205]

Thanks for this amazing video, explained to the point in a very simple way. If possible, Request you to make a video on : Why unconnected lookup does not support dynamic cache? Explanations are available in text but not helping. Thanks in advance.

[@rmaleshri]

have watched this video numerous times, whenever I am in doubt. very clear explanation. can you post some videos on Informatica Power Exchange? thanks

[@TechCoach]

Thanks Ravi for the kind words, Sure I will work on them. I have a small request, I am working on this new channel please do watch and subscribe to it. Your support is my motivation. kzbin.info/www/bejne/hauXhZWHrNelhbs

[@KalyanS]

awesome well explained

[@manishaverma1110]

perfect explaination thanks...

[@TechCoach]

Thanks a lot for the kind words Manisha, I have a small request I am working a new youtube channel. I will really appreciate if you watch and subscribe to it. kzbin.info/www/bejne/lXKVioqip9p4odU

[@JyotsnaAkundi]

Thanks for such a clear explanation. This concept never felt so clearly understood before I found your video. I came across a question in one of the interviews but never found answer to it, the question goes like- Can you make Informatica to retrieve a particular record in case of multiple match policy in lookup(static or dynamic)?

[@TechCoach]

Yes lookup has an option to find the 1st matching record or last matching record. Or you can use order by clause as well to select a specific value

[@JyotsnaAkundi]

@@TechCoach yes I am aware of 1st match or last match but was not sure how to do it for a specific value. But yes, order by clause makes sense in this case. Thanks you so much for the response.

[@sanjayguptaa04]

Wonderful Explaination... Thanks

[@TechCoach]

Thanks Sanjay for the kind words. Please consider subscribing to my other channel as well it will really help me. kzbin.info/www/bejne/bHiUqXqfYqlopdk

[@jayashreerath7363]

Very nice explanation. One correction 0 is not for update: New look up port values:- 0 The PowerCenter Server does not update or insert the row in the cache. 1 The PowerCenter Server inserts the row into the cache. 2 The PowerCenter Server updates the row in the cache.

[@TechCoach]

Thanks For correcting Jayashree If I have misstated anything, I will look into it. I have a small request, I am working on this new channel and will really appreciate if you watch and subscribe to it. kzbin.info/www/bejne/Z2KnfGClrrekopI

[@siddharthkhade4557]

@@TechCoach how did it show rowtype =update for newlookup row value=0 ?

[@ganjiharshu]

@@siddharthkhade4557 He mentioned a condition in the Update strategy: IIF(NewLookupRow=1, DD_INSERT, DD_UPDATE). Hence apart from NewLookupRow=1, the data will be marked for Update.

[@PrasoonJain]

Thanks a lot Vivek sir for sharing your valuable knowledge. :)

[@TechCoach]

+Prasoon Jain Thanks a lot buddy for your kind words, Hopefully these videos are helpful in your project work

[@shrushtiamulya5553]

Thanks very neat and clear explanation

[@TechCoach]

Thanks Buddy , I am glad I could help :)

[@NEENA0499]

One question.. When you say cache is is it the snapshot of target ?

[@rmaleshri]

good explanation. thank you Vivek.

[@TechCoach]

Thanks a lot for the kind words, I have a small request I am working on this new youtube channel " An Indian Abroad " and will really appreciate if you watch and subscribe to it. kzbin.info/www/bejne/Z2KnfGClrrekopI

[@RamKrishnaBlessings]

Good one

[@NaveenKumar-df9rq]

My question is ,I have to update when it get inserted in transformation fro source to target

[@akshaymalviya2676]

Thanks for such a clear explanation. Can you explain SCD type 2 using dynamic cache with same record you use in this video. Whatever you explain is clearly understandable.

[@TechCoach]

Sure Akshay , I will work on it soon.

[@NaveenKumar-df9rq]

My question is about cache, that load the first value then it will treat the same second value as new .if that two value inserted as one after the another then how it load . because cache will be inserting the records as bulk like 1k records at once in that 1k records this kind of values are getting inserted then it will send Error like constraint violated ,if the record inserted first value as 900 and second value as 700 ,now what will be loaded,if so what is the remid

[@NEENA0499]

If dynamic cache is enabled does lookup becomes case sensitive ?

[@dipakit45]

Can you please create video on increment data load when we use source as Flat file.

[@madhuriwagh6919]

Very nice..explained..plz mame more videoes in lkp

[@TechCoach]

Sure Madhuri I will work on them.

[@ankushgondane8642]

Thank you Vivek

[@TechCoach]

Ankush Gondane You are welcome buddy :)

[@soumyapadhee]

Excellent. Great to see the concept in action.

[@TechCoach]

i am glad you liked the video :)

[@vijaybathula592]

Thank you, Vivek!!

[@TechCoach]

Thanks for the kind words, I have a small request I am working on this new youtube channel "An Indian Abroad" ,it will really help me if you watch and subscribe to it. kzbin.info/www/bejne/fnO0eZihgpaIaMU

[@falakdawar]

Awesome explanation, first time understood the concept. Just one question regarding the video: Why did not we send any column from the lookup to subsequent transformation apart from (NewLookupRow)? Isn't fetching a corresponding value of a column (used in lookup condition) from the lookup table / file the basic function of a lookup transformation?

[@TechCoach]

Thanks falak for the kind words :) The basic purpose of look is simple to verify if a record exists or not. 1 column is sufficient to verify that :) I have a small request. Please consider subscribing to my other channel as well. It will really help me. kzbin.info/www/bejne/bHiUqXqfYqlopdk

[@falakdawar]

@@TechCoach Thanks for explaining, also please note that I have already subscribed to your other channel. :)

[@madanbihari4067]

Create Video for Java , SQL and Store Procedure Transformation..! Thanks

[@ziongladys1120]

In associated port what port u have given

[@azzadshaik7614]

Nice

[@TechCoach]

Thanks buddy :)

[@chinna1131]

if there is no change in record what happens not explained? ... insert and update is ok... if there is no change in record informatica should not change update details with same values... will it work

[@JyotsnaAkundi]

Hi Vivek, I got few questions here- 1. Even when the record is existing in the target , the value of the NewLookupRow is 0. In what situation will the NewlookupRow value be 2 ?(In Infa help it is mentioned that 0 means IS does not update or insert the row in the cache. and 2 means IS updates the row in the cache. 2. When I run this workflow with source file having records like- Completely new records, Existing record with no change in data and existing record with some change in data, the affected rows is also including the existing record with no change in data. How do I avoid no change records from being updated. I am not able to take router to distinguish between update records based on NewLookupRow property because it is 0 for any existing record. Thanks in advance

[@TechCoach]

Compare the records using MD5 and filter the unchanged records using filter transformation :)

[@venkateshkulkarni1087]

What are associated ports here

[@shaksrini]

Newlookup row =2 is for UPDATE, And zero does not insert/update

[@TechCoach]

That's correct, My apologies If I mis spoke in the video

[@TechCoach]

please consider subscribing to my new channel it will really help me kzbin.info/www/bejne/bHiUqXqfYqlopdk

[@saurabh1812]

how to create that file which u used

[@nehatasleem3535]

How to do unconnected lookup from a target table

[@chandanadandeboina7804]

I have watched your videos.. very interesting. Can you please explain how to pass table name through parameter file. Ex. Using multiple tables which has same no of columns and data types and without using unions. Thanks in advance 😊

[@TechCoach]

Its actually very easy, Define the variable in param file, with the same name create a variable in mapping you can now use the variable in any of the transformation you just have to specify the path of the parameter file in workflow definition.

[@chandanadandeboina7804]

Thank you very much for quick reply

[@FactsKannadaShivu]

Share More real time senarios. What you getting New senarios. Please make videos.

[@lovelylife584]

Add some workflow failed video how the session will kick off if file come or not at particular time

[@TechCoach]

Ideally it should be handled using UNIX or scheduler .But sure I will work on it

[@salmak8660]

How to created flite files ..sir pls tell me. .

[@yesu1001]

Good One Vivek!!!

[@TechCoach]

gogo le Thanks a lot :)

[@rameshmasal316]

The explanation was best. but I don't understand why you have used expression transformation cant we just input the lookup from expression transformation?

[@fahadmahmood7150]

One question i have in case of static cache if we are getting redundant Primary key and i have updated data from source can’t we use update strategy to update the same data in target..!

[@TechCoach]

If I understand your question correctly it will not work even though the record is inserted into db because informatica will not look into DB to verify if the records exists rather it will look into the cache it built earlier where the record was not present when the ran started

[@fahadmahmood7150]

@@TechCoach Thanks for the response i got my answer.👍👍👍

[@Suman-fo1zv]

what is the need of exp trans...please explain sir ..thank u

[@TechCoach]

You can compute values using expression transformation, Add Static/dynamic values and much more.For example we have a source file that sends employee salary yearly but we want to store monthly salary in table we can compute that in expression transformation.We want to assign a static/dynamic value to a field we can create an output port and assign it a value for example sysdate for created_date column.I hope this clarifies your doubt. I have a small request please help me in building my new channel "An Indian Abroad" by watching subscribing and sharing with your friends. kzbin.info

[@nazeers799]

in static cache first update the target table and then cache will be updated where as in dynamic cache first update the cache and then target table will be updated . Am I correct?

[@TechCoach]

Static cache doesn't update during the session run.

[@bhaskarpg64]

Hi Vivek,will you please do some videos on unit testing??

[@TechCoach]

Sure buddy, I will work on them soon.

[@bhaskarpg64]

@@TechCoach thanks vivek,your way of teaching is simply superb

[@rajathkudtarkar1574]

why was the expression transformation used here?

[@TechCoach]

Good question Rajath, I usually add an expression transformation in my mapping for any static values that I may want to have at a later stage.This gives me flexibility while scaling and maintaining the mapping. I have seen quite a few times post the 1st development business asks me to add few static columns or similar things which I can easily Initiate using expression transformation without deleting all the link between transformations and then adding them again. In this case the expression transformation is not playing any active role and ideally from performance perspective you should add minimum transformations but in my opinion if you don't have an expression transformation in your mapping add one, You can initialise audit variables like sysdate,company name and stuff like that using expression transformation easily when asked in future. Please let me know if this clarified your doubt. I have a small request I am working on this new youtube channel "An Indian Abroad " It will really help me if you watch and subscribe to it. kzbin.info/www/bejne/fnO0eZihgpaIaMU your support is my motivation.

[@TechCoach]

I have a small request Rajath, I am working on this new channel and would really appreciate if you watch and subscribe to it. kzbin.info/www/bejne/f4GVh6Fsn7Brfsk

[@amitrajora784]

great work and good explanation ! But small doubt ! Will it read sequentially, lets say Rio has latest salary 900 but in source file it is coming as Rio 900 Rio 600 What happen in that case it should first update 900 then 600 right. So the sequence of providing the updated value in source is matters ?

[@TechCoach]

Yups if I understand ur question correctly it should work fine

[@sharathhemmadi6293]

Does the static caching, look up into the source table/file when the I'd is not available in the cache file?

[@TechCoach]

@sharath, No it will only look into the cache file. Please consider subscribing to my other channel as well. It will really help me. kzbin.info/www/bejne/bHiUqXqfYqlopdk

[@sharathhemmadi6293]

@@TechCoach Thanks! I just did!

[@sharathhemmadi6293]

And I also liked the clear and simple way that you have detailed.

[@TechCoach]

@@sharathhemmadi6293 thanks a lot buddy for the kind words.your support is my motivation.

[@bhaskarsharma3338]

why you have used EXP transform?

[@TechCoach]

bhaskar sharma I usually use expression transformation as a fail safe so that if I have to make a change in future I am not changing my mapping too much. I hope that clarifies.

[@mkr7345]

Nice

[@TechCoach]

Thanks M K R for the kind words :)