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”.

6 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.

  2. Hi all,

    I am PLSQL developer. and new to ODI. can you any one guide me, how to call PLSQL function and PLSQL procedures in ODI12c mappings.

    Thanks & Regards,

    • Hi Raj,
      You can call any Oracle PL/SQL function directly in the mappings (formerly interfaces), procedure steps, joins, etc. just like you execute them in database by using TOAD, SQL Developer, etc. Be careful about the login credentials, ODI is connecting to the database with the credentials you provide in the Physical Topology.
      You have to select Oracle as technology and select appropriate Logical Schema to run.

      Cheers — Gurcan.

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.