by Varun Reddy

Oracle Data Integrator (ODI): Executing A Load Plan

Load Plan 

A Load Plan is an executable object in Oracle Data Integrator that can contain a hierarchy of steps that can be executed conditionally, in parallel or in series. Procedures, interfaces, packages, variables, and can be added to Load Plans for executions in the form of scenarios in a Load Plan.

An ODI Load Plan is made up of a sequence of several types of steps. Each step can contain several small steps. Depending on the step type, the steps can be executed conditionally, in parallel, or sequentially. By default, an ODI Load Plan contains an empty root serial step. This root step is mandatory and the step type cannot be changed.

Creating a Load Plan 

ODI Load Plans appear in both the Designer and Operator Navigator. ODI Load Plans are available for edition in a development and production repositories, and can be organized into scenario folders.

"Right Click" and select "New" (Load Plan). Then specify a "Name" for the Load Plan.

Load Plan definition takes place on the "Steps Tab." You can define a hierarchy of steps in sequence/parallel conditionally based on the values of variables.

Data Warehouse Load Plan Example:

• Dimensions are loaded in parallel. This includes the LOAD_TIME_DIM, LOAD_PRODUCT_DIM, LOAD_CUSTOMER_DIM scenarios, the geographical dimension and depending on the value of the ODI_VAR_SESS1 variable, the CUST_NORTH or CUST_SOUTH scenario.

• The geographical dimension consists of a sequence of three scenarios (LOAD_GEO_ZONE_DIM, LOAD_COUNTRIES_DIM, LOAD_CITIES_DIM).

• After the dimensions are loaded, the two fact tables are loaded in parallel (LOAD_SALES_FACT and LOAD_MARKETING_FACT scenarios). 

Running a Load Plan

Run the ODI Load Plan by clicking the "Execute" button. The load plan running will be shown in the "Load Plan Executions" according to the Operator. Steps taken along with their status and statistics are shown in the "Steps Tab."  This tab reflects the executions in-progress and can be refreshed.

The sessions started by the load plan will still appear in the "Session's" list. However, the "Steps Tab" is more useful to monitor the overall execution of these sessions.

Like 'Scenarios', ODI Load Plans can also be started from a command line or a web service interface. They can be scheduled using external scheduling or the built-in scheduler.

Load Plans require a JEE or Standalone agent for running. They cannot run within the Studio Local Agent. This is because the ODI Load Plan execution flow is distributed across the agents running the sessions started from the ODI Load Plan. Using this architecture, there is no single technical failure point that may prevent a load plan from proceeding its execution flow when the execution takes place on multiple agents.

Varun Reddy Patrapalle is a Consulting Manager at KPI Partners. His areas of expertise include data warehousing, Oracle Business Intelligence, and Informatica.  His clients have included Fortune 500 companies within finance, retail, healthcare, education, and high-tech . Check out Varun's blog at KPIPartners.com.