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