OBIEE Logging Security Restrictions and Levels

OBIEE has a smart query logging system and it happens to be mainly based on security logging levels.  This is great from an overhead perspective because you wouldn’t want every single user of the system to have a full introspective query log produced each and every time your hundreds of users run a query requests or dashboard view hit. 

Typically one will use logging during development, analysis, or troubleshooting efforts.  Otherwise there is no need to have logging on any higher than the default setting.  By default each user account’s “Logging Level” is set to 0 (zero) which is basically no logging.  For basic purposes of analyzing SQL or MDX syntax that make up the query, row counts, and recordset deliver time you will want to change the “Logging Level” to a 1 or 2.  These two levels start providing basic logging which can be seen from Settings > My Account > Manage Session in Answers.

Here’s how to change the “Logging Level” for a specific account:

1. Open the OBIEE Administration Tool Console
2. In the File menu bar select Manage > Security…
   
3. In the Security Manager window choose the “Users” item from the left panel.  Now in the right panel right-click on the user in question show the menu. Select “Properties…”
   
4. Click the “Yes” button if prompted to Check Out Objects
   
5. In the properties window, change the Logging Level from 0 to a 1 or a 2 (See table below which describes all logging levels).
   
6. Click  OK to back out of all windows and return to the Administration Tool.
7. In the Administration tool clear the cache(s).
8. Return to your report/view, run it, and view the log.  Be sure to check the timestamps to ensure that you are looking at the right report. If so, you are good to go.

Logging Levels Breakdown per the OBIEE help menu

Level 0

• No logging.

Level 1

• Logs the SQL statement issued from the client application.
• Logs elapsed times for query compilation, query execution, query cache processing, and back-end database processing.
• Logs the query status (success, failure, termination, or timeout). Logs the user ID, session ID, and request ID for each query.

Level 2

• Logs everything logged in Level 1.
• Additionally, for each query, logs the repository name, business model name, presentation catalog (called Subject Area in Answers) name, SQL for the queries issued against physical databases, 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.
• Do not select this level without the assistance of Technical Support.

Level 4

• Logs everything logged in Level 3.
• Additionally, logs the query execution plan. Do not select this level without the assistance of Technical Support.

Level 5

• Logs everything logged in Level 4.
• Additionally, logs intermediate row counts at various points in the execution plan. Do not select this level without the assistance of Technical Support.

Level 6 and 7

• Reserved for future use.