Art of BI: 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…
    [simage=6,200,y,center]
  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…”
    [simage=5,400,y,center]
  4. Click the “Yes” button if prompted to Check Out Objects
    [simage=7,200,y,center]
  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).
    [simage=4,400,y,center]
  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.
Contact Us
Christian Screen
Christian is an innovator in analytics and data warehousing design, best practices, and delivery. With more than fifteenyears of decision support and data warehousing with key experiences at Office Depot HQ, Sierra-Cedar, and Capgemini, he oversees the Oracle Analytics Practice which includes the technical development and delivery of Oracle BI collaboration software, data warehouse solutions, Oracle BI/EPM projects, and packaged analytics solutions at Datavail.

Leave a Reply

Your email address will not be published.
Required fields are marked (*).

5 thoughts on “Art of BI: OBIEE Logging Security Restrictions and Levels”
  1. Supporting documentation can make the difference between a bit of kit that’s ok or one that’s exceptional at the job it was designed for. How do you know you’re using something properly if you don’t know it’s capabilities for instance. I’m not sure why appeared when I was searching specifically for technical documentation services but I read it anyway and will think a bit more about it. There are some interesting points.

  2. How do you control logging level for users when you don't have any users defined in the repository? We've integrated OBIEE with Hyperion and only set up groups in the repository. The users are assigned to the gorup in Shared Services.

    Jerry

    1. Jerry,

      You should be using security Groups to set the logging level for users. If you are using the Shared Services integration then you are most likely using the SS open LDAP authentication. Just create the groups in shared services and then again in the RPD. Set the logging level for those groups in the RPD and it should work for you.

  3. Hi Jerry.
    How do you set log level for Groups in rpd.
    I think the log levels are set for Users not Groups..