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:
- Open the OBIEE Administration Tool Console
- In the File menu bar select Manage > Security…
- 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…”
- Click the “Yes” button if prompted to Check Out Objects
- In the properties window, change the Logging Level from 0 to a 1 or a 2 (See table below which describes all logging levels).
- Click OK to back out of all windows and return to the Administration Tool.
- In the Administration tool clear the cache(s).
- 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
- No logging.
- 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.
- 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.
- 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.
- Logs everything logged in Level 3.
- Additionally, logs the query execution plan. Do not select this level without the assistance of Technical Support.
- 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.
EPM applications help measure the business performance. This post will help you choose the best EPM solutions for your organization’s needs and objectives.
It’s 2015 and you can now establish totally respectable MS SQL DBA credibility just by mentioning you have been in the game since SQL Server version 9. You may even get the same gasps of shock from some colleagues that used to be reserved for the version 6 veterans.