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!

Oracle, ODBC 32, Windows 7, and Golden32

Last week I needed a connection to an Oracle database. I planned on using Golden to access the database once the connection was in place. It took awhile to figure out all the steps and how they come together, but eventually I got it work.  If anyone would find it helpful, these are the steps I took:

First, I need to create an ODBC connection to my Oracle database.  But when I click 'add', this is what I see....

SQL, SQL, and only SQL. That does me no good.

#1 -  I need to download and install an Oracle Client. But Golden needs a 32bit ODBC connection, so don't download 64bit.

#2 - Find the download for "Oracle Database 11g Release 2 (11.2.0.1.0) for Microsoft Windows (32-bit)" on the Oracle website. Or whatever the new release is.

#3 - Download it, and follow the instructions to install it.  This usually means running a setup.exe, and adding the Oracle folder to your Windows path.

• In Command prompt: PATH C:\Oracle; %PATH%
• Once installed, add tnsnames.ora in your Oracle admin folder (C:\app\<user>\product\11.2.0\client_1\network\admin)

#4 - Add an Oracle Connection to ODBC-32bit. The path to ODBC-32 in Windows7 is here:  C:\Windows\SysWOW64\odbcad32.exe

#5 - Open Golden, and have it look in your tnsnames.ora, to find your database(s) and connect.