Introduction of Dynamic Lookup

What is Dynamic Lookup?

When lookup used Dynamic Cache then it's called as a Dynamic Lookup.

What is Dynamic Cache?

Dynamic Cache may get updated during mapping run. Means new records would be inserted and updated records would be updated in cache during mapping run.

How to create Dynamic Looup

Lookup Transformation-->Right Clickà Edità Properties Tab
Dynamic Lookup Cache Option (Check) (Check box should be check)

Properties of Dynamic Lookup

NewLookupRowà You will get this default port as soon as you checked option for dynamic lookup. In this port you will get three values
0 (For No Operation at Cache)
1 (If record inserted into Cache)
2 (If record Updated in Cache)

Associated Expressionà This option we need to set for each and every lookup port means if you have checked all port as lookup port then we need to set associated port for each and every lookup port. (Basically value in lookup port compared with value of Associated Expression to decide whether Lookup Cache has to insert or update, If so then Lookup Cache will be updated with value of Associated port.)

Lookup SQL Override: Yahoo!!! Here also, we can override SQL according to our need; If Lookup table is relational table.

For Updating Lookup Cache we need to set some more properties
You can choose either of option according to your need
  1. Insert Else Update

  2. Update Else Insert
(OMG!!! Isn’t both options are same)
Let me help you J

It’s totally depends upon row type, means which type of rows are entering to lookup, is it insert or update. Ex: I have use update Strategy before lookup and based upon some condition I am flagging record as DD_UPDATE then row type becomes as update. So, in a simple word if you are passing records from update strategy with (DD_UPDATE) to Lookup then Choose Update Else Inset otherwise choose Insert Else Update. Be careful while choosing this option otherwise your cache won’t update L

Output Old Value on Updateà Check box option… Select it according to your requirement if you need lookup to return old value after updating cache with new value.

Update Dynamic Cache Condition--> Through this option we can develop expression. When an expression evaluation is true then only update Cache. Ex: (Source_Sal!= LKP_Sal)
Yes, now we have power. Thank you, Informatica. Means before 9.x this option was not available. L

Phewww.. I am too tiered now… For more details Please open our Bible, Yes; Informatica Help J

4 comments:

  1. Such a great help for all the informatica learners . Thanks for all the complex scenarios and very clear explanations :) .. That's really appreciable :)

    ReplyDelete
  2. can any one tell me in real time, in which scenario we check/uncheck this option "Output Old Value on Updat" ?
    In real time example.

    ReplyDelete
  3. Can anybody make it clear that what happens when Update Dynamic Cache Condition is just set to TRUE and the cache is static?

    ReplyDelete