Oracle Business Intelligence Applications

Home› Oracle Analytics› Oracle Analytics Forums› More Solutions› Oracle Business Intelligence Applications

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Query for OBIA 11g Load Plan details

Received Response
49
Views
6
Comments
Nagadasari
Nagadasari Rank 2 - Community Beginner
in Oracle Business Intelligence Applications

Hi Friends,

   We are running multiple load plans in OBIA 11g. Now my customer want to see their no.of load plans, start date, end date, duration in hh:mm:ss format. Can anyone please give me query with metadata table names.

Thanks in advance.

Raghu Nagadasari

Answers

  • Charles M
    Charles M Rank 6 - Analytics Lead

    Hi Raghu,

    Try this (use a connection to your ODI repository):

    alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

    select * from {your_odi_repository}.snp_lpi_run order by start_date desc;

    Let me know if this is what you need.

    Regards,

    Charles

  • Charles M
    Charles M Rank 6 - Analytics Lead

    Default duration is in seconds. You can make these modifications to convert that into minutes or hours:

    -- "Duration in Minutes"
    select I_LP_INST, NB_RUN, START_DATE, END_DATE, round((duration/60),2) as "Duration in Minutes" from {your_odi_repository}.snp_lpi_run order by start_date desc;

    -- "Duration in Hours"

    select I_LP_INST, NB_RUN, START_DATE, END_DATE, round(((duration/60)/60),2) as "Duration in Hours" from {your_odi_repository}.snp_lpi_run order by start_date desc;

    Regards,

    Charles

  • Charles M
    Charles M Rank 6 - Analytics Lead

    Here are some of the repository tables that relate to load plans (based on 11.1.1.7, but should also work for 11.1.1.9):

    SNP_LP_INSTLoad Plan instance. When a load plan starts, this table acts as the running instance of this load plan
    SNP_LP_STEPLoad Plan steps hierarchy
    SNP_LP_STEP_VARLoad plan variables defined for a step
    SNP_LP_VARLoad plan variables
    SNP_LPI_EXC_LOGLoad plan instance exception log per run
    SNP_LPI_EC__VAR_LOGLoad plan instance exception variable log per run
    SNP_LPI_RUNLoad Plan instance runs. Every time an attempt is made to re-start a load plan, data is captured here
    SNP_LPI_STEPLoad plan instance steps hierarchy
    SNP_LPI_STEP_LOGLoad Plan instance step logs per run
    SNP_LPI_STEP_VARLoad plan instance variables defined for a step
    SNP_LPI_TXT_LOGLoad plan instance variables SQL text log per run [DEPRECATED]
    SNP_LPI_VARLoad plan instance variables
    SNP_LPI_VAR_LOGLoad plan instance variables log per run
  • Nagadasari
    Nagadasari Rank 2 - Community Beginner

    Thanks Charles M for you prompt response. We got below query from my friend.

    Execute the below Query in the ODIREPO Schema of the respective instance:

    SELECT --SSF.SCEN_FOLDER_NAME,

      SLP.I_LOAD_PLAN,

      SLP.LOAD_PLAN_NAME,

      RT.I_LP_INST,

      RT.NO_OF_RUNS,

      RT.START_TIME,

      RT.END_TIME,

      RT.total_duration

    FROM SNP_LOAD_PLAN SLP,

      SNP_SCEN_FOLDER SSF,

    SNP_LP_INST SLI,

      (SELECT i_lp_inst,

        MAX(NB_RUN) No_of_Runs,

        MIN(TO_CHAR(start_date, 'DD-MON-YYYY HH24:MI:SS')) start_time,

        MIN(START_date) START_TIME_DT,

        MAX(TO_CHAR(end_date, 'DD-MON-YYYY HH24:MI:SS')) end_time,

    TO_CHAR(to_date(SUM(duration),'sssss'),'hh24:mi:ss') total_duration

      FROM SNP_LPI_RUN

      GROUP BY i_lp_inst

      ) RT

    WHERE SLP.I_SCEN_FOLDER  =SSF.I_SCEN_FOLDER

    AND SLP.I_LOAD_PLAN      = SLI.I_LOAD_PLAN

    AND SLI.i_lp_inst = RT.i_lp_inst

    AND SLP.FIRST_USER      IN ('biappsadmin')

    AND SSF.SCEN_FOLDER_NAME = 'Generated Load Plans'

    ORDER BY --SLP.I_LOAD_PLAN , RT.i_lp_inst

      RT.START_TIME_DT;

    Thank you Ravi for the detailed query.

    Thanks,

    Raghu Nagadasari

  • John_K
    John_K Rank 5 - Community Champion

    As this is customer facing, have you thought about putting this in a small app on the ODI server? I started something a while back but never got around to finishing it - http://wegobeyond.co.uk/blog/entry/monitoring-oracle-data-integrator-odi-load-plan-executions-with-bi-apps.html. You can make it quite powerful though - allow users to drill into different load plan executions, analyse long running jobs, jobs whose time has increased consistently, historic failures etc.

  • Nagadasari
    Nagadasari Rank 2 - Community Beginner

    Thanks ORA-01033/John Keymer for the information. Could you please share the app to raghu.nagadasari@gmail.com so that we can try to implement this. Thanks again.