April, 2009

Executing Interfaces and Procedures in parallel in a Package with ODI

I have been working on Oracle Data Integrator (ODI) over the last three weeks. It is an extremely flexible and innovative tool. Claims by Oracle that it is up to ten times faster to develop ETL routines in ODI as compared to traditional ETL tools are actually no exaggeration.

In this article I will show you how to execute Procedures and Interfaces in parallel in a Package. I spent about half a day in the forums etc. before I actually figured out how to set this up. The way you execute Mappings or Transformations in OWB sent me in the wrong direction. While in OWB you use a Fork and AND Operator to accomplish this, in ODI you create a Scenario from your Interfaces and/or Procedures, and connect these inside a Package. Then you just set the Snychronous/Asynchronous parameter in those Scenarios that you want to execute in parallel to Asynchronous Mode.

We’ll now go through each of the steps. We will first create three tables in the SH sample schema. We will then create three Procedures that will load these tables. Finally we will create a Package from the three Procedures that will load two of them in parallel.

Note: Instead of the Procedures we could have used Interfaces. It is the same principle. However, it is easier and quicker to set up and demonstrate the parallel load with Procedures.

Let’s create the three tables first. Log in to the SH schema via SQL+ and execute the following SQL.

SQL> create table a (a number);

Table created.

SQL> create table b (b number);

Table created.

SQL> create table c (c number);

Table created.

Next we create the Procedures.

Note: I have already created all of the Topologies, Models, Contexts etc. that are pre-requisites for creating the Procedures. Have a look at John Goodwin’s excellent series of articles on ODI for some tutorials.

Log on to Designer. Expand your Project and Folder, right click Procedures and select Insert Procedure.

Populate fields at the Definition tab as per screenshot below.

Name: insert a
Target Technology: Oracle


Change to the Details tab and click on the Add button

Populate the fields as per screenshot below

Name: insert a
Log Counter: Insert
Schema: ORCL_SH. This could be different in your environment. Depending on the name given to the schema in the Topology Manager.
Command: insert into a values (7).


In this Procedure we populate the table a in the SH schema with the value 7.

Next we create the Procedure that populates table b. Go through the same steps as for table a. As the Command type in: insert into b select prod_id from sales.

Next we create the Procedure that populates table c. Go through the same steps as for table a and b. Populate the Command with: insert into c values (8).

Next we need to create a Scenario for each of our Procedures.

Right click the insert a Procedure and from the menu select Generate Scenario…

Leave the default values and click OK.


Repeat the same steps for the other two Procedures.

Next we create the Package. Right click Packages and select Insert Package from the menu.

Populate the Name field as per screenshot


Change to the Diagram tab and drag the following items onto the diagram.

Scenario_A Version 001
Scenario_B Version 001
Scenario_C Version 001
ODIWaitForChildSession tool from the Event Detection toolbox

Next right click the Execution of the Scenario Insert_A item and select First Step from the menu

Next connect the items as per screenshot below


We want to execute Scenario Insert_B and Insert_A in parallel, so we need to set the Synchronous/Asynchronous parameter to Asynchronous Mode for these Scenarios.

Click on the Execution of the Scenario Insert_B. In the Properties area set the Synchronous/Asynchronous parameter to Asynchronous Mode


Do the same for Execution of the Scenario Insert_C.

That’s it. You can now execute the Package and then verify in the Operator that Scenario Insert_B and Insert_C actually executed in parallel.

Need ODI Training? Learn ODI from the experts!

I am proud to announce that Sonra are now offering ODI classes.

Value-priced. Customised. Onsite-Offsite. Online.

Five day standard training course.
Starts 24 February 2014.
Book before 15 January 2014. Pay $949.

More information on our ODI courses.

OUG Ireland BI Special Interest Group launched

At the OUG Ireland 2009 we launched the OUG Ireland Business Intelligence Special Interest Group. All OUG Ireland members are welcome to join. Tony Cassidy from Bearing Point and me will co-chair the SIG.

We will hold our first meeting in June or July 09. I will give a presentation on Oracle Data Integrator and how it favourably compares to Oracle Warehouse Builder. This will be followed by a hands on tutorial on how to develop Knowledge Modules in ODI Designer. We will also try and organise a session on a more business orientated topic.

Query hints in OBIEE

I have recently come across a post on the OTN forums on how to use hints in OBIEE. OBIEE lets you specify hints in the physical layer on a table by table basis. The problem of the user in the forum was how it is possible to hint the alias of a table in the SQL generated by Answers. This is relatively straightforward and I will show you how this can be done in a minute. However, the bigger issue around usage of hints in OBIEE is that it is extremely inflexible. In Business Objects you can use a workaround to use hints on a query by query basis. This same workaround does not work in OBIEE and use of variables did not work either. But let’s first have a look at how we can use hints with aliased tables before we have a look at why OBIEE is very inflexible when it comes to query hints.

We have the following scenario: We want to use dynamic sampling on a table either because we have stale statistics or we want to filter multiple columsn on the table. If you want to find out more about dynamic sampling have a look at Tom Kyte’s great article on dynamic sampling in Oracle magazine.

In order to find the alias that OBIEE will use for the physical table in BI Administrator go to Tools > Query Repository. For name type in the name of your table and Physical Table as type (as per screenshot below)


This will return the internal ID of this table. In the screenshot this is 3001:210. We are interested in the part after the colon. This piece is the alias that OBIEE will give this table in Answers. It will prefix it with a T.

So if we want to add a dynamic sampling hint to the products table we need to add the following to the product table’s hint field : dynamic_sampling(T210,3)


This will then generate SQL as follows:


As you can see, our hint was applied to the query.

However, the whole thing is terribly inflexible. It will add this hint to each query that you generate via answers that involves the products table.

I have tried various workarounds to make this more flexible, unfortunately without any succcess. To get this to work in Business Objects you create the equivalent of a logical column and populate it with /*+ dynamic_sampling(T210,3) */ ”. This will throw a parse error but it will still allow you to add it as a column to the equivalent of the presentation layer. When you create an ad hoc query you have to add this column as the first field to your ad hoc report. This will then generate SQL similar to this:

   /*+ dynamic_sampling(T210,3) */ '' as col1,
   'whatever' as col2
   products T210

When you try to do the same in OBIEE it will throw a parse error and won’t allow you to proceed. I have also tried to create the hint as a variable and then reference this in the hint field of the physical table. Again without success.


When Answers generated the SQL it just took the literal value of VALUEOF(hinter) without evaluating the hinter variable and put the phrase in as the hint for the query.

If anyone has a neat solution for more flexible hinting in OBIEE please let me know.

You may also be interested in the OBIEE book Oracle BI Enterprise Edition Dashboard & Report Best Practices. This is currently the only published OBIEE book on the market.