Analytic Functions In OWB

August 27th, 2004 by Mark Rittman


Analytic Functions In Oracle Warehouse Builder: "Analytic Functions are
an important feature of the Oracle database that allows the users to enhance
SQL’s analytical processing capabilities. These functions enable the user to
calculate rankings and percentiles, moving window calculations, lag/lead
analysis, top-bottom analysis, linear regression analytics and other similar
calculation-intense data processing. This whitepaper describes how to use some
of these analytical functions in the ETL (extraction, transformation and
loading) processing implementation using Oracle Warehouse Builder."

Posted in Oracle Warehouse Builder |

Comments

  1. Subhra Dhar Says:

    May 5th, 2008 at 1:40 pm

    Can’t we use Oracle analytic functions in OWB?
    I am working on version 10.2.0.2, it is not allowing me to use aggregate function SUM or the analytic function LEAD etc in OWB Expression editor. I want to find the cumulative sum, the next record from the current record etc for a group.
    Is there any workaround available?

  2. Peter Scott Says:

    May 6th, 2008 at 7:35 am

    As the Oracle white paper says, it is not possible (yet) to use functions that have a non-analytic version with the same name (SUM, AVG etc) as the expression operator will not allow it. I did not know that LEAD also did not work, but to be honest have never tried that one.

    One suggestion is to encapsulate the analytic functions in a view definition and to use the view in the mapping. This may require you to revise the mapping to persist an intermediate result into a physical table, as in the case of a map with a lot of transform steps downstream of the proposed analytic function