ODI and analytic functions (again???)

Uli Bethke analytic functions, Oracle Data Integrator (ODI)

My friend and colleague Maciej Kocon has come up with the following trick. Rather than use a user defined function to implement an analytic function such as MAX() OVER or SUM() OVER as suggested by note 807527.1 there is another way to work around the getGrpBy bug.

We will take advantage of the ODI multi pass parser and hide the analytic function from getGrpBy in our mappings:

<?out.print("SUM");?>(SALES.AMOUNT_SOLD) OVER (PARTITION BY PRODUCTS.PROD_CATEGORY)

The above SUM gets only generated once getGrpBy has been parsed and as a result no GROUP BY clause is generated, which is the correct behaviour.

A brilliant yet simple trick.

Cheers Maciej.

Teach me Big Data to Advance my Career

In order to master scripting in ODI I recommend the following books.

Java BeanShell

Scripting in Java: Languages, Frameworks, and Patterns

Jython

The Definitive Guide to Jython: Python for the Java Platform.

Jython Essentials (O'Reilly Scripting)