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.)
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.
- 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.
- Once the load plan is open, then click on the “Steps” tab – which will display all the Load Plan steps.
- Then expand to the step you are interested in and expand
- Then scroll to the right and click on the Session ID
- Once the session opens navigate to Steps
- In this case navigate to the Load Data step and double click
- This will open the Session Task for loadingIn this case I’m interested in the Source Code
- 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
- 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
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