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!

Watch YouTube Videos by Country

Sometimes I wonder what the hell goes on in Greenland... or does Chittagong, Bangladesh have video bloggers?  I consider myself lucky to be living in a time where you can 'visit' just about any place in the world.

So, exactly how DO you find youtube videos posted from a certain country? 

You need to utilize the glorious Youtube API. The old one, in fact, but it works. This allows you to search for videos based on their latitude and longitude. Though, not all videos include that data, so it's not a perfect science. But you should find some results.

Here is an example url you would use:
 http://gdata.youtube.com/feeds/api/videos?max-results=50&start-index=5&q=bible&location=37.42307,-122.08427&location-radius=200km

Basically take the base url:  http://gdata.youtube.com/feeds/api/videos?

Then add any filters you want to include. They are optional:

location=37.42307,-122.08427  (Here is where you put the latitude/longitude for the location you wish to search)

location-radius=200km   (You can specify the km radius to search around the location)

 
max-results=50   (50 is the maximum amount. But you can make this lower if you want)

start-index=5    (What page do you want to start at?)

q=bible    (What type of keywords do you want?)

By default it will search by keyword relevance. But you can add these to the url, to search by
something else:

&orderby=published   (Search by published date)
&orderby=viewCount  (Search by viewCount - Highest to Lowest)

Unfortunately these are always in Descending order, and can't be changed to Ascending. Which is stupid! But whatever.

How do you find a location's latitude and longitude?    I use Google Maps, but a quick search would probably reveal more ways of doing it.

Using Google Maps, you can find a location (ex. Chittagong, Bangladesh).  Right-click on the city,
and select "What's Here?"

Then Google Maps will show you the name of the City, and the Long/Lat data. Simply copy and paste it into your url.

 

Here it is, with no other filters, except location and location radius:

 http://gdata.youtube.com/feeds/api/videos?location=22.289096,91.799927&location-radius=10km 

Let's see if I get anything....

 

It looks like there a good number of videos out there actually. 

I like changing the URL, because I find it to be the fastest way. But if you don't like messing with that,  you can go to http://gdata.youtube.com/demo/index.html, and scroll to the bottom. There is a form where you can enter this info, and generate an URL.

But why do all that when you can cut out the middle-man I say.

Happy Searching!