Tuesday Mar 27, 2012

ODI 11g – Oracle Multi Table Insert

With the IKM Oracle Multi Table Insert you can generate Oracle specific DML for inserting into multiple target tables from a single query result – without reprocessing the query or staging its result.

When designing this to exploit the IKM you must split the problem into the reusable parts – the select part goes in one interface (I named SELECT_PART), then each target goes in a separate interface (INSERT_SPECIAL and INSERT_REGULAR).

So for my statement below…

/*INSERT_SPECIAL interface */ insert  all
when 1=1 And (INCOME_LEVEL > 250000) then

/* INSERT_REGULAR interface */ when 1=1  then
/*SELECT*PART interface */ select   
where    (1=1)

Firstly I create a SELECT_PART temporary interface for the query to be reused and in the IKM assignment I state that it is defining the query, it is not a target and it should not be executed.

Then in my INSERT_SPECIAL interface loading a target with a filter, I set define query to false, then set true for the target table and execute to false. This interface uses the SELECT_PART query definition interface as a source.

Finally in my final interface loading another target I set define query to false again, set target table to true and execute to true – this is the go run it indicator!

To coordinate the statement construction you will need to create a package with the select and insert statements. With 11g you can now execute the package in simulation mode and preview the generated code including the SQL statements.

Hopefully this helps shed some light on how you can leverage the Oracle MTI statement. A similar IKM exists for Teradata. The ODI IKM Teradata Multi Statement supports this multi statement request in 11g, here is an extract from the paper at www.teradata.com/white-papers/born-to-be-parallel-eb3053/

Teradata Database offers an SQL extension called a Multi-Statement Request that allows several distinct SQL statements to be bundled together and sent to the optimizer as if they were one. Teradata Database will attempt to execute these SQL statements in parallel. When this feature is used, any sub-expressions that the different SQL statements have in common will be executed once, and the results shared among them.

It works in the same way as the ODI MTI IKM, multiple interfaces orchestrated in a package, each interface contributes some SQL, the last interface in the chain executes the multi statement.

Wednesday Mar 21, 2012

ODI 11g – How to Load Using Partition Exchange

Here we will look at how to load large volumes of data efficiently into the Oracle database using a mixture of CTAS and partition exchange loading. The example we will leverage was posted by Mark Rittman a couple of years back on Interval Partitioning, you can find that posting here. The best thing about ODI is that you can encapsulate all those ‘how to’ blog posts and scripts into templates that can be reused – the templates are of course Knowledge Modules.

The interface design to mimic Mark's posting is shown below;

The IKM I have constructed performs a simple series of steps to perform a CTAS to create the stage table to use in the exchange, then lock the partition (to ensure it exists, it will be created if it doesn’t) then exchange the partition in the target table. You can find the IKM Oracle PEL.xml file here.

The IKM performs the follows steps and is meant to illustrate what can be done;

So when you use the IKM in an interface you configure the options for hints (for parallelism levels etc), initial extent size, next extent size and the partition variable;

  The KM has an option where the name of the partition can be passed in, so if you know the name of the partition then set the variable to the name, if you have interval partitioning you probably don’t know the name, so you can use the FOR clause. In my example I set the variable to use the date value of the source data

FOR (TO_DATE(''01-FEB-2010'',''dd-MON-yyyy''))

Using a variable lets me invoke the scenario many times loading different partitions of the same target table.

Below you can see where this is defined within ODI, I had to double single-quote the strings since this is placed inside the execute immediate tasks in the KM;

Note also this example interface uses the LKM Oracle to Oracle (datapump), so this illustration uses a lot of the high performing Oracle database capabilities – it uses Data Pump to unload, then a CreateTableAsSelect (CTAS) is executed on the external table based on top of the Data Pump export. This table is then exchanged in the target. The IKM and illustrations above are using ODI which was needed to get around some bugs in earlier releases with how the variable is handled...as far as I remember.

Tuesday Feb 28, 2012

ODI 11g – Expert Accelerator for Model Creation

Following on from my post earlier this morning on scripting model and topology creation tonight I thought I’d add a little UI to make those groovy functions a little more palatable. In OWB we have experts for capturing user input, with the groovy console we open up opportunities to build UI around the scripts in a very easy way – even I can do it;-)

After a little googling around I found some useful posts on SwingBuilder, the most useful one that I used for the dialog below was this one here. This dialog captures user input for the technology and context for the model and logical schema etc to be created. You can see there are a variety of interesting controls, and its really easy to do.

The dialog captures the users input, then when OK is pressed I call the functions from the earlier post to create the logical schema (plus all the other objects) and model. The image below shows what was created, you can see the model (with typo in name), the model is Oracle technology and references the logical schema ORACLE_SCOTT (that I named in dialog above), the logical schema is mapped via the GLOBAL context to the data server ORACLE_SCOTT_DEV (that I named in dialog above), and the physical schema used was just the user name that I connected with – so if you wanted a different user the schema name could be added to the dialog.

In a nutshell, one dialog that encapsulates a simpler mechanism for creating a model. You can create your own scripts that use dialogs like this, capture input and process.

You can find the groovy script for this is here odi_create_model.groovy, again I wrapped the user capture code in a groovy function and return the result in a variable and then simply call the createLogicalSchema and createModel functions from the previous posting. The script I supplied above has everything you will need. To execute use Tools->Groovy->Open Script and then execute the green play button on the toolbar.

Have fun.

ODI 11g - Scripting the Model and Topology

Scripting is the ideal mechanism to automate start up and teardown for repeated tasks and those that you just want to automate. Here are a couple of more illustrations of how to easily construct a model in ODI, the script will also create all of the topology objects. The script uses two methods; createLogicalSchema and createModel. The createLogicalSchema creates the logical schema, data server, physical schema and logical schema to physical schema mapping via a context all from one function call.

The signature of these methods looks like this;


contextCode – the ODI code for the context used to map the logical schema to the physical

technologyCode – the ODI code for the technology

nameForLogicalSchema – the name for the logical schema to create

NameForDataserver – the name for the data server to create

userNameForAuthentication – the username for the connection to the data server

passwordForAuthentication – the password for the connection to the data server

urlForAuthentication – the URL for the connection to the data server

driverForAuthentication – the JDBC driver for the connection to the data server

schemaForAuthentication – the schema to use for the ODI physical schema


logicalSchemaObject – the ODI logical schema object (instance of ODILogicalSchema)

contextCode – the ODI context code for reverse engineering

nameForModel – the name for the model to create

codeForModel – the code for the model to create

So with these two methods or variations of them you can easily construct your topology objects and models. For example the call below creates a new model named ORACLE_MODEL and all of the topology objects that will allow me to go straight to reverse engineering when the script has been run.

lschema = createLogicalSchema("GLOBAL", "ORACLE", "ORACLE_EBS", "ORACLE_HQLINUX_DEV", "SCOTT",

    ObfuscatedString.obfuscate("<password>"), "jdbc:oracle:thin:@localhost:1521:orcl", "oracle.jdbc.OracleDriver", "SCOTT")

createModel(lschema, "GLOBAL", "ORACLE_MODEL", "ORACLE_MODEL")

Here is the source code for the script

import oracle.odi.core.persistence.transaction.support.DefaultTransactionDefinition;
import oracle.odi.domain.util.ObfuscatedString;
import oracle.odi.domain.model.OdiModel;
import oracle.odi.domain.topology.OdiLogicalSchema;
import oracle.odi.domain.topology.OdiPhysicalSchema;
import oracle.odi.domain.topology.OdiDataServer;
import oracle.odi.domain.topology.OdiContext;
import oracle.odi.domain.topology.OdiTechnology;
import oracle.odi.domain.topology.OdiContextualSchemaMapping;
import oracle.odi.domain.topology.AbstractOdiDataServer;
import oracle.odi.domain.topology.finder.IOdiContextFinder;
import oracle.odi.domain.topology.finder.IOdiTechnologyFinder;

def createLogicalSchema(contextCode, techCode, schName, dataserverName, userName, password, url, driver, schema) {
  txnDef = new DefaultTransactionDefinition();
  tm = odiInstance.getTransactionManager()
  txnStatus = tm.getTransaction(txnDef)

  contextFinder = (IOdiContextFinder) odiInstance.getTransactionalEntityManager().getFinder(OdiContext.class);
  context = contextFinder.findByCode(contextCode);

  techFinder = (IOdiTechnologyFinder) odiInstance.getTransactionalEntityManager().getFinder(OdiTechnology.class);
  tech = techFinder.findByCode(techCode);

  lschema = new OdiLogicalSchema(tech, schName)
  dserver = new OdiDataServer(tech, dataserverName)
  con = new AbstractOdiDataServer.JdbcSettings(url, driver)
  pschema = new OdiPhysicalSchema(dserver)
  cschema = new OdiContextualSchemaMapping(context, lschema, pschema)

  return lschema

def createModel(lschema, contextCode, modName, modCode) {
  txnDef = new DefaultTransactionDefinition();
  tm = odiInstance.getTransactionManager()
  txnStatus = tm.getTransaction(txnDef)

  contextFinder = (IOdiContextFinder) odiInstance.getTransactionalEntityManager().getFinder(OdiContext.class);
  context = contextFinder.findByCode(contextCode);

  mod = new OdiModel(lschema, modName, modCode)
  return mod

lschema = createLogicalSchema("GLOBAL", "ORACLE", "ORACLE_EBS", "ORACLE_HQLINUX_DEV", "SCOTT", ObfuscatedString.obfuscate("<password>"),
"jdbc:oracle:thin:@localhost:1521:orcl", "oracle.jdbc.OracleDriver", "SCOTT")

createModel(lschema, "GLOBAL", "ORACLE_MODEL", "ORACLE_MODEL")

Have fun scripting!

Wednesday Feb 08, 2012

ODI 11g – More accelerator options

A few more options added into the interface accelerator that I blogged about earlier here in initial post and a later one here. Added options for doing position based and case sensitive/insensitive options. These were simple changes added into the auto map class. You can now find the latest updates below;

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

java –classpath <cp> OdinterfaceAccelerator jdbc:oracle:thin:@localhost:1521:ora112 oracle.jdbc.OracleDriver ODI_MASTER mypwd WORKREP1 SUPERVISOR myodipwd STARTERS SDK <icontrol.csv

In the automapper I created a couple of options that can drive the accelerator, it supports;

  • positional based match (match columns by position from source to target)
  • exact match case sensitive  (match EMPNO with EMPNO, but not empno with EMPNO)
  • exact match case insensitive (match EMPNO with empno)
  • src/target ends with sensitive/insensitive (match PFX_empno with empno/EMPNO)
  • src/target starts with sensitive/insensitive (match empno_col with empno/EMPNO)

Note, you can also use the “diagrams” in the models to greatly accelerate development if source and targets have the same structure – if not then you have to go through the SDK route above if you want to accelerate.

Tuesday Jan 24, 2012

ODI Time Generation – SQL as a Source

Came across a nice use of the earlier SQL as a Source KM posting where the source was a time dimension generator. The forum entry is here, so the temporary interface is a data generator which when nested can be used in an interface to merge or load into a target.

Click on the image to see more …


Nice way to capture within tool and leverage different integration KMs on the target.

Monday Jan 09, 2012

OWB 11gR2 - Dimensional Modeling Paper

At the recent DOAG conference in November 2011, Maren Eschermann from Trivadis presented a session on Dimensional Modeling with OWB 11gR2. Maren has kindly posted an English translation of the presentation and paper, both well worth reading where she provides some valuable opinions on the features and how to get the most from them.

There’s a lot of useful stuff in there, thank you for sharing Maren!

There were a lot of OWB sessions at DOAG, another interesting geographic statistic is from the ‘owbland’ Sourceforge project, if you look at the download statistics there have been 2300 downloads of files from that project in the past 14 months, not bad….good job Oleg. Also interesting statistics on the top countries with downloads, no surprises…Germany and Netherlands!

So quite some activity, if you get time have a read through the paper and presentation on Dimensional Modeling with OWB 11gR2, and thanks again Maren.

Wednesday Dec 14, 2011

OWB 11gR2 – Windows and Linux 64-bit clients on OTN

The OWB clients for Windows and Linux 64-bit clients are now on OTN for download at the link below;


You should now see a line for the Oracle Warehouse Builder 11g Release 2 ( Standalone Software and links for Linux and Windows 64-bit downloads.

The software can also be downloaded from Oracle Support as described in this post.

Thursday Dec 08, 2011

OWB – SQLLoader, big data files and logging

OWB’s flat file loading support is rich and there are a lot of configuration properties for mappings in general and the properties exposed for SQLLoader mappings are expansive. One of the things that OWB does is load the resultant logs from SQLLoader into the runtime audit tables through the runtime service and also scrapes audit information (number of rows etc) from the file.

The thing to be wary of is whether a verbose output is used for feedback – combined with the rows per commit property (default is 200) the SQLLoader mapping will write a feedback message every 200 rows committed. Imagine…big data files and filling a log file with this kind of message every n rows can be quite large!

With this setting your log will have a lot of ‘Commit point reached’ messages in the log.


The ‘Supress’ properties (a typo I know) can be used to hide this information and make the logs compact, so switch on the Supress Feedback property as follows and the log shrinks, with no verbose output;



This equates to the SQLLoader SILENT option, if we look at the control file generated by OWB, we see the SILENT=(FEEDBACK) option is now added;


The tooltip for the misspelled ‘Supress Feedback’ is ‘Suppresses the “commit point reach” messages that normally appear on the screen’. Others have come across this on the forum also.

Wednesday Nov 02, 2011

Parallel Processing with DBMS_PARALLEL_EXECUTE

Here is another illustration of some of the powerful capabilities of the DBMS_PARALLEL_EXECUTE package in the Oracle database, carrying on from the earlier post here. One of the comments from that post was on how to insert into a different named table within each chunk and that insert can perform parallel DML also. This kind of scenario could be interesting for very high end processing, it could be end point target tables or tables that are prepared and then you perform partition exchanges with them or something.

The image below shows a variation on the original post where rather than inserting into a specific partition, you write into a specific table.

Driving the whole process can be your own chunking criteria, the question was how to drive this process from a table using SQL such as ‘select distinct level_key, level_key from chunk_table’ where chunk_table has the level_key and the target table name. For example it could contain the following data;

level_key table_name
1 sales_level1
2 sales_level2
3 sales_level3
4 sales_level4

So the first chunk with level_key 1 will write the results to table sales_level1 etc.

You can use the DBMS_PARALLEL_PACKAGE as follows to create this functionality. The start/end values have to be of data type NUMBER, so you will have to lookup the (target) table name inside your PLSQL block within the statement provided in the run_task call.

This block has the query to determine the chunks .....

   exception when others then null;
   sql_stmt =>'select distinct level_key, level_key from chunk_table', by_rowid => false);

Then the next block will construct the and process the tasks......

     sql_stmt =>'declare
       s varchar2(16000); vstart_id number := :start_id; vend_id number:= :end_id;
       table_name varchar2(30);
         select table_name into table_name from chunk_table where level_key=vstart_id;
         s:=''insert into ''||table_name||'' select /*+ PARALLEL(STG, 8) */ colx from STAGING_TABLE STG
           where level_key =:vstart_id'';
         execute immediate s using vstart_id;
     language_flag => DBMS_SQL.NATIVE, parallel_level => 2 );

The anonymous PLSQL block can be any arbitrary code, you can see the table name of the target is retrieved, the example does a parallel INSERT using the hint PARALLEL(STG,8). Anyway, good to share.

Monday Oct 24, 2011

Example XDB/XML Mapping

The mapping to construct the XML for the example 3-46 in the XDB documentation can be found in the xdb_example_3_46.mdl MDL file. This MDL is for, so you will need at least that version.

The example was described in the earlier post on leveraging XDB, the Oracle doc has changed since that older post and example 3.44 is now 3.46 … so by the time you read this it might be different.

The code can be generated and you can inspect with the SQL in the XDB documentation to see how the different parts have been composed.

The other technique that can be used and described here is the inline view, so you can effectively bury your own SQL in a view that is not deployed in the database but the code is generated inline when used in the mapping.

Both of these illustrations are included in the MDL file mentioned above.

Tuesday Oct 11, 2011

Generating XML with Experts

The leveraging XDB post (here) from a few years ago is one of the most actively read posts, since that was done there have been a few more updates on the expert posted within it. One of the updated areas was in the generation of XML using the Oracle Database using the expert., the areas include supporting generated a single document vs multiple documents and the ability to include/exclude attributes from the content, plus whether to create the attributes as XML properties or XML elements.

A recent query was regarding how the ‘Create XML from objects’ menu option gets created. This is added just by enabling the expert on the ‘Tables’ node in the tree, here we see the sequence of actions to do this in OWB 11gR2, you must first import the expert’s MDL, then add the expert to the tree as follows.

First right click on Tables node and select ‘Maintain Creation Experts Here' (you can add any of your own custom experts to parts of the tree also);


Then in the XML_ETL folder within public experts, enable the CREATE_XML_FROM_OBJECTS expert;


That’s it! Now you can run the expert from the tree. For example now click on the Tables node, you will see the ‘Create XML from objects’ option.


This then runs the expert, the dialog was enhanced to include a ‘Generate Root’ option – this was added so that all generated XML fragments are wrapped in a single element rather than created as XML documents. Using this lets you generate one document like;

<Department name=’ACCOUNTING’/>
<Department name=’RESEARCH’/>

rather than multiple documents like (where Department is the route node);

<Department name=’ACCOUNTING’/>
<Department name=’RESEARCH’/>

So let’s select ‘Generate Root’ and see how it works….


As before we get to enter the name of the pluggable map that gets generated.


We then choose the tables for or document, and order the master to the detail, we will have departments and the employees nested inside the department;


We then can define the element name for the root (because we selected generate root), and the dept and emp tables.


For each table we can then define the XML element/attribute names for the columns also, we can also define whether to exclude attributes, or define an element name for the attribute rather than a property name.


For the EMP XML element details we will exclude the foreign key column DEPTNO, and provide nice business names for the properties.


After this, the pluggable mapping is generated. We can use the table function from the earlier post and the pluggable mapping to write the XML to file, for example we generate the following from the SCOTT schema.


Fairly simple example of leveraging the database along with experts to generate based on some basic inputs from the guided expert.

Wednesday Sep 21, 2011

OWB 11gR2 - MySQL and Data types

Thought I’d write a quick post on data type support for MySQL or any other system type for that matter to show some of the intricacies of why things happen. I was helping someone the other day import some tables from MySQL into OWB and there were some columns not imported – OWB mentioned they were skipped, this was because the platform definition at the time did not have the types expected (what types I hear you cry). The platform did have tinyint defined, strange, MySQL did have the column defined with tinyint(1), so what was wrong? The piece in the middle – the JDBC driver was projecting the column as having BIT datatype. The BIT datatype wasn’t in the MySQL platform’s definition when I first published it on the blog (it is now updated here).

You can retrieve the types supported by a platform using the OMBRETRIEVE PLATFORM command as follows;


On the OTN page for the OWB SDK there is a link to some useful utilities that let you visually see the platform definition including the types, and type mappings. Here is an example of the types for MySQL with the useful type properties captured (this expert was built using a simple java component here).


Some useful pointers to the experts and how things work.

Thursday Sep 15, 2011

Custom Java Activity for XML Loading

Always nice to see consultants post real-world scenarios using Oracle tools and technology. Michael Reitsma has posted an entry here on using custom java activities in process flows for loading large XML into a data warehouse. There is also a useful insight into using the runtime public views for monitoring the activities and seeing the low level errors. More postings from Michael I hope sharing his knowledge and experience, thanks Michael for taking the time!

It's definitely a small world - he used a SAX parser (Saxonica) from Michael Kay who I worked with a long way back in ICL where he was driving a next generation metadata repository...sounds familiar:-) Back then we were using a cool persistent Prolog (Megalog) language from the ECRC.

Tuesday Aug 02, 2011

OWB 11gR2 - Creating Interval Partitions

Designing partitioned tables in OWB is done in the table editor in the partitioned tab, the partitions tab let’s you design and deploy complex partitioning strategies. Here we will see how to define an interval partition (see an example in the Oracle Database VLDB and Partitioning documentation here), we will partition the SALES fact table using a date column (TIMES) in the table below.


On the partitioning tab there is a table with a tree control inside, essentially there are 4 steps for this example; defining the partition type, define the key columns, define the interval expression and the initial partition details. The buttons Add/Add Subpartition/Add Hash Count/Delete get enabled when you select rows, so you can modify the definition.


Generating the code we can see the DDL for the Oracle partitioning clause has been included.


To create the table definition in OMB you can do something like the following – note there is some double quoting in the expressions.

# Plus the rest of your table definition....

That’s it!


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


« July 2016