Extreme re-usability in ODI 11g (I can’t believe I am giving away this trick).
Get our e-books Discover the Oracle Data Integrator 11g Repository Data Model and Oracle Data Integrator Snippets and Recipes
Another Christmas and a second baby under my belt it’s time to get back to blogging.
There 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 feature in ODI. Whenever you think of re-usability in ODI think of user functions. Functions can be used anywhere in ODI where you need to write something once and apply it many times. This is not limited to using functions inside interfaces to load columns or the use of wrapping and parameterizing common functionality. You could just write a snippet of SQL, e.g. a commonly used filter in a WHERE clause as a function and reuse it many times. The nice thing about functions is that you can use substitution method API calls in a function, use Java or the ODI tools.
Use case for re-use of user functions
One of the things that have puzzled me about ODI and Knowledge Modules is that some of the steps in the KMs are repeated and re-used over and over again without some central placeholder. Why not write the step once and re-use it many times across the Knowledge Modules? This is exactly what I will show you. We will take table stats gathering as an example. One of the steps in Knowledge Modules is to gather table stats once the target table has been loaded. Why not create a user function that gathers table stats on a target table and takes the estimate percent size as a parameter?
In a first step let’s create the function. As a recommendation I would suggest to prefix your functions with a convention. In a first implementation I had GATHER_TABLE_STATS for the syntax, which wreaked havoc with any KMs that are using the Oracle GATHER_TABLE_STATS procedure in package dbms_stats.
And the implementation
|
1 2 3 |
BEGIN dbms_stats.gather_table_stats ( ownname => '<%=odiRef.getInfo( "DEST_SCHEMA" )%>', tabname => '<%=odiRef.getTargetTable("RES_NAME")%>', degree => DBMS_STATS.AUTO_DEGREE, estimate_percent => $(sample_size), cascade => TRUE ) ; END; |
Next we will use the function as a step in a (global) Knowledge Module and pass in 10 as a parameter for the estimate_percent part of the stats gathering piece. In this particular case I have modified the IKM SQL Control Append.
Then we create and execute an interface that uses this IKM
As you can see the function was substituted at runtime and stats were gathered on the target table.
This is just one example where you can benefit from user function re-usability. Be creative and think out of the box and you will see user function written over everything.
