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.