Skip navigation
Contact Us
23756 Views 4 Replies Latest reply: Feb 15, 2013 7:24 AM by AL PACINO . RSS
radhika kodepaka Newbie 1 posts since
Mar 29, 2011
Currently Being Moderated

Mar 29, 2011 3:34 AM

In what scenarios we can use dynamic cache . Please explain me with some real time scenario

In what scenarios we can use dynamic cache . Please explain me with some real time scenario

  • Ori Levran InfaEmp 46 posts since
    Jul 23, 2010


    I assume you refer to dynamic cache for lookups in PowerCenter. if that is the case then this post is not on the right forum...


  • Dakshayani Velakaturi Newbie 15 posts since
    Mar 28, 2011

    Hi Radhika,


    When the Integration Service reads a row, it changes the lookup cache depending on the results of the lookup query and the Lookup transformation properties you define. It assigns the value 0, 1, or 2 to the NewLookupRow port to indicate if it inserts or updates the row in the cache, or makes no change.

    So, How does dynamic lookup work?


    0 Integration Service does not update or insert the row in the cache.

    1 Integration Service inserts the row into the cache.

    2 Integration Service updates the row in the cache.


    When the Integration Service reads a row from the source, it updates the lookup cache by performing one of the following actions:



    Inserts the row into the cache: If the incoming row is not in the cache, the Integration Service inserts the row in the cache based on input ports or generated Sequence-ID. The Integration Service flags the row as insert.



    Updates the row in the cache: If the row exists in the cache, the Integration Service updates the row in the cache based on the input ports. The Integration Service flags the row as update.



    Makes no change to the cache: This happens when the row exists in the cache and the lookup is configured or specified To Insert New Rows only or, the row is not in the cache and lookup is configured to update existing rows only or,

    the row is in the cache, but based on the lookup condition, nothing changes. The Integration Service flags the row as unchanged.


    For example,


    If the source  and target contains the columns like ITEM_ID,ITEM_NAME,ITEM_DESC,PRICE,MANF_ID


    In the mapping i use the lookup table by looking the target table. In the lookup take all the columns from source as input. And give the condition as ITEM_ID=IN_ITEM_ID. And select the property Dynamic Lookup Cache, when u select this property the other 3 properties will be enable, click on Insert Else Update property also. By this u can see a new column in the lookup table by NewLookupRow which is in disabled state. And give all the output associated ports by their respective input ports which r taken from source.




    Take Router t/r and connect with the lookup o/p rows with the NewLookupRow also. Now, apply the conditions like NewLookupRow=1  and



    Then connect this router to an 2 Update Strategies, by all the columns except that NewLookupRow. and give the condition as DD_INSERT and in the second update strategy  give the condition as DD_UPDATE.

    Then connect this update with Targets.


    I hope this scenario will give u an idea about the dynamic cache.




    Thank you.

  • Rajendra Ongole Novice 96 posts since
    Dec 29, 2010


    If you are using Dynamic cache in lookup transformation. That will automatically comes with Associated port and New lookup row port(Dynamjcally insert or updates the data into target table) to connect the ports from source database.

    it will caches the data automatically from sources.


    0---- No inserts or No updates in cache

    1---- Insert data

    2-----Update data



    Mostly we use in ------Scd2 type.





  • AL PACINO . Newbie 10 posts since
    Nov 6, 2012



    We use normally dynamic lookup when there is a continuous changes in source system.


    The default cache property of the lookup table is static.


    When we do lookup of target table using static cache then when we run the integeration service it caches the target table only once through out the session. It will not upate any cache.


    Let say we have to load type 2 in target table.


    source columns

    A             B              C              

    1              Tony        Alive        


    Target Columns

    A             B            C           D                                     E                                     F

    1             Tony      Alive       2005-12-12 10:10:00      9999-12-12 00:00:00     I


    When we run again the  mapping then

    ´╗┐Target Columns

    A           B               C            D                                      E                                   F

    1           Tony          Alive       2005-12-12 10:10:00       9999-12-12 00:00:00    U



    Now the scenaio where dynamic vs static

    Let say we are running above session with the above target record and we have new records related to same id or name


    A            B                C

    1              Tony         Dead

    1              Tony         Again Alive


    We need to maintain type 2 for above records sequencially


    If we use static cache then we will get only one record with Alive..

    then while type 2 for above new 2 records the E columns will be same so if many cases the start date (E) will be key and throws duplicate rows. It really does't know which record to pickup we normally take 9999 reord to update with current time.


    In this case if we use dynamic cache then the cache is updated along with the target table.

    so the above 2 records are sequencially inserted with correct end dates(999)..


    note: some databases like Oralce doesn't support milliseconds. In that case there might be errors while loading.


    Real scenario means i can tell banking but i didn't work on this ..


    Let say you went to atm and with drawn some money with in 5 seconds you did another transaction.


    so these transactions has to be captured and the money has to be subtracted when you did second transaction.


    In this case a dynamic cache will work.


    But normally for transactions most of companies uses webservices. i just given example ;-)

More Like This

  • Retrieving data ...

Bookmarked By (1)


  • Correct Answers - 10 points
  • Helpful Answers - 5 points