Gurcan Orhan's Oracle Data Integrator Blog


Adding Functions to ODI

In most cases we (ETL developers) are stuck into doing something. Sometimes an analytic function helps, sometimes a function in database. But is there a way to use this function in a structured way that ODI can understand? Let’s try to find out. :-)

Languages in Topology Manager help you leverage your languages that you can use in ODI. You can even add a new language (if you know and eager to type all commands).

So let’s add the analytic function LEAD to ODI. Right click “Aggregate” and select “Insert Language Element” from drop down menu.

Adding Analytic Function #1

Adding Analytic Function #2

Let’s discuss about Definition and Implementation of function named LEAD.

  • LEAD is used in Oracle Technology and you may add other technologies that is supported by LEAD or it may be even a database function that you create in database.
  • LEAD has 3 parameters.
  • LEAD is NOT a group by function (when ODI catches LEAD with 3 parameters.
  • LEAD is NOT standard function (example : COUNT or SUM are standard).
  • LEAD is NOT a universal function (example : COUNT or SUM are universal).

But what happens when we want to add MIN, MAX, COUNT or SUM as an analytic function?

Answer is simple, just add “x” at the end. MIN is stored for universal, standard, group function and you cannot use the name “MIN” with analytic function parameters (partition by, order by).

Create a new function MINx (MAXx, COUNTx, SUMx) with 3 parameters in expression and implement it to Oracle technology without selecting “Group Function”, “Universal” and “Standard”.

About these ads


  1. What a nice post ! This is the just one reason why we should ODI for data integration affairs.

    Comment by Ahmet Fuat Sungur — 05.12.12 @ 11:49 | Reply

  2. [...] were recently some good posts by David Allan and Gurcan Orhan on the power of ODI functions. David mentions correctly that ODI functions are the most underrated [...]

    Pingback by Extreme re-usability in ODI 11g (I can’t believe I am giving away this trick). | BI-Quotient — 07.01.13 @ 20:17 | Reply

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Blog at


Get every new post delivered to your Inbox.

Join 554 other followers

%d bloggers like this: