Executing Interfaces and Procedures in parallel in a Package with ODI

Uli Bethke Oracle Data Integrator (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.

Teach me Big Data to Advance my Career

Let’s create the three tables first. Log in to the SH schema via SQL+ and execute the following SQL.
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

odi_procedure14

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).

odi_procedure2

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.

odi_procedure3

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

odi_procedure4

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

odi_procedure4a

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

odi_procedure5

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.

Teach me Big Data to Advance my Career

About the author

Uli Bethke LinkedIn Profile

Uli has 18 years’ hands on experience as a consultant, architect, and manager in the data industry. He frequently speaks at conferences. Uli has architected and delivered data warehouses in Europe, North America, and South East Asia. He is a traveler between the worlds of traditional data warehousing and big data technologies.

Uli is a regular contributor to blogs and books, holds an Oracle ACE award, and chairs the the Hadoop User Group Ireland. He is also a co-founder and VP of the Irish chapter of DAMA, a non for profit global data management organization. He has co-founded the Irish Oracle Big Data User Group.