Select Page

Art of BI: OBIEE Logging Security Restrictions and Levels

Author: Christian Screen | | July 21, 2009

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.

Oracle BI Publisher (BIP) Tips: Functions, Calculations & More

Check out these BI Publisher tips including functions & calculations so you can understand more about the production and support of BI Publisher reports.

Sherry Milad | January 15, 2018

Qlik vs. Tableau vs. Power BI: Which BI Tool Is Right for You?

Tableau, Power BI, and Qlik each have their benefits. What are they and how do you choose? Read this blog post for a quick analysis.

Tom Hoblitzell | June 6, 2019

How to Index a Fact Table – A Best Practice

At the base of any good BI project is a solid data warehouse or data mart.

Christian Screen | March 16, 2010

Subscribe to Our Blog

Never miss a post! Stay up to date with the latest database, application and analytics tips and news. Delivered in a handy bi-weekly update straight to your inbox. You can unsubscribe at any time.

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.

CONTACT US

Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.

EXPLORE JOBS