Tuesday Jan 14, 2014

OWB to ODI Migration Utility now available for OWB +CP3 Linux and Windows 64-bit standalone installation.

The OWB to ODI Migration now supports migration from OWB version to ODI 12c.

It is available as "Patch 17224695: OWB-ODI MIGRATION PATCH FOR 64BIT STANDALONE INSTALL" and can be downloaded from the support website.

This patch(17224695) supports migration from Linux 64-bit and Windows 64-bit standalone OWB + CP3( Patch 16568042) to ODI + Patch 17053768.

More information about the migration utility is here http://docs.oracle.com/middleware/1212/odi/ODIMG/index.html

Tuesday Aug 07, 2012

OWB – ANSI and Oracle SQL code generation

There is a configuration property in OWB for switching between ANSI SQL code generation and Oracle SQL. It is under the ‘Code generation options’ in the mapping configuration. The join condition is expressed in Oracle SQL join syntax and OWB will reinterpret if generating ANSI SQL.

You can change the value to false, generate the code and inspect it inline within the mapping editor;

The 11gR2 release of OWB has changes in the join component to allow you to express the join type in a logical manner, so you can indicate outer join on a group for example.

Thursday Jun 07, 2012

ODI 11g – Faster Files

Deep in the trenches of ODI development I raised my head above the parapet to read a few odds and ends and then think why don’t they know this? Such as this article here – in the past customers (see forum) were told to use a staging route which has a big overhead for large files. This KM is an example of the great extensibility capabilities of ODI, its quite simple, just a new KM that;

  1. improves the out of the box experience – just build the mapping and the appropriate KM is used
  2. improves out of the box performance for file to file data movement.

This improvement for out of the box handling for File to File data integration cases (from the companion CD and on) dramatically speeds up the file integration handling. In the past I had seem some consultants write perl versions of the file to file integration case, now Oracle ships this KM to fill the gap. You can find the documentation for the IKM here. The KM uses pure java to perform the integration, using java.io classes to read and write the file in a pipe – it uses java threading in order to super-charge the file processing, and can process several source files at once when the datastore's resource name contains a wildcard. This is a big step for regular file processing on the way to super-charging big data files using Hadoop – the KM works with the lightweight agent and regular filesystems.

So in my design below transforming a bunch of files, by default the IKM File to File (Java) knowledge module was assigned. I pointed the KM at my JDK (since the KM generates and compiles java), and I also increased the thread count to 2, to take advantage of my 2 processors.

For my illustration I transformed (can also filter if desired) and moved about 1.3Gb with 2 threads in 140 seconds (with a single thread it took 220 seconds) - by no means was this on any super computer by the way. The great thing here is that it worked well out of the box from the design to the execution without any funky configuration, plus, and a big plus it was much faster than before,

So if you are doing any file to file transformations, check it out!

Friday Feb 24, 2012

ODI 11g - Getting Scripting with Groovy

The addition of the groovy interpreter to the ODI designer now let’s you easily script any tasks that you repeatedly perform. The documentation has illustrations here, so using the ODI 11g SDK you can encapsulate common tasks in simple groovy functions.

Groovy can be executed by executing a script, you can create a new one or open an existing groovy script;

You will then see a new groovy window appear in the IDE plus the execute green button is enabled on the toolbar.

I have taken the script defined here and shown below in its more minimal groovy form and parameterized the script in a groovy function ‘createProject’. I can then call createProject with whatever values for the project and folder I wish to create.

import oracle.odi.core.persistence.transaction.support.DefaultTransactionDefinition;
import oracle.odi.domain.project.OdiProject;
import oracle.odi.domain.project.OdiFolder;

def createProject(projectName, projectCode, folderName) {
  txnDef = new DefaultTransactionDefinition();
  tm = odiInstance.getTransactionManager()
  txnStatus = tm.getTransaction(txnDef)
  project = new OdiProject(projectName, projectCode)
  folder = new OdiFolder(project, folderName)

createProject("EDW Staging", "EDW", "Initialization")

So in the UI if I execute as follows;

After executing the script I refresh the Designer tree and see my new project.

Thursday Feb 09, 2012

ODI 11g – Interface Builder

In the previous blogs such as the one here I illustrated how to use the SDK to perform interface creation using various auto mapping options for generating 1:1 interfaces either using positional based matching, like names ignoring case and so on. Here we will see another example (download OdiInterfaceBuilder.java) showing a different aspect using a control file which describes the interface in simple primitives which drives the creation. The example uses a tab delimited text file to control the interface creation, but it could be easily taken and changed to drive from Excel, XML or whatever you wanted to capture the design of the interface.

The interface can be as complete or incomplete as you’d like, so could just contain the objects or could be concise and semantically complete.

The control file is VERY simple and just like ODI requests the minimal amount of information required. The basic format is as follows;

Directive Column 2 Column 3 Column 4 Column 5
source <model> <datastore>    
   can add many        
target <model> <datastore>    
mapping <column> <expression>    
   can add many        
join <expression>      
   can add many        
filter <expression>      
    can repeat many        
lookup <model> <datastore> <alias> <expression>
   can add many        

So for example the control file below can define the sources, target, joins, mapping expressions etc;

source    SCOTT    EMP
source    SCOTT    DEPT
mapping    ENAME    UPPER(EMP.ENAME)
mapping    DEPTNO    ABS(EMP.EMPNO)
filter    EMP.SAL > 1
mapping    COMM    ABS(BONUS.COMM)

When executed, this generates the interface below with the join, filter, lookup and target expressions from the file.

You should be able to join the dots between the control file sample and the interface design above.

So just like the initial post you will compile and execute the code, but use the different classname OdiInterfaceBuilder;

java –classpath <cp> OdinterfaceBuilder jdbc:oracle:thin:@localhost:1521:ora112 oracle.jdbc.OracleDriver ODI_MASTER mypwd WORKREP1 SUPERVISOR myodipwd STARTERS SDK DEMO1 <myinterfacecontrolfile.tab

The interface to be created is passed from the command line. You can intersperse other documentation lines between the control lines so long as the control keywords in first column don’t clash.

Anyway some useful snippets of code for those learning the SDK, or for those wanting to capture the design outside and generate ODI Interfaces. Have fun!

Wednesday Oct 05, 2011

Sting'ing in the rain?

Hopefully this is a good omen for the weather for the appreciation event tonight in San Francisco! Great seeing all of the ODI and OWB folks at OpenWorld this week!


Monday Jun 13, 2011

OWB – Calling Java from Process Flows

Although the doc shows how to call java from process flows it still may not be clear enough..or even correct, here’s a quick illustration to plug the gap. The section on the JAVA activity is here just in case you didn’t know. Below is the simple example java class I fabricated…

public class javatest {
public static void main(String args[]) {
  System.out.println("In main of my java, with " + args.length + " arguments.");
  for (int i = 0; i < args.length; i++) {
    System.out.println("  arg  " + i + " : " + args[i]);
  if (args.length == 2) {
    int rslt = new Integer(args[0]) + new Integer(args[1]);

The java I will call is the static main method which takes an array of arguments and adds the first and second argument and uses that as the status. A silly example I know…but at least you see how parameters are passed into and out of the java call. There is also some debug to print the parameters etc so you can see where it goes in OWB. I compiled this class with JDK 1.5 (it must be 1.5 or less than) and jar’d the class file into a jar file.



The classpath, class_name and result_code parameters to the JAVA activity are pretty much standard as you would setup any activity in process flow. The parameter passing into the java activity is done via the PARAMETER_LIST activity parameter, see the value ,$(PARAM1),$(PARAM2) this uses the custom parameters PARAM1 and PARAM2 that I added to the JAVA activity in my flow.

In the above screenshot I have tried to show all of the important parts for configuring this example, the JAVA activity input parameters are bound to the process flow parameters PROC_ARG1 and PROC_ARG2 respectively.

So when I execute the flow, the arguments used will be passed to the java class. So if I pass 0 and 0 for the arguments the result of the java will be 0+0, 0 is a success code for the java activity. I can see what happened in the audit, I can see the process parameters and the activity parameters, I can also see the std output from the java, see the ‘Execution Details’ dialog and the Log panel for that output…


If I execute the process flow passing the values 1 and 2, then the result of the java is 1+2 which is an error code in java. See below for when the values 1 and 2 are passed ….


There is another parameter that has not been used and that was the RUN_DIRECTORY parameter, this is simply the working directory to where the java command is invoked from. So this is course grained integration of java using process flows, in 11gR2 its also possible to invoke EJBs from a process flow, and there are other integration techniques from java stored procedures or tables functions to java in 11gR2 Code Templates.

Saturday May 21, 2011

ODI - Extending the Interface Accelerator

The post here illustrates how to use the ODI 11g SDK to extend the classes provided to customize your own auto mapping capabilities;

Thursday May 12, 2011

ODI 11g - Getting Groovy with ODI

Here we will see how you can use the groovy interpreter to automate, automate, automate (with no compile,compile,compile). When you get into large enterprise configurations of software, automation is the name of the game. The ODI 11g SDK as I blogged here lets you interact with pretty much the entire ODI set of metadata. The example I posted was a interfaceAccelerator.java (or for post here) is a java class that needs compiled and then executed. Rather than compiling and executing (and needing to understand much of the build side of java), let’s look at an alternative….

In OWB, there is an interpreter/interactive shell called OMBPlus and language that lets you automate the construction of artifacts and get smart with the tool. With ODI you can leverage the groovy interpreter for example, which lets you interact with java objects. You’ll need to download groovy and install. When groovy is installed you can then either execute the groovy process and process a groovy script as input or use the groovysh shell/interpreter. The groovy scripts can be scripts which use the ODI 11g SDK and interact with ODI.

For example the command below, takes the groovy script interfaceAccelerator.groovy or post here (which is a groovy version of the java class file previously used) as input, and the tabs.txt is piped in the standard input stream.

groovy interfaceAccelerator.groovy < tabs.txt

The groovy script is a mixture of groovy commands and ODI SDK calls to generate the interfaces in ODI.

The CLASSPATH including all the ODI jars is one thing that needs set up prior to executing the groovy script above, I just included all jars in the oracledi.sdk\lib directory.

There was a small change in the transaction handling APIs after production 11g ODI, hence the different versions of the groovy and java scripts.

Lots of possibilities as you can imagine! There are a few cool things we can extend the actual interfaceAceelerator to do...regardless of groovy or java, but just some more useful capabilities, more to come.

Sunday Jan 18, 2009

OWB Integration with Salesforce.com Data: Sometimes The Old Ways are the Best Ways

A request that we get frequently enough to comment on is, "How do I use OWB to move data between Salesforce.com and my Oracle database?" Data warehouse customers in particular seem to be running into this one. Usually, the person asking expects that some kind of web service call will be the solution of choice. Perhaps it would be, in some arcane cases. There are also ODBC drivers for Salesforce.com, which OWB could connect to with similar results to other ODBC data sources. (Of course, Oracle support won't help with those third-party components.) However, bulk data movement is the most common case, and there are tried and true ways of doing that without the latest SOA secret sauce. The moving parts of the simple-enough solution are: [Read More]

ETL, CDC, Real-Time DI and Data Quality for the Oracle Database from the inside.


« October 2015