5 Replies Latest reply on Jan 24, 2018 7:24 AM by Nico Heinze

    Using same lookup table multiple times in same mapping with different join conditions

    Reddaiah Kasturi Active Member

      Hello SMEs,

       

      I have a
      requirement where same dimension lookup table to read 3 times with different
      join conditions and pass the output row to next transformation in the mapping.
      Here are the details for example,

       

      As you see
      above, I need to read T2 table 3 times to pull all datasets with different join
      column in each lookup. We can use persistent cache for first lookup and reuse
      same for second and third time, but I guess it works for same lookup conditions
      and same lookup query.  Could someone please suggest how to approach to
      avoid caching same data multiple times?

      Look up table
      has around 5 million rows to cache, it caches 3 times in same mapping, it means
      15 million rows getting stored in cache every time.

      Any best
      solution would be greatly helpful to increase performance of session. We are
      using 9.6.1

        • 1. Re: Using same lookup table multiple times in same mapping with different join conditions
          Nico Heinze Guru

          I don't know of any good "generic" solution for such a request. What I can propose assumes that you want to perform a lookup at "different levels", so to speak. Here's an example:

           

          Let's assume (granted, this is not a very practical example) your dimension table has these key columns:

          - COUNTRY_ID,

          - STORE_ID,

          - PRODUCT_ID.

           

          Let's further assume that first you want to perform a lookup based on all three columns.

          If this LKP doesn't find anything, look up based on country ID and store ID.

          If this LKP doesn't find anything either, look up based on COUNTRY_ID.

           

          If that is the case in your use case, then there might be an easy approach to use one and the same cache everywhere:

           

          First perform a LKP based on all three input values: country ID, store ID, and product ID.

          If this LKP finds something, fine, we're done.

           

          If this first LKP didn't yield anything, look up based on country ID, store ID, and a "generic" product ID.

          If this LKP finds something, fine, we're done.

           

          If this second LKP didn't yield anything either, then look up based on country ID, a "generic" store ID, and the "generic" product ID.

           

          Now the big question is: what the heck are these "generic" store and product IDs?

          These are "fake" IDs, they don't exist in real-life data. You have to insert them into the cache file yourself.

          For example, you could build your persistent cache file using a SQL Override; this SQL Override not only reads all real data from the "lookup" table, it also SELECTs (via a UNION ALL clause) two more records from a "dummy" table, e.g. DUAL in Oracle or SYSIBM.SYSDUMMY1 in DB2.

          Then the cache will contain one additional record for each COUNTRY_ID (namely with the "generic" store ID and the "generic" product ID) and one additional record for each unique combination of COUNTRY_ID and STORE_ID (namely with the "generic" product ID).

           

          Of course this assumes that you can identify a "fake" product ID and a "fake" store ID which will never occur in real-life data.

           

          Regards,

          Nico

          • 3. Re: Using same lookup table multiple times in same mapping with different join conditions
            Nico Heinze Guru

            Did you have a chance to try this approach? Did it work out fine?

             

            Regards,

            Nico

            • 4. Re: Using same lookup table multiple times in same mapping with different join conditions
              Reddaiah Kasturi Active Member

              Hi Nico,

              My case is bit different, would never have condition on all 3 columns together.

              As I explained in my diagram, will match on c1 first if it is found then we bring dimension key. if no match we match with c2 then bring key if not match with c3 then bring key value.

               

              Thanks

              Reddy.

              • 5. Re: Using same lookup table multiple times in same mapping with different join conditions
                Nico Heinze Guru

                Yes, I understood that.

                That's why I suggested to see whether you can find any artificial values for C2 and C3 which will never occur in real life. Then you can set up the lookup conditions like this:

                First lookup (on C1 alone):

                T1.C1 = input C1 and T1.C2 = (fake value for C2) and T1.C3 = (fake value for C3).

                 

                Second lookup (checked only if LKP #1 doesn't yield anything):

                T1.C1 = input C1 and T1.C2 = input C2 and T1.C3 = (fake value for C3).

                 

                Third lookup (checked only if neither LKP #1 nor LKP #2 yielded anything):

                T1.C1 = input C1 and T1.C2 = input C2 and T1.C3 = (fake value for C3).

                 

                Of course this requires one thing in preparation:

                You have to make sure that the lookup cache (which is shared between all three LKP transformations) does contain records for all valid C1 values and fake value for C2 and fake value for C3 AND for all valid C1&C2 values and fake value for C3.

                Only then will the cache contain all these records for the three lookup conditions shown above.

                 

                Regards,

                Nico