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

3 thoughts on “Adding Functions to ODI

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

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