How to set the log level from a Session variable Initialization block
As we know it is normal to set the log level non-zero for a particular user when we wish to debug problems. However sometimes it is inconvenient to go into each user’s properties in the
Admin tool and update the log level. So I am showing a method which allows the log level to be set for all users via a session initialization block. This is particularly useful for anyone wanting an alternative way to set the log level.
The screen shots shown are using the OBIEE 11g SampleApp demo but are applicable
to any environment.
Open the appropriate rpd in on-line mode and navigate to Manage Variables.
Select Session Initialization Blocks, right click in the white space and create a New Initialization Block.
I called the Initialization block Set_Loglevel .
Now click on ‘Edit Data Source’ to enter the SQL.
Chose the ‘Use OBI EE Server’ option for the SQL. This means that
the SQL provided must use tables which have been defined in the Physical layer
of the RPD, and whilst there is no need to provide a connection pool you must
work in On-Line mode.
The SQL can access any of the RPD tables and is purely used to return a value
of 2. The ‘Test’ button confirms that the SQL is valid.
Next, click on the ‘Edit Data Target’ button to add the LOGLEVEL
variable to the initialization block.
Check the ‘Enable any user to set the value’ option so that this
will work for any user.
Click OK and the following message will display as LOGLEVEL is a system session
variable:
Click ‘Yes’.
Click ‘OK’ to save the Initialization block. Then check in the
On-LIne changes.
To test that LOGLEVEL has been set, log in to OBIEE using an administrative
login (e.g. weblogic) and reload server metadata, either from the Analysis
editor or from Administration > Reload Files and Metadata link. Run a query
then navigate to Administration > Manage Sessions and click ‘View
Log’ for the query just issued (which should be approximately the last
in the list). A log file should exist and with LOGLEVEL set to 2 should include
both logical and physical sql. If more diagnostic information is required then
set LOGLEVEL to a higher value.
If logging is required only for a particular analysis then an alternative
method can be used directly from the Analysis editor.
Edit the analysis for which debugging is required and click on the Advanced
tab. Scroll down to the Advanced SQL clauses section and enter the following
in the Prefix box:
SET VARIABLE LOGLEVEL = 2;
Click the ‘Apply SQL’ button.
The SET VARIABLE statement will now prefix the Analysis’s logical SQL.
So that any time this analysis is run it will produce a log.
You can find information about training for Oracle BI EE products here or
in the OU Learning Paths.
Please send me an email at
[email protected] if you have any further
questions.
About the Author:
Gerry Langton started at Siebel Systems in 1999 working as a technical instructor
teaching both Siebel application development and also Siebel Analytics (which
subsequently became Oracle BI EE). From 2006 Gerry has worked as Senior Principal
Instructor within Oracle University specialising in Oracle BI EE, Oracle BI
Publisher and Oracle Data Warehouse development for BI.
