How to view the physical SQL in OBIEE

Often times you need to see the SQL behind a given analysis. This is incredibly helpful, especially when troubleshooting. You would think this would be a straightforward process, but as is often the case with Oracle, this isn't always true.

Here are the steps I follow (for 11.1.1.5 and 11.1.1.7).

1. Open and edit the analysis you are interested in. Go to the Advanced tab. Under the advanced tab, copy the query inside the 'Issue SQL' box.

2. Go to the Administration area.

3. Go to 'Issue SQL', and paste your query into the box.

4. Make sure you use Logging Level 2 or higher. Level 2 provides less info, so it may run faster. I have to check the cache box, but I'm not sure if that's the case for others.

5. Now click the 'Issue SQL' button and scroll down to the bottom. You should see a link to 'View Log'. In the log, look for: Sending query to database named <dbname>
Just below that, you should see the physical SQL query that the analysis uses.

If instead you see: "Cache query hit"  and you only see the Logical SQL query, then you need to clear out the query cache.

There are probably different ways to do this, but the only way I know is to just purge ALL cache. You can do this by using the same 'Issue SQL' box, and issuing the command: call SAPurgeAllCache()

Once the cache is cleared successfully, try running the analysis query again, with the correct logging level ect.. and you should now see the physical query.

Hope that's been helpful... happy reporting!

Export not working in Oracle OBIEE 11.1.1.5.5 dashboards

I was getting a javascript error when trying to export reports from a Dashboard in OBI. It looks like a piece of code is missing.

Depending on what browser you are using, the error may be about Downloadguard.js or viewcontrol.js. Or simply, 'control is undefined'.

A work-around to this (assuming you don't want to upgrade to a higher version), is the following:

1. Edit dashboard page.
2. Add the Text Object.
3. Input the following.
<script type='text/javascript' src='res/b_mozilla/downloadguard.js'></script>
4. Check the box Contains HTML Markup
5. Save it, and now the export should work!