ODI 11g – Interface Builder Accelerator
By David Allan-Oracle on Apr 20, 2011
Often using ANY tool there are scenarios where there is a lot of grunt work, imagine Microsoft tools like Excel without VB and macros to accelerate and customize those boring repetitive tasks. Data integration and ETL design is exactly the same, the tool needs to expose an SDK to a base platform that you can use to make your life easier. Something to automate the grunt work that is common and very repetitive. The ODI 11g SDK let’s you script these kind of repetitive tasks. As an aside the ODI common format designer (see this post here) has a way for migrating like named objects, however using the SDK let’s you control much much more.
To illustrate I have created a simple interface construction accelerator that you can download (interfaceAccelerator.java or 18.104.22.168 and after here), the accelerator generates ODI interfaces from a control file that defines the interface name, the source and the target – simple and a nice example for demo purposes. If you look at the java code, it is very basic (no pun intended). It literally is a dozen lines of code. The image below illustrates the java program interfaceAccelerator using the ODI 11g SDK to take as inputs the configuration of the connection details and a control file specify the source to target datastore mappings.
The code when called has a bunch of command line parameters shown below and the standard input stream is the interface control file, so the command line looks like;
java –classpath <cp> interfaceAccelerator <url> <driver> <schema> <pwd> <workrep> <odiuser> <odiuserpwd> <project> <folder> < <control_file>
the control file provided in the standard input stream needs to be a comma delimited file with the following structure
for example a sample command line using an Oracle repository could be
java –classpath <cp> interfaceAccelerator jdbc:oracle:thin:@localhost:1521:ora112 oracle.jdbc.OracleDriver ODI_MASTER mypwd WORKREP1 SUPERVISOR myodipwd STARTERS SDK < icontrol.csv
the interfaces will be created in the folder SDK and the project code is STARTERS. The icontrol.csv file used above was (remember the format is interface_name,source_model,source_table,target_model,target_table, this is just what I happened to use in this simple demo program);
You can created as many interfaces from this driver control file as you desire, the interface generated will map from the source table to the target table and use ODI’s auto mapping to perform column level mapping of the source to target table, it will also create default source sets and use the default KM assignment. So you get a pretty useful set of stuff as a basis here.
The interfaces generated whilst executing this accelerator look like the following, the table to table map with all of the like-named columns mapped, the physical flow configured with defaults KMs!
You can take this code and customize to make it fit your needs or send in comments on how to do things. In summary if you are finding you desire ways of tuning your work to make using ODI even more productive, then you should look into the ODI 11g SDK and see if you can automate, automate, automate.