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!