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 !!
Thanks Sami for the kind words. I am working on this new channel and would really appreciate if you watch and subscribe to it. th-cam.com/video/IP2Vn6jK8Hg/w-d-xo.html
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.
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. th-cam.com/video/11DK-oyRql0/w-d-xo.html
@@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.
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 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.
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.
Thanks Sanjay for the kind words. Please consider subscribing to my other channel as well it will really help me. th-cam.com/video/6G1xGi-D5ow/w-d-xo.html
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. th-cam.com/video/Oz4TbQwrrOY/w-d-xo.html
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. th-cam.com/video/IP2Vn6jK8Hg/w-d-xo.html
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.
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..!
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
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. th-cam.com/video/GnVn3mPBRz4/w-d-xo.html
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
@sharath, No it will only look into the cache file. Please consider subscribing to my other channel as well. It will really help me. th-cam.com/video/6G1xGi-D5ow/w-d-xo.html
@pragya thanks for the kind words. Please consider subscribing to my other channel as well. It will really help me. th-cam.com/video/6G1xGi-D5ow/w-d-xo.html
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
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
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
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?
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. th-cam.com/video/6G1xGi-D5ow/w-d-xo.html
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?
The explanation was best. but I don't understand why you have used expression transformation cant we just input the lookup from expression transformation?
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. th-cam.com/video/HBQHekM1U2c/w-d-xo.html your support is my motivation.
I have a small request Rajath, I am working on this new channel and would really appreciate if you watch and subscribe to it. th-cam.com/video/IP2Vn6jK8Hg/w-d-xo.html
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. th-cam.com/video/11DK-oyRql0/w-d-xo.html
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. th-cam.com/video/_A2YWlruEks/w-d-xo.html
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.
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 ?
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. th-cam.com/users/AnIndianAbroadd
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 😊
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.
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. th-cam.com/video/HBQHekM1U2c/w-d-xo.html
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 !!
Thanks Sami for the kind words. I am working on this new channel and would really appreciate if you watch and subscribe to it.
th-cam.com/video/IP2Vn6jK8Hg/w-d-xo.html
Very Nice explanation ..Got complete understanding of dynamic and static look up cache..!!
Thanks a lot for the kind words buddy :)
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.
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.
th-cam.com/video/11DK-oyRql0/w-d-xo.html
@@TechCoach how did it show rowtype =update for newlookup row value=0 ?
@@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.
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)?
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
@@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.
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.
Very good explain thanks ,pls come with more tricky and complex scenario based on real time
Wonderful Explaination... Thanks
Thanks Sanjay for the kind words.
Please consider subscribing to my other channel as well it will really help me.
th-cam.com/video/6G1xGi-D5ow/w-d-xo.html
Very nice..explained..plz mame more videoes in lkp
Sure Madhuri I will work on them.
have watched this video numerous times, whenever I am in doubt. very clear explanation. can you post some videos on Informatica Power Exchange? thanks
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.
th-cam.com/video/Oz4TbQwrrOY/w-d-xo.html
Very nice explanation which gives us the proper understanding .Thank u
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.
th-cam.com/video/IP2Vn6jK8Hg/w-d-xo.html
Thanks very neat and clear explanation
Thanks Buddy , I am glad I could help :)
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.
Sure Akshay , I will work on it soon.
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..!
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
@@TechCoach Thanks for the response i got my answer.👍👍👍
Newlookup row =2 is for UPDATE,
And zero does not insert/update
That's correct, My apologies If I mis spoke in the video
please consider subscribing to my new channel it will really help me
th-cam.com/video/6G1xGi-D5ow/w-d-xo.html
Nice one..clear explanation!!
Awesome .... explanation . Great job
Thanks a lot for the kind words :)
Can you please create video on increment data load when we use source as Flat file.
In associated port what port u have given
This is really well explained!! Kudos!
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.
th-cam.com/video/GnVn3mPBRz4/w-d-xo.html
Hi Vivek,will you please do some videos on unit testing??
Sure buddy, I will work on them soon.
@@TechCoach thanks vivek,your way of teaching is simply superb
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
Compare the records using MD5 and filter the unchanged records using filter transformation :)
One question.. When you say cache is is it the snapshot of target ?
Does the static caching, look up into the source table/file when the I'd is not available in the cache file?
@sharath, No it will only look into the cache file.
Please consider subscribing to my other channel as well. It will really help me.
th-cam.com/video/6G1xGi-D5ow/w-d-xo.html
@@TechCoach Thanks! I just did!
And I also liked the clear and simple way that you have detailed.
@@sharathhemmadi6293 thanks a lot buddy for the kind words.your support is my motivation.
Thanks for the wonderful explanation!!
@pragya thanks for the kind words.
Please consider subscribing to my other channel as well. It will really help me.
th-cam.com/video/6G1xGi-D5ow/w-d-xo.html
Such a beautiful explanation.. thanks much..😊
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
how to create that file which u used
What are associated ports here
Excellent. Great to see the concept in action.
i am glad you liked the video :)
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
Add some workflow failed video how the session will kick off if file come or not at particular time
Ideally it should be handled using UNIX or scheduler .But sure I will work on it
If dynamic cache is enabled does lookup becomes case sensitive ?
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
Good one
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?
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.
th-cam.com/video/6G1xGi-D5ow/w-d-xo.html
@@TechCoach Thanks for explaining, also please note that I have already subscribed to your other channel. :)
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?
Static cache doesn't update during the session run.
How to created flite files ..sir pls tell me. .
Thanks a lot Vivek sir for sharing your valuable knowledge. :)
+Prasoon Jain Thanks a lot buddy for your kind words, Hopefully these videos are helpful in your project work
awesome well explained
My question is ,I have to update when it get inserted in transformation fro source to target
The explanation was best. but I don't understand why you have used expression transformation cant we just input the lookup from expression transformation?
why was the expression transformation used here?
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.
th-cam.com/video/HBQHekM1U2c/w-d-xo.html
your support is my motivation.
I have a small request Rajath, I am working on this new channel and would really appreciate if you watch and subscribe to it.
th-cam.com/video/IP2Vn6jK8Hg/w-d-xo.html
good explanation. thank you Vivek.
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.
th-cam.com/video/11DK-oyRql0/w-d-xo.html
Share More real time senarios. What you getting New senarios. Please make videos.
How to do unconnected lookup from a target table
perfect explaination thanks...
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.
th-cam.com/video/_A2YWlruEks/w-d-xo.html
why you have used EXP transform?
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.
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 ?
Yups if I understand ur question correctly it should work fine
what is the need of exp trans...please explain sir ..thank u
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.
th-cam.com/users/AnIndianAbroadd
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 😊
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.
Thank you very much for quick reply
Create Video for Java , SQL and Store Procedure Transformation..! Thanks
Good One Vivek!!!
gogo le Thanks a lot :)
Thank you, Vivek!!
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.
th-cam.com/video/HBQHekM1U2c/w-d-xo.html
Thank you Vivek
Ankush Gondane You are welcome buddy :)
Nice
Thanks M K R for the kind words :)
Nice
Thanks buddy :)