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.
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:
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.
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.
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
Load Plan Dependency load plan can be found in ODI Studio which has been generated via BIACM in the above step
“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.
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. |