How to Setup Dependencies between Load Plans in Oracle Data Integrator

by Chaithanya Kumar Taduvai

ODI developers usually deal with load plans and mismanaged dependencies between multiple-load plans are typically a cause of data corruption in the system. An ideal Informatica warehouse implementation has multiple execution processes dependent on other execution processes which run automatically without any manual intervention. Here is how we can overcome this issue using the load plan dependency feature of ODI.

Scenario

We need to schedule the two load plans for execution one after the other. And the second load plan has components (tables to be loaded) which are derived based on the first load plan execution.

For example:

• Load Plan 1 is scheduled at 9:00 AM IST and takes 3 hours to complete.
• Load Plan 2 is scheduled at 12:30 PM IST which will be kicked off once the Load Plan 1 is completed.

What If? 

Let’s suppose we have encountered a failure in Load Plan 1 execution which was not monitored. Meanwhile, Load Plan 2 has been kicked off and as Load Plan 2 has dependent objects to be loaded from the execution of Load Plan 1, there will be data discrepancy. Instead, if we setup Load Plan 2 to run in a sequential manner right after the completion of Load Plan 1, we can prevent the data discrepancy issue permanently. Let’s look in to how we can overcome this issue.

Solution Implementation 

Setting up Load Plan 2 to run in a sequential manner right after the completion of Load Plan 1 will help overcome the issue. In ODI, we can implement it using an inbuilt Oracle component in the Package Toolbox. Let’s dive into implementing Load Plan dependencies between the two different load plans.

Load Plan Dependency Setup Process

Create a Load Plan by logging in to the BIACM and navigate to Manage Load Plans under Load Plans Administration

Click the ‘+’ button to select the required fact groups and provide the required details to generate the Load Plan

• Name: Load Plan Name
• Load Plan Type: Load Plan Type needs to be selected (SDE/SIL/PLP/Domain Load)
• Source Instance: Source version of the ERP system

Load Plan Dependency load plan can be found in ODI Studio which has been generated via BIACM in the above step

Scenario Implementation Process 

“DLP_EBS_R1223” load plan (Load Plan 2) shown above needs to be run right after “Load Plan Dependency” load plan (Load Plan 1) execution is completed. This should be done immediately rather than monitoring the load plan, waiting for it to complete and kicking off the next load plan manually. As we need to set the dependency between the two load plans, we will follow the steps as shown in the example below. This example is based on an OBIA environment and can be implemented in standalone ODI as well. 

• Create a Package with “OdiStartLoadPlan” component from the toolbox, provide the required details.

• Load Plan Name: Load Plan Name which is dependent on Load Plan 1 can be selected from the drop-down list
• Context Code: Select the Available Context from the drop-down list
• Agent Code: Select the Agent from the drop-down list
• Oracle Data Integrator User: Select the Supervisor from the drop-down list
• Oracle Data Integrator Password: *******

• Generate Scenario for the package that has been created

• Open the Load Plan Dependency load plan where the Load Plan 2 component needs to be added

Drag and drop the DLP_EBS_R1223 scenario as the final step as shown below and save it:

As we finish setting up the dependency between the Load Plans, once the Load Plan Dependency” reaches the last step, DLP_EBS_R1223 is initiated automatically and the load plan execution begins.

Navigate to the Operator tab and check for the run status of the execution process as shown below:

We hope that this blog post has helped you understand how to setup dependencies between Load Plans in ODI. This will help prevent data discrepancy between the dependent tables present in the load plans. Migrating this to a new environment is similar to individual mappings in any OBIA project. If you have to customize the underlying objects/any reordering of the objects in the dependent load plan, make sure to update the schedule which ensures that the latest code is picked as part of the scheduling process.

Chaithanya Kumar Taduvai is a Team Lead specializing in OBIA, ODI, Informatica and OBIEE with over 8 years of experience in Data Warehousing. He has implemented BI Apps solutions including installations and configurations for Financials, Procurement & Spend, and Supply Chain Management on Oracle EBS source systems.