OBIEE – Enable logging – Getting Database queries

Post date: 22-Mar-2010 13:39:54

 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.

If you have external authentication and you want to set LOGLEVEL explicitly for that user. 

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.

Courtesy: http://oraclebizint.wordpress.com/2007/11/06/oracle-bi-ee-101332-enable-logging-getting-database-queries/