Oracle Analytic Functions in ODI

Oracle Analytic functions are a great way to write efficient, complex SQL statements. Instead of having to write multiple joins and subqueries you can write a similar statement in just one line. This is a great time saver especially when using a tool such as Oracle Data Integrator (ODI), which makes it difficult to do subqueries. Unfortunately, ODI’s knowledge modules do not support all analytic function out of the box. The problem is when ODI sees the SUM keyword it automatically triggers the use of the GROUP BY and HAVING clause regardless if is a regular SUM or analytical query SUM. If you ever tried using such a function in ODI you probably received “ORA-00979: not a GROUP BY expression”.

With just a few lines of code you can easily implement a solution to fix this issue:

1) Navigate to the KM you wish to customize to use analytic functions (can be either LKM or IKM).

2) Create a new KM Option

3) Open the knowledge module and navigate to the Details tab, “Load Data” step (or “Insert flow into I$ table” step for IKM).

4) In the Definition tab look for the lines of code that contains (either Command on Target or Source):

<%=snpRef.getGrpBy()%>
<%=snpRef.getHaving()%>

And replace it with the following

<% if (odiRef.getOption("USE_ANALYTIC_FUNCTION").equals("0")){
out.print(odiRef.getGrpBy());
out.print(odiRef.getHaving());
} else
{
out.print("--Group by functions are suppresses by KM");
}
%>

5) Click the option tab and be sure to check your option name (USE_ANALYTIC_FUNCTION). Click Okay to complete.
6) When creating your interface and choosing your KM, in the flow tab you now have the ability to select the user defined USE_ANALYTYIC_FUNCTION.

The USE_ANALYIC_FUNTION option works by suppressing the GROUP BY and HAVING clause of the query when the value of Yes is selected. Because the GROUP BY will not be used you can use any analytic function you like.