Setting LOGLEVEL from Answers in OBIEE

Run an analysis?? Want to see the SQL / Query Log generated by OBIEE server??

Here you can get trick to temporarily enable the logging level. Then you can be able to see the query log.

Normally, we check query in NQQuery.log file or from Answers also we can check the query directly.

If it’s directly from Answers (go to Administration > Manage Sessions.

If we are unable to see the log file or if we find any error message saying that, No Log found while trying to watch the query in log.

Here, the first thing you need to check is LOG LEVEL. When creating rpd, by default log level takes 0 values for Administrator user.

Using Administration Tool,

• Manage > Identity
• Double-click  the User for which you would like to set the Log Level.
• Set it to 2 or more level accordingly, such that, the query will be appeared.

You can get the detail of available Log Levels in OBIEE at the below link,
http://obieenow.blogspot.in/2014/02/different-logging-levels-in-obiee-11g.html

Using Answers, (This is temporarily, for that current session only)

• Stop your BI Server service
• Delete the content of NQQuery.log completely, save file.
• Start BI Server services.
• Open BI Analysis and run any simple report.
• Go to advanced tab.
• Within Advanced SQL Clauses, Scroll down to see the option: Prefix
• Here write SET VARIABLE LOGLEVEL=2; (semi-colon at the end is must)

• Now click on Results tab to re – run the report.
• Now you go Settings > Administration > Manage Sessions
• Click on View Log to view the query. Now you are able to see the query and according to the LOGLEVEL you have set.
• Go back to Advanced tab, observe the Logical query.
• Before SQL, you find the text you written in Prefix field.

• By mentioning the semi colon, BI Server executes these statements one after another.

Different Logging levels in OBIEE 11g

Logging Level	Logging Level Information
Level 0	No Logging
Level 1	Logs everything logged in Le Logs everything logged in Level 1. Additionally, for each query, Logs the repository name, business model name, presentation catalog name which is called as Subject Area in Answers. Logs the SQL for the queries issued against physical databases. Logs the queries issued against the cache. Logs the 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 2	Logs everything logged in Level 1. Additionally, for each query, Logs the repository name, business model name, presentation catalog name which is called as Subject Area in Answers. Logs the SQL for the queries issued against physical databases. Logs the queries issued against the cache. Logs the 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 that logged in level 3. In addition to this, logs query execution level.
Level 5	Logs everything logged in Level 2. Additionally, logs intermediate row counts at various points in the execution plan.
Level 6 & 7	For future use