OBIEE Tips & Tricks: OBIEE11g: How to set Logging Levels for all the Users using a System Session Variable (or) Creating or Configuring or Setting LOGLEVEL system session variable in OBIEE or LOGLEVEL Variable in OBIEE

Recently i got a requirement to add the logging level as 5 for all the users. We can get this done by creating a 'LOGLEVEL' session variable. In this post we will see how to set a common logging level for all the users using LOGLEVEL session variable.

We know that Logging level can be defined for each and every user in the RPD separately. To set the logging level for an individual user we should open the RPD in online mode--> click on Manage-->Identity-->select the particular user and double click on the user then we can see the Logging levels in the General tab-->we can set the logging levels from 0 to 7. The description of the each and every logging level is given below

Logging Levels:

Level 0
No logging.
Level 1
Logs the SQL statement issued from the client application. Also logs the following:

Physical Query Response Time — The time for a query to be processed in the back-end database.
Number of physical queries — The number of queries that are processed by the back-end database.
Cumulative time — The sum of time for all physical queries for a request (that is, the sum of all back-end database processing times and DB-connect times).
DB-Connect time — The time taken to connect to the back-end database.
Query cache processing — The time taken to process the logical query from the cache.
Elapsed time — The time that has elapsed from when the logical query is presented to the BI Server until the result is returned to the user. Elapsed time can never be less than response time, because elapsed time takes into account the small extra time between the logical query being presented to the BI Server to the start of preparation of the query. In cases where this delta time is negligible, the elapsed time equals the response time.
Response time — The time taken for the logical query to prepare, execute, and fetch the last record.
Compilation time — The time taken to compile the logical query.
For each query, logs the query status (success, failure, termination, or timeout), and the user ID, session ID, and request ID.

Level 2
Logs everything logged in Level 1.
Additionally, for each query, logs the repository name, business model name, subject area name, SQL statement issued against the physical database, queries issued against the cache, number of rows returned from each query against a physical database and from queries issued against the cache, and the number of rows returned to the client application. Level 3
Logs everything logged in Level 2.
Additionally, adds a log entry for the logical query plan, when a query that was supposed to seed the cache was not inserted into the cache, when existing cache entries are purged to make room for the current query, and when the attempt to update the exact match hit detector fails. Level 4
Logs everything logged in Level 3.
Additionally, logs the query execution plan. Level 5
Logs everything logged in Level 4.
Additionally, logs intermediate row counts at various points in the execution plan. Level 6 and 7
For future purpose

About Session Variables:

System session variables are session variables that the Oracle BI Server and Oracle BI Presentation Services use for specific purposes. System session variables have reserved names, that cannot be used for other kinds of variables (such as static or dynamic repository variables and non-system session variables).
System session variables obtain their values from initialization blocks.
Every active BI Server session generates session variables and initializes them. Each session variable instance can be initialized to a different value.

LOGLEVEL Session Variable:

The value of LOGLEVEL (a number between 0 and 5) determines the Logging level that the Oracle BI Server will use for user queries. This system session variable overrides a variable defined in the Users object. If the Administrators Users object has a Logging level defined as 4 and the session variable LOGLEVEL defined in the repository has a value of 0 (zero), the value of 0 applies.

Creation of LOGLEVEL Session Variable:

Step 1: Open the RPD in Online or Offline mode (I have done this complete exercise in online mode only)

Go to Manage-->Variables-->Click on System in the left side--> Just do right click on the right side empty space--> from the list of the session variables select 'New LOGLEVEL' as shown in the below screenshot..

Step 2: In the Name enter as LOGLEVEL (case sensitive) and check in the option Enable any user to set the value as shown in the below screenshot

Click on New button of the initialization block as shown below

Give the initialization block name as you like in my case it is LOG_LEVEL and click on Edit Data Source button as shown below

Now select the radio button which says Use OBIEE Server as shown below

Now write the sql query (Select 5 from Sales) as shown in the below screenshot. As i am using the OBIEE server i referred the table from the RPD only . The table (Sales) which i used in the query is in the RPD. You can use any table from the RPD to create this sql query.

Click on Test. You can see LOGLEVEL variable value as 5 as shown in the below screenshot

Again click on Test button as shown in the below screenshot. You can get the below screenshot.

As it is a system defined (System reserved) variable one pop up will be displayed with a warning message as shown in the below screenshot, Just click on Yes

Now set the default value as 5 as shown in the below screenshot and click on Ok

Step 3: for testing purpose select few users and look at their logging level (It can be done only in the online mode) . Manage -->Identity-->Action-->Set Online User Filter--> give the value as * and hit Enter or click on Ok.

In the below screenshot we can see the logging level for the weblogic user is set to 0.

In the below screenshot we can see the logging level for the Nagarjuna user is set to 0.

Now do the consistency check of the RPD and Save it. If any errors are there then we need to fix then before saving the RPD.

Step 4:Now log in to the analytics using weblogic user and create one sample report or add any columns to the criteria tab and click on Results. You can see the results of the columns selected as shown in the below screenshot

Click on Administration -->Manage Sessions--> Click on View log of any session query as shown in the below screenshot.

Now you can see the query log file even though the logging level of the particular user is set to 0.How it is getting it means the default logging level (0)of the user is overwritten by the LOGLEVEL variable value (5).

Do sign out once and Now log in to the analytics using Nagarjuna user and create one sample report or add any columns to the criteria tab and click on Results. You can see the results of the columns selected as shown in the below screenshot