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.