Tuesday Nov 16, 2010

ODI 11g – Simple, Flexible, Powerful


SQL as a source, inline SQL, SQL override...want to know more ....? A couple of specific enhancements in the code generation capabilities of ODI 11g, opens the door to an area of code generation which provides great benefit from the ability to have SQL as a source to the construction of even better performing code to better interface designs and accelerators.

So what are they? The two key changes are;

  • support for sub-select to chain, when possible, multiple interfaces into one. With ODI 11g the code generation capabilities of ODI were extended in order to build larger more complex interface designs without the necessity of staging via temporary interfaces. There have already been blog posts on this subject, for example see Uli Bethke's post here.
  • a property in the KM definition to define current command for sub-select. A new property defined on a command in a KM to indicate to the ODI code generator that this command should be used for generating the SQL statement for a sub-select. This one is the key change in addition to the support for sub-select that opens the door. Is the penny dropping yet?

These two together open the door to building operator(or transformation)-like KMs within the bounds of the information that can be defined by a temporary interface and the options on the KM.

Let's see...

Simple Example - The SQL as Source IKM

Let's build a very simple example that is similar to Informatica's SQL override capability or inline views - SQL as source. So to the new breed of code template an IKM with the SQL code generator to be used for sub-query. We define an IKM and have the target technology just now set to Oracle;


Now comes the interesting part, we can define a command to fulfill the SQL generation. This can be as simple as actually just being the SQL code (SQL as source) or something a little more complex such as fulfilling a transformation such as a pivot or table function.

Let's check out the command definition, its very simple - the SQL representing the subquery will be an option on the IKM. Don't worry you won't have to build this KM its on the code samples already, you can download the KM here. (I built it with Oracle as a target technology at first then changed for all using undefined technology). Also it will become blatantly obvious how its used when you see a simple example.


From the above you see 2 things; 'Use current command for Derived-Table sub-select statement' is checked, a bit of a mouthful, basically ODI will use THIS command in the IKM for generating the SQL when a temporary interface is used in an interface and the sub-select check box is selected.

The IKM has an option VIEWQUERY. When the IKM is used in an interface design this is where you will define the SQL as a source.


That's a peek inside the IKM, we can now build temporary interfaces that are essentially inline view designs that will define the view columns (the target datastore columns in the temporary interface) and the SQL query which is to be used to realize the interface. With ODI we can go the extra mile and use the ODI reference APIs to ensure the SQL is portable also across systems so that we don't hard-wire access, schemas etc.

Building the inline view definition

Like any view (in database terms) we define a signature - the columns to be projected are defined on the temporary target datastore on an ODI temporary interface, below you see I have defined a comment (--) for the mapping expression and the indicator is for the target;


The execution unit is assigned the SQL_as_Source IKM, where the SQL query is then defined on the VIEWQUERY option.


Above I have hard-wired the query, as mentioned we can use ODI reference APIs to get the physical name from the datastore in a flexible manner. So the query could be defined using the odiRef.getObjectName which makes the statement much more flexible and provides flexibility via the context to use different physical schemas for example.


That is the SQL as a source or inline view defined. We can reuse this definition many times in different interfaces. Let's see how it is used.

Using the inline view in an interface

The temporary interface (our inline view definition) can be dropped into other interfaces and used like any other interface in ODI, now in 11g as we have seen from other posts we can include the interface as a sub-select. The INLINE_VIEW can be joined  with other tables etc and used like any other interface artifact.


Above I am using the INLINE_VIEW temporary interface and have selected the Use Temporary Interface as Derived Table (Sub-Select), since the tables used by the view are from the same server as the BONUS table.

So what code gets generated?

Simulating the code in ODI 11g we see the SQL statement we defined in our temporary interface nested inside the overall code (this was using the had-wired SQL rather than the odiRef'd version).


This KM lets you then use a temporary interface as your SQL Query or inline view and provides a great mechanism to balance fully fledged design using interfaces and integrating custom arbitrary SQL.

You can download the SQL as Source KM from the code samples here on java.net. The example illustrates a capability that we can exploit for other cases - more to come!



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


« July 2016