5 Replies Latest reply: Jan 28, 2013 10:57 PM by 930307 RSS

    using analytical functions

    vai
      How do i make use of analytical functions in ODI?
        • 1. Re: using analytical functions
          Bhabani Ranjan
          go for using yellow interface. This is quite easy to achieve sub query, analytic function etc.
          • 2. Re: using analytical functions
            vai
            but how to use it in the same interface?
            • 3. Re: using analytical functions
              Bhabani Ranjan
              In one interface ? I dont think you can achieve this. It depends on on which column you want put analytic function and the
              same customization can be done on the KM. Give one example if i can give a try for the KM customization.

              Take a look
              http://etlanddwh.blogspot.in/2011/11/odi-11g-temp-interface-kullanm-yellow.html
              • 4. Re: using analytical functions
                JeromeFr
                Hi vp_ora,


                There is a little issue with the analytical functions. If you write directly SUM(number) OVER(partition by class1, class2), the ODI parser will see the SUM as an aggregate function and thus create a group by clause for all the other fields with no aggregate functions.

                Fortunately there are two workarounds :
                - You can create a user function in ODI. You can choose your syntax and fort the implementation you just have to put SUM($(param1)) OVER(partition by $(param2), $(param3)). See note 807527.1 on Metalink/MOS for more info.
                - You can use the workaround described here by Uli Bethke : http://www.business-intelligence-quotient.com/?p=905


                Hope it helps.

                Regards,
                JeromeFr
                • 5. Re: using analytical functions
                  930307
                  I have had success doing this by simply putting double quotes around the sum .

                  E.g. "SUM" ( AAA ) over ( partition by BBB )

                  I prefer this method as it also works if you run the SQL externally e.g. in SQL developer.