________________________________________
Looking for Data Integration at OpenWorld 2009? Check out here!
The posts in this series assume that you have some level of familiarity with ODI. The concepts of Interface and User Function are used here assuming that you understand them in the context of ODI. If you need more details on these elements, please refer to the ODI Tutorial for a quick introduction, or to the complete ODI documentation for detailed information..
This post will give some examples of how and where user functions can be used in ODI. We will look back at a previous post and see why and how user functions were created for this case.
1. A CASE FOR USER FUNCTIONS
As I was trying to design a simple way to generate random data, I simple approach to specify the type of data to be generated. An example would be to easily generate a random string.
Working on Oracle for this example, I could leverage the database function DBMS_RANDOM.STRING. It takes 2 parameters: one for the type of characters (uppercase, lowercase, mixed case), and on for the length of the string. But I want a little more than this: I also want the ability to force my generation to have a minimum number of characters. For this, I now need to generate a random number. The database function DBMS_RANDOM.VALUE does this, but returns a decimal value. Well, the TRUNC function can take care of this... but my mapping expression becomes somewhat complex:
DBMS_RANDOM.STRING(Format, TRUNC(DBMS_RANDOM.VALUE(MinLen,MaxLen)))
Imagine now using this formula over and over again in your mappings - not the easiest and most readable portion of code to handle. And maintenance will easily become a nightmare if you simply cut and paste...
A user function makes sense at this point, and will provide the following benefits:
- A readable name that makes the usage and understanding of the transformation formula a lot easier
- A central place to maintain the transformation code
- The ability to share the transformation logic with other developers who do not have to come up with the code for this anymore.
From a code generation perspective, if you use the User Function in your interfaces, ODI will replace the function name with the associated code and send that code to the database. Databases will never see the User Function name - the substitution is part of the code generation.
2. BUILDING THE USER FUNCTION
You need to provide 3 elements when you build a user function:
- A name (and a group name to organize the functions)
- A syntax
- The actual code to be generated when the developers will use the functions
2.1 Naming the User Function
The name of the user function is yours to choose, but make sure that it properly describes what this function is doing: this will make the function all the more usable for others. You will also notice a drop down menu that will let you select a group for this function. If you want to create a new group, you can directly type the group name in the drop down itself.
For our example, we will name the user Function RandomString and create a new group called RandomGenerators.
2.2 The User Function Syntax
The next step will be to define the syntax for the function. Parameters are defined with a starting dollarsign:
$
and enclosed in parenthesis:
().
You can name your parameters as you want: these names will be used when you put together the code for the user functions, along with the $ and (). You can have no parameters or as many parameters as you want...
In our case, we need 3 parameters: One for the string format, one for the minimum length of the generated string, one for the maximum length. Our syntax will hence be:
RandomString($(Format), $(MinLen), $(MaxLen))
If you want to force the data types for the parameters, you can do so by adding the appropriate character after the parameter name: s for String, n for Numeric and d for Date. In that case our User Function syntax would be:
RandomString($(Format)s, $(MinLen)n, $(MaxLen)n)
2.3 Code of the User Function
The last part in the definition of the user function will be to define the associated SQL code. To do this, click on the Implementation tab and click the Add button. A window will pop up with two parts: the upper part is for the code per se, the bottom part is for the selection of the technologies where this syntax can be used. Whenever you will use the User Function in your mappings, if the underlying technology is one of the ones you have selected, then ODI will substitute the function name with the code you enter here.
For our example, select Oracle in the list of Linked Technologies and type the following code in the upper part:
DBMS_RANDOM.STRING($(Format), TRUNC(DBMS_RANDOM.VALUE($(MinLen),$(MaxLen))))
Note that we are replacing here the column names with the names of the parameters we have defined in the syntax field previously.
Click on the Ok button to save your syntax and on the Ok or Apply button to save your User Function.
3. EXPANDING THE USER FUNCTIONS TO OTHER TECHNOLOGIES
Once we are done with the previous step, the user function is ready to be used. One downside with our design so far: it can only be used on one technology. Chances are we will need a more flexibility.
One key feature of the User Functions is that they will give you the opportunity to enter the matching syntax for any other database of your choice. No matter which technology you will use later on, you will only have to provide the name of the user function, irrespectively of the underlying technology.
To add other implementations and technologies, simply go back to the Implementation tab of the User Function, click the Add button. Select the technology for which you are defining the code, and add the code.
Note that you can select multiple technologies for any given code implementation: these technologies will then be listed next to one antother.
4. USING THE USER FUNCTIONS IN IINTERFACES
To use the User Functions in your interfaces, simply enter them in your mappings, filters, joins and constraints the same way you would use SQL code.
When you will execute your interface, the generated code can be reviewed in the Operator interface. Note that you should never see the function names in the generated code. If you do, check out the following elements:
- User Function names are case sensitive. Make sure that you are using the appropriate combination of uppercase and lowercase characters
- Make sure that you are using the appropriate number of parameters, and that they have the appropriate type (string, number or date)
- Make sure that there is a definition for the User Function for the technology in which it is running. This last case may be the easiest one to oversee, so try to keep it in mind!
As long as you see SQL code in place of the User Function name, the substitution happened successfully.
Enjoy!
All Screenshots were taken using version 10.1.3.5 of ODI. Actual icons and graphical representations may vary with other versions of ODI.
참조 : ODI User Functions: A Case Study