Business Intelligence – Oracle

Oracle BI EE 10.1.3.3/2 – Enable logging – Getting Database queries

Posted by Venkatakrishnan J on November 6, 2007

I had 2 users asking yesterday about logging in OBI EE. What they wanted to know was a way, from within OBI EE, to find out what actual SQL Query generated by OBI EE for a report. Though it is very simple, i thought i would blog about it here since this seems to be a very common question. There are different types of logging in OBI EE. But for the above requirement we would just need the BI Server logging. Logging of BI Server is enabled per user. There are different levels of logging for BI Server. Let me paste the levels here directly from the docs.

      

As you see above the logging level needed to find out the sql query is 2( i have not included the remaining logging levels since those are not desired here). By default the logging level for BI Server is 0. So, in order to change the logging level to 2, go to the Administration Console and open the repository in online mode. Go to Manage-> Security -> Users and then double click on the user for whom you would like to change the logging level to.

      

Change the logging level to 2. Once done check in the changes. Log in to presentation Services and go to Settings->Administration->Manage Sessions. You would see different sessions that are running/have run. Click on view for the session that you need and you should see the SQL fired in the logs now. Of course, this will show the updated logging for sessions that started after enabling the logging.

      

      

Very simple but

Advertisements

7 Responses to “Oracle BI EE 10.1.3.3/2 – Enable logging – Getting Database queries”

  1. Krishna said

    Venkata, First of all, Im following your blog for sometime now and its awesome. Its very helpful for me and all others.

    The logging level needed for getting a logical/physical query is 2 and not 3. 3 is for getting a query plan.

  2. Venkatakrishnan J said

    Thanks Krishna. I have corrected that.

  3. Tom Okon said

    Hi.
    How would you do this using a session variable? We are using external authentication for our instance, so we don’t add users via the Administration Tool.

    Thanks.
    -Tom

  4. Venkatakrishnan J said

    In that case you need to set the LOGLEVEL system session variable everytime the user logs in. That will determine the logging level for that user.

  5. SreeHarsha said

    i have an impersonator user. I can see the session logs when logged in as administrator.. but when logged in directly as impersonator and when i check the session log, it says “Session log not found”. I have made the log level as 2 for impersonator also in the rpd..

    Can you please tell me what should i do?

  6. radha said

    changing the log lvl to 2 online is causing Oracle BI Presentation Server (sawserver.exe) to hang during the process of saving the rpd .Any one faced this kind of issue.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: