1. Oracle Data Integrator Quick Reference Guide
  2. Load Plans

15 Load Plans

Learn about load plans in Oracle Data Integrator and how to create them.

Topics

What are Load Plans

Understand what an Oracle Data Integrator load plan is.

A load plan is an executable object in Oracle Data Integrator (ODI) that contains a hierarchy of steps. The steps can be executed conditionally, in parallel, or in series. The leaves of this hierarchy are scenarios. The set of scenarios that makes up a load plan can involve the execution of packages, mappings, variables, and procedures. Load plans also support exception handling strategies for scenarios that end in error.

Load plans can be started, stopped, and restarted from the command line, from Oracle Data Integrator Studio, Oracle Data Integrator Console, or a web service interface. They can also be scheduled by using the run-time agent’s built-in scheduler or by using an external scheduler. When a load plan is executed, a load plan instance is created. Each attempt to run this load plan instance is a separate load plan run.

A load plan can be modified in production environments and steps can be enabled or disabled according to production needs. Load plan objects can be designed and viewed in the Designer and Operator Navigators. Various design operations (such as create, edit, delete, and so forth) can be performed on a load plan object if a user connects to a development Work Repository, but some design operations are not be available in an execution work repository. After it is created, a load plan is stored in the work repository. The load plan can be exported, and then imported to another repository and executed in different contexts.

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

The step types are:
  • Serial - This defines a serial execution of its child steps. Child steps are ordered and a child step is executed only when the previous one is terminated.
  • Parallel - This defines a parallel execution of its child steps. Child steps are started immediately in their order of priority.
  • Run Scenario - This launches the execution of a scenario.
  • Case - The combination of these steps allows conditional branching based on the value of a variable.
  • When - If you have several When steps under a Case step, only the first enabled When step that satisfies the condition is executed.
  • Else - If no When step satisfies the condition or the Case step does not contain any When steps, the Else step is executed.

Load plan instances and load plan runs are similar to sessions. The difference is that when a session is restarted, the existing session is overwritten by the new execution. The new load plan run does not overwrite the existing load plan run; it's added after the previous load plan runs for this load plan instance. The load plan instance cannot be modified at run time.

A scenario is an atomic run time component that is given to production. The production administrator can orchestrate the various scenarios by using load plans in order to optimize the time taken to execute the ETL jobs in the given time frame and ease the administration of all the ETL jobs.

See Introduction to Load Plans in Developing Integration Projects with Oracle Data Integrator.

See Running Load Plans in Developing Integration Projects with Oracle Data Integrator.

See Using Load Plans in Production in Developing Integration Projects with Oracle Data Integrator.

See Executing a Load Plan in Administering Oracle Data Integrator.

Create Load Plans

Learn the steps to create load plans in Oracle Data Integrator.

To create a simple load plan in ODI that executes two steps in parallel (in this example we load two dimensions, PRODUCT and TIME):
  1. Go to the Designer Navigator and click the Load Plans and Scenarios tab. From the tab’s menu, select New Load Plan.
  2. Enter a suitable Name.
  3. Click the Steps tab.

    The load plan editor opens with the root step node.

  4. Add ODI objects to the load plan as parallel steps. Click Add Step (the green plus), and select Parallel Step. The Parallel step node appears under root_step.
  5. Define two new procedures and place them under the parallel node so that they execute in parallel.

    Note that the default restart type for Parallel steps is “Restart all children,” and the restart type for root_step is “Restart from failure.”

  6. Save your load plan.
  7. Define two procedures to load dimensions, PRODUCT and TIME, and add them to the load plan Parallel step node.
    1. On the Projects tab of the Designer Navigator, expand the project and folder. Right-click Procedures and select New Procedure.
    2. Enter a suitable name for the procedure to load the PRODUCT dimension. Update the other fields as appropriate for your environment. Click Save and close the editing tab.
    3. Repeat the previous two steps to create and save the procedure to load the TIME dimension.
    4. Return to the Data Warehouse Load Plan editor. With the Parallel step highlighted, click Add Step (green plus) and select Run Scenario Step.
    5. Select an existing scenario for a step, or choose other objects (packages, interfaces, variables, and procedures), for which ODI automatically generates a scenario to run the step. You now add the two procedures that you just defined as steps under the Parallel node of the load plan. On the Add Run Scenario Step screen, click Lookup Scenario (magnifying glass).
    6. Select Procedure as the Executable Object Type. Select the procedure to load the PRODUCT dimension. Click OK.
    7. Go to the New Scenario window, and add a name for the scenario so that ODI can create it to run this procedure, and click OK.

      ODI returns to the Add Run Scenario Step window. By default, the name of the new scenario that ODI generates for the procedure, and the step name that you are adding to the load plan are the same as the procedure name.

    8. Click Finish.

      The first of two parallel steps appears.

    9. Click the Parallel node of the Steps Hierarchy and click Add Step (the green plus sign) again. Select Run Scenario Step.
    10. Repeat steps f to h to add your other procedure to load the TIME dimension to the Parallel node of the Steps Hierarchy.
To create a simple load plan to execute two steps serially:
  1. Go to the Designer Navigator, click the Load Plans and Scenarios tab. From the tab’s menu, select New Load Plan.
  2. Enter a suitable Name.
  3. Click the Steps tab.

    The load plan editor opens with the root step node.

  4. Add ODI objects to the load plan as serial steps.
    1. Click Add Step (the green plus), and select Serial Step. The Serial node appears under root_step.
    2. With Serial node selected, click Add Step and select Run Scenario Step.
    3. Click Lookup Scenario in the Add Run Scenario Step window.
    4. Select Procedure as the Executable Object Type. Select the procedure name. Click OK. In the New Scenario window, accept the default as the name of the scenario so that ODI creates it to run this procedure, and click OK.

      ODI returns to the Add Run Scenario Step window.

    5. Click Finish.

      The first serial step appears.

    6. Click the Serial node of the Steps Hierarchy, and click Add Step again. Select Run Scenario Step.
    7. Click Lookup Scenario, and select Mappings.
    8. Select the mapping name and click OK. In the New Scenario window, accept the default as the name of the scenario so that ODI can create it to run this mapping, and click OK.

      ODI returns to the Add Run Scenario Step window.

    9. Click Finish.
To create a simple load plan to execute two steps in parallel and to execute five steps serially (this example includes two procedures to load dimensions, TIME and PRODUCT):
  1. Go to the Designer Navigator, click the Load Plans and Scenarios tab. From the tab’s menu, select New Load Plan.
  2. Enter a suitable Name.
  3. Click the Steps tab.

    The load plan editor opens with the root step node.

  4. Add ODI objects to the load plan as parallel steps. Click Add Step (the green plus), and select Parallel Step. The Parallel step node appears under root_step.
  5. You define two new procedures and place them under the parallel node so that they execute in parallel.

    Note that the default restart type for Parallel steps is “Restart all children,” and the restart type for root_step is “Restart from failure.”

  6. Click Save to save your load plan.
  7. Define two procedures to load dimensions, PRODUCT and TIME, and add them to the load plan Parallel step node.
    1. Go to the Projects tab of the Designer Navigator and expand the HandsOnLoads > HandsOn folder. Right-click Procedures and select New Procedure.
    2. Enter a suitable name for the procedure to load the PRODUCT dimension. Update the other fields as appropriate for your environment. Click Save and close the editing tab.
    3. Repeat the previous two steps to create and save the procedure to load the TIME dimension.
    4. Return to the Data Warehouse Load Plan editor. With the Parallel step highlighted, click Add Step (green plus) and select Run Scenario Step.
    5. Select an existing scenario for a step, or choose other objects (packages, interfaces, variables, and procedures), for which ODI automatically generates a scenario to run the step. Add the two procedures that you just defined as steps under the Parallel node of the load plan. On the Add Run Scenario Step screen, click Lookup Scenario (magnifying glass).
    6. Select Procedure as the Executable Object Type. Select the procedure to load the product dimension. Click OK.
    7. Go to the New Scenario window and add a name for the scenario that ODI creates to run this procedure. Click OK.

      ODI returns to the Add Run Scenario Step window. By default, the name of the new scenario that ODI generates for the procedure, and the step name that you added to the load plan are the same as the procedure name.

    8. Click Finish.

      The first of two parallel steps appears.

    9. Click the Parallel node of the Steps Hierarchy, and click Add Step (the green plus sign) again. Select Run Scenario Step.
    10. Repeat steps e to h to add your other procedure to load the time dimension to the Parallel node of the Steps Hierarchy.
  8. Add ODI objects to the load plan as serial steps.
    1. Go to the Steps Hierarchy, and highlight root_step. Click Add Step (the green plus sign) and select Serial Step.

      The Serial node appears in the Steps Hierarchy.

    2. Select the Serial node, click Add Step and select Run Scenario Step.
    3. Go to the Add Run Scenario Step window, and click Lookup Scenario.
    4. Select Procedure as the Executable Object Type. Select the procedure name. Click OK. In the New Scenario window, accept the default as the name of the scenario that ODI can create it to run this procedure, and click OK.

      ODI returns to the Add Run Scenario Step window.

    5. Click Finish.

      The first serial step appears.

    6. Click the Serial node of the Steps Hierarchy, and click Add Step again. Select Run Scenario Step.
    7. Click Lookup Scenario, and select Mappings.
    8. Select the mapping name and click OK. In the New Scenario window, accept the default as the name of the scenario so that ODI can create it to run this mapping, and click OK.

      ODI returns to the Add Run Scenario Step window.

    9. Click Finish.
    10. Repeat the previous four steps to add the remaining serial mappings.
  9. Execute the load plan and examine the list of executed steps in the Operator Navigator.
    1. Click the green arrow to execute the load plan. Click OK in the Start Load Plan window, and then click OK when the “Load Plan started” message appears.
    2. Open the Operator Navigator, click Refresh, and examine the results of executing the load plan.

      The steps in the load plan appear as a grouping of executions within seconds of each other. For each load plan step, ODI generated a scenario for execution. You can see that steps running in parallel.

See Creating a Load Plan Developing Integration Projects with Oracle Data Integrator.