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.
In order to master scripting in ODI I recommend the following books.