Forum Stats

  • 3,769,816 Users
  • 2,253,026 Discussions
  • 7,875,216 Comments

Discussions

How to create dependency in three load plans in ODI ?

Mahek Choudhary
Mahek Choudhary Member Posts: 1 Green Ribbon
edited Apr 24, 2021 1:43PM in Data Integrator

Hello,

The load plan Lp1 runs at 14:30 and takes around 140 mins to complete and Load plan Lp2 which runs at 15:00 takes 30 mins to complete are used to load the dimension tables. Sometimes the time varies and hence it is not fixed.

Another load plan Lp3 is used to load the Fact table. The fact table is daily refreshed using the dimension tables updated in the Lp 1 and Lp2.

I have a situation where I would like to add a step that checks if both the Load plans have completed and then trigger Lp3.

Kindly please suggest! How will dependency be created to first check if Lp1 and Lp 2 is completed and then trigger Lp3.

Tagged:

Answers

  • Christyxo
    Christyxo Member Posts: 146 Silver Badge
    edited Apr 26, 2021 10:42AM

    The simplest way would be to have a control table with one row that reflects the status of each load plan.

    Create table tb_etl_control 
    ( 
      param_code varchar2(10 bytes)
    , param_value varchar2(10 bytes
    ) ;
    


    Insert into tb_etl_control 
    (
      param_code
    , param_value
    )
    values
    (
      'Lp1'
    , NULL
    ) ;
    
    Insert into tb_etl_control 
    (
      param_code
    , param_value
    )
    values
    (
      'Lp2'
    , NULL
    ) ;
    
    Insert into tb_etl_control 
    (
      param_code
    , param_value
    )
    values
    (
      'Lp3'
    , NULL
    ) ;
    
    commit ;
    

    At the start of Lp1 you create a procedure that changes the status to running.

    As the final step in Lp1 you create a procedure that changes the status complete.

    For more control you should also have a 3rd procedure that showed an error state. You can make it more dynamic by using <%=odiRef.getLoadPlanInstance("LOAD_PLAN_NAME")%> to get the load plan name rather than writing a procedure for each load plan.

    Merge into tb_etl_control t 
    using 
    ( 
      select 
        '<%=odiRef.getLoadPlanInstance("LOAD_PLAN_NAME")%>'
      , 'running' 
      from 
        dual 
    ) s 
    on 
    (
      t.param_code = s.param_code
    )
    When matched then update 
    set 
      t.param_value = s.param_value ;
    

    You apply the same logic in Lp2. Although it isn't necessary for Lp3 I'd suggest repeating for consistency, but also as a secondary check to avoid running a load plan when the previous is still running or failed.

    In Lp3 you place a package at the very start with either a wait for data component, or a variable with a loop of refresh, evaluate and sleep, that is scanning your control table. Only when you see both load plans are complete will the package move on.

    Select
      count ( * )
    From
      Tb_etl_control 
    Where
      1=1
    and param_code in ('Lp1','Lp2')
    and param_value <> 'complete' ;
    

    evaluate variable = 0 else sleep


    The only issue with this solution is that this loop will continue until the correct criteria is met, even if it's still running until the next day. It's useful as it shows what is happening in the operator, however if you want to apply something that will timeout after a period of time, replace the above package with a package that uses the ODIWaitForData component. You can point that at the control table, and use the same criteria as the variable above. If you use this method you can specify a timeout as well but you won't see the constant checks happening in the operator.