Oracle Data Integrator (ODI) Load Parameters for Oracle Business Intelligence Applications (OBIA)

May 22, 2015

Oracle Business Intelligence Applications (OBIA) use substitution variables which control the way data is loaded.  These variables must always be configured during an implementation of BI Applications.  The most important of these are the extract dates – which control the timeframe of data to be loaded into the system.

During the initial phases of a BI Apps project you would never do a full load of data into the warehouse, instead 1-3 months of data is usually acceptable.  Each BI Apps module uses different variables to control the timeframe of data being loaded.  For instance, there are a total of nine variables that control the extraction of the HR Analytics module!

INITIAL_EXTRACT_DATE (Note:  Common to all BI Apps, controls the overall timeframe if no specific variable is defined.)
HR_ABSENCE_EXTRACT_DATE
HR_ACCRUAL_EXTRACT_DATE
HR_WRKFC_EXTRACT_DATE
HR_PAYROLL_EXTRACT_DATE
HR_RCRTMNT_EXTRACT_DATE
HR_TIMELABOR_EXTRACT_DATE
HR_WRKFC_SNAPSHOT_HIST_EXTRACT_DATE
HR_WRKFC_SNAPSHOT_DT 

Often times, during the debugging of an executed load plan, it is important to lookup the value of these parameters in order to test the SQL.   For instance, let’s say your load plan has failed on a specific step and you want to investigate the SQL behind that step.

  1. Open the executed Load Plan by navigating to the ODI Operator > Load Plan Executions > All Executions and then double click on the load plan in question.  This will load the Load Plan on the right-hand side of the ODI user interface.
    JM 1
  2. Once the load plan is open, then click on the “Steps” tab – which will display all the Load Plan steps.
    JM 2
  3. Then expand to the step you are interested in and expand
    JM 3
  4. Then  scroll to the right and click on the Session ID
    JM 4
  5. Once the session opens navigate to Steps
    JM 5
  6. In this case navigate to the Load Data step and double click
    JM 6
  7. This will open the Session Task for loadingIn this case I’m interested in the Source Code
    JM 7
  8. You will then copy the SQL from this window and past it into your favorite SQL tool, in my case I’m using Oracle SQL Developer
    JM 8
  9. Finally, you will need to run find over the SQL code for anything that begins with “#BIAPPS”

In my case I have the following:

——————-
AND ( PAY_PAYROLL_ACTIONS.CREATION_DATE >= TO_DATE(SUBSTR(‘#BIAPPS.HR_PAYROLL_EXTRACT_DATE‘,0,19),’YYYY-MM-DD HH24:MI:SS’) )
——————-

You will need to look-up the parameter in order to know what substitution variable to put in place:

If you setup OBIA properly you would have set these variables in the BIACM.

Which is here: http://servername:9704/biacm/

1- Goto Data Load Parameters Administration
JM 9

2- Search on the parameter “Code”

3- Click the parameter code and look at the child record underneath

4- In this case 3/1/15

In this case I will then backspace over #BIAPPS.HR_PAYROLL_EXTRACT_DATE and replace it with 2015-03-01.

Now you can run the SQL and investigate further.

We hope you found this helpful!

Author: John McGale, Performance Architects


© Performance Architects, Inc. and Performance Architects Blog, 2006 - present. Unauthorized use and/or duplication of this material without express and written permission from this blog's author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Performance Architects, Inc. and Performance Architects Blog with appropriate and specific direction to the original content.

One thought on “Oracle Data Integrator (ODI) Load Parameters for Oracle Business Intelligence Applications (OBIA)

  1. sm.saad50

    Dear John McGale,
    This is a great article.
    I am working on Financial Analytics, Which parameters I will have to set?

    Thank you

    Muhammad Saad

Leave a Reply