4 Replies Latest reply on Aug 9, 2021 10:09 PM by JanLeendert Wijkhuijs

    Get MAX values on Pushdow optimization

    Rudi Ribere New Member



      I've already asked a very similar question but now I'm testing Pushdow optimization.


      This is my source  (TABLE)

      product    Line   Order     Toto

      q1           10        20        3

      q1           30       300       6

      q1            5       2000      8

      q2           20       30        3

      q2           300    400       8

      q3           20      200       9

      q3           40     3000      5

      q3           10     7000      3

      q3           20     1200      4


      And a want to get on my target the lines whit the max Order for each product.


      TGT (TABLE)

      product    Line   Order    Toto

      q1            5     2000     8

      q2           300    400      8

      q3           10     7000     3


      Normally I should create a variable to be able to calculate the previous value of the product,but the Pushdow optimization doesn't allow me to use a variable.


      So after the SQ i'm using a sorter to sorte my data by the product and order (desc)

      after the sorter im using a EXP where i create the output port prev_PRODUCT  =  PRODUCT

      and after that im using one more EXP2 where i have a output port FILTER= IIF(PRODUCT = prev_PRODUCT, 'TRUE', 'FALSE')


      My FILTER port need to be on the first EXP but i can't use the  output port prev_PRODUCT in the output port FILTER.


      Can you help me with that?

        • 1. Re: Get MAX values on Pushdow optimization
          Syed Aziz Guru

          Hello Rudi,

          Have you tried using an additional upstream transformations to determine the values to use in the output port?




          The Integration Service should be able to generate an SQL statement for an expression that does use output ports from upstream transformations because it can then determine the values to use.




          Best regards,
          • 2. Re: Get MAX values on Pushdow optimization
            JanLeendert Wijkhuijs Guru

            Hi Rudi,


            If you want to have this logic with PDO you must change the approach.

            In this case I would read the source twice, once the product column and the order column and perform an aggregation on the order (group by product)
            After the aggregator join on both product and order column on the data with all the columns (inner join)


            Check also KB article 109671 about not supported transformations and functions


            Hope this helps


            Jan Leendert

            2 of 2 people found this helpful
            • 3. Re: Get MAX values on Pushdow optimization
              Rudi Ribere New Member

              I'm wondering why I didn't think of this solution


              • 4. Re: Get MAX values on Pushdow optimization
                JanLeendert Wijkhuijs Guru

                Hi Rudi,


                PDO offers you a lot of opportunities, not only in performance but also in building mappings.

                Would you ever imagine building a mapping with source/source qualifier and then a filter transformation?

                (except on flatfiles of course).
                But if you do PowerCenter will treat the filter transformation as source filter when you're using PDO.
                So you have all the functions etc available but you don't need to worry about syntax, PowerCenter will do at runtime when creating the query.
                It's also very convenient when switching RDBMS. I have experienced switching from Oracle to SQL-server but also SQL-server to AWS Redshift.