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!

Creating Datastage Routines for hash files

I created some datastage routines to help manipulate hash files more easily. Instead of trying to work with the Admin console, I created reusable routines that will:

• Count rows in a hash file
• Remove rows from a hash file
• Select rows from a hash file

I'm not exactly an expert, so my code is fairly simple and straightforward.

To create a routine, go up to File, and select New and Server Routine.

Give the routine a name and description, and choose the type you want it to be.

Most of my routines are 'Transform Function' type, because I use them within Transformers. They take data as arguments, and return results.

Next, you add what arguments your function will be using (if any). Then your code. Compile it. Test it. Save it. Done.

 Below is the code I use for my different hash file routines:


1. Count rows in a hashed file

I use one argument, the name of the hashed file I want to count.

The Code:


**This creates a pointer called myhash that points to your hashfile (Arg1). I hard code the hashfile location.
Command1 = "SETFILE C:\IBM\InformationServer\Server\Projects\<YourProjectName>\<YourHashDir>\":Arg1:" myhash"
Call DSExecute('TCL', Command1, Output, ReturnCode)

ErrorCode = ReturnCode ;* set this to non-zero to stop the stage/job

**Next you simply execute the COUNT command on your hashfile pointer. The result gets returned in Ans.
Command2 = "COUNT myhash"
Call DSExecute('TCL', Command2, Output, ReturnCode)
** The Ans output has crazy formatting because it returns as '*38454 records counted.*' And I want it to return 38454.
Ans = (Output [' ',1,1]) [2,10]

ErrorCode = ReturnCode ;* set this to non-zero to stop the stage/job

** Remove Pointer when done **
Command3 = "DELETE VOC myhash"
Call DSExecute('TCL', Command3, Output, ReturnCode)

ErrorCode = ReturnCode ;* set this to non-zero to stop the stage/job

*******

Here is an example of how I then use the routine in a Transform.

The routine is called HSUCountHashRows().  I use a job parameter (vHASH) to capture the hashed file name to be counted.  The name gets passed to the routine, as the argument.  I add the "+ 0" to convert the answer to an integer.  The transform then returns the file name and the resulting count.


2. Delete Hashed row by Primary key

This routine takes 3 arguments. The hashed file name, the column name that holds the primary key, and the primary key itself.

The Code

** Set file Pointer **
**This creates a pointer called myhash that points to your hashfile (Arg1). I hard code the hashfile location.
Command1 = "SETFILE C:\IBM\InformationServer\Server\Projects\<YourProjectName>\<YourHashDir>\":Arg1:" myhash"Call DSExecute('TCL', Command1, Output, ReturnCode)

Command2 = "DELETE FROM myhash WHERE ":Arg2:" = '":Arg3:"'"
Call DSExecute('TCL', Command2, Output, ReturnCode)
Ans = Output

ErrorCode = ReturnCode ;* set this to non-zero to stop the stage/job

*******

3. Delete Hashed row by Date

This routine deletes rows within a certain date range. I normally use this when I need to redo a load for the day. I delete from the hashed file those records with a load date (CREATE_EW_DTTM) of today.

The Code

**This creates a pointer called myhash that points to your hashfile (Arg1). I hard code the hashfile location.
Command1 = "SETFILE C:\IBM\InformationServer\Server\Projects\<YourProjectName>\<YourHashDir>\":Arg1:" myhash"Call DSExecute('TCL', Command1, Output, ReturnCode)

Command2 = "DELETE FROM myhash WHERE CREATE_EW_DTTM > '":Arg2:"' AND CREATE_EW_DTTM < '":Arg3:"'"
Call DSExecute('TCL', Command2, Output, ReturnCode)
Ans = Output

** Remove Pointer when done **
Command3 = "DELETE VOC myhash"
Call DSExecute('TCL', Command3, Output, ReturnCode)

4. Select a Row with a given Primary Key

My hashed files all use the same column for the primary key, so I didn't need to pull in the column name as an argument.

The Code

**This creates a pointer called myhash that points to your hashfile (Arg1). I hard code the hashfile location.
Command1 = "SETFILE C:\IBM\InformationServer\Server\Projects\<YourProjectName>\<YourHashDir>\":Arg1:" myhash"

Call DSExecute('TCL', Command1, Output, ReturnCode)

Command2 = "SELECT * FROM myhash WHERE PRIM_KEY = '":Arg2:"'"
Call DSExecute('TCL', Command2, Output, ReturnCode)
Ans = Output

** Remove Pointer when done **
Command3 = "DELETE VOC myhash"
Call DSExecute('TCL', Command3, Output, ReturnCode)

ErrorCode = ReturnCode ;* set this to non-zero to stop the stage/job

***

Hope this has been helpful. Happy coding!

How to Use* a Mobotix Camera (*hack)

Mobotix cameras are great. They come with tons of features, top notch audio, video, recording, ect...

I don't actually own one, mind you. I like to find them online, and simply see what they see. This article is about what to do with a Mobotix camera, once you've found one, or if you own one.

First off, how do you find a mobotix camera you ask? The easiest way is a simple Google search:

inurl:"/control/userimage.html"

Most camera links will have titles like:
 mx10-9-31-61 Live
 m22 Live
 Live
 Menu

There are other searches you can try, but I'll leave that up to you to find.

Now that you have a camera, let's see what fun things we can check out!

Depending on the setup, different options will be available for you to play with. You might be able to move the camera, or zoom out and in. You can change the FPS and the camera view (Panoramic vs. Center).

I'm going to focus on my favorite elements.. AUDIO and screen TEXT.

---------------------------------------------------------------------------------
AUDIO

Mobotix has some fun audio elements. A Mobotix camera includes a speaker to play ABBA out of (or whatever), and a microphone you can pick up sound from. You can even talk through the camera speaker using your mic (assuming its been activated).

First you need to turn on the audio. I can only seem to get this work from IE, and not firefox or any other browser.
All you do is click the 'Audio On' button on the left. If you don't see it, then sorry, it's not activated.

Once you press it, it may ask for a username/password. Don't fret! Simply say Yes or Si or whatever language the button on the left is in. Then just click Cancel. Now it will either work, or it will say something about the Admin needing to activate the audio (no sound for you!).

You know it worked when a little speaker icon shows up at the top.

Now it's no good playing sounds through the speaker when you can't hear them. At least not to me. So turn up the camera mic. Go to the center drop-down and look for Microphone Sensitivity. Now you can choose how much sound it picks up. I find that this feature is most often available.

Next, try playing a sound through the speaker. Go to the same drop-down and choose speaker volume. Choose a number between 30 and -30 (I recommend 30 for hella loud). Select it. Now open the drop-down again and pick a sound from the list at the bottom.

Sometimes they don't all work.
If the camera is on a busy street, you can have a lot of fun scaring people with the camera alarm or confuse them with the cuckoo clock. Immaturity for the win!

 Let's try something more interesting now. Check to see if the speaker mic is activated. To do this, right click anywhere in the camera window. You will see two options: Audio from Camera and Audio To Camera. Click both so they both have checkmarks next to them.

 Now that it's checked, hold down the right-mouse button in the window, and the cursor will turn into a red microphone. Your microphone is now broadcasting through the camera!

 Again, this doesn't always work, if it's been deactivated. You can hear yourself through the camera mic, if it IS working.

MORE CAMERA AUDIO

In some cameras, you can access the action controls. You can find these by adding /control/ OR /control/action to the camera URL. (Example:  http://83.xxx.xxx.20/control/)

One action that I like, especially when the mic trick doesn't work, is the "speakip" action. This causes the camera to broadcast it's IP address and mac address over the speaker in a computerized female voice. It's loud, obnoxious and never fails to get attention.

Just pop this in the URL:   
http://89.xxx.xxx.107/control/rcontrol?action=speakip

Since it's just a url, you can even create a link in a forum or chat site saying something like "AWESOME FREE PORN", and as people click it, the camera just keeps broadcasting. HAHA


CAMERA TEXT

The Mobotix camera window includes some text options. Such as writing text, outside temperature, time/date, and much more.
If you want to add text, you need to access the control area.
Add /control/control?list to the end of the camera URL to access.

Example:  
http://83.xx.xxx.20/control/control?list

It's a little tricky to manuver around, but it provides you with the syntax you need to change the text.
 If you wanted to change your text color, you would use a url like this:

http://83.xx.xxx.20//control/control?set&section=text&textcolor=0x00FF00 

And to add/change text, you would use a url like this:

http://83.xx.xxx.20//control/control?set&section=text&print=Big Brother Is Watching

 Mobotix cameras have many options to check out, but these are few of my favorites.
 And those of you who go out and buy one... don't forget to change the default username and default password:   admin/meinsm

NOTE: If you have a trendnet camera, that is another one you really want to update, and change the username/password too.

http://71.189.230.108/anony/mjpg.cgi

Don't believe me? Do a google search for inurl:"/anony/mjpg.cgi"

Have Fun!

Filter Twitter by Country

This will either pull the country the person is twittering about, or the country they are from.

To only pulls tweets in the US, use ->   near:"hutchinson" within:1000mi

Here is an example search, that shows who has checked into US hotels on foursquare:

4sq.com hotel OR motel near:"hutchinson" within:1000mi

Have fun!

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!

Favorite VPN, Phone, Cellphone, and Firewall

Here is a catch-all post about all the new toys I'm playing with. I give each of these a thumbs-up.

Are you looking for a VPN? This one isn't free, but for those who are willing to pay for awesome-ness, this is the one. Check out Private Internet Access.

Yes, not the most creative name, but they really get the job done. The price isn't bad, and while they don't have a free trial, you can pay a small amount to try it for a month. That's what I did, and I'm sold.
It's easy to use and works great. I'm no expert on VPN's, so feel free to do more homework, but I would start with this one.

Next is phones....
I'm a fan of Skype. Again, it's easy to use and you can't beat the price. For 10$ you can call any number in US/Canada for 3 months. Target and other such stores even sell $10 gift cards that can be used towards Skype, so add it to your Christmas list.

I also use a pre-paid cellphone because I don't use a cell that much. I went with page plus cellular because you can hold onto purchased minutes for a whopping 120 months (!). They use verizon towers, so I haven't had a problem using my phone most places.
It has been finicky at times, when calling out. But only once in a blue moon. And frankly, you can't beat the price. I have to buy my minutes online, since they don't sell cards in my area, but it's very easy and quick to do.
So, if like me, you don't use a cell very often. And you want to hold on to minutes as long as you can before they expire, I would check them out. They have different plans, or you can pay by the minute.

I was in the market for a firewall, and I'm currently using Comodo Firewall. So far, I'm really liking it. They have a free version, which is great. And it comes with anti-virus too, if you need it.

Okay, that's all I got for now. Just my 2 cents, hope it helps.

Datastage - Loading MySQL Text into Oracle Varchar

I had to create a simple job to load data from a MySQL database into an Oracle database.
Everything was working fine until I had to move MySQL data that was in a Text format, into an equivalent Oracle field.


I grab the data in my first stage, and move it into a field of type Char, with the necessary byte size. The data then gets loaded into CLOB fields in Oracle, and in Datastage I label them as type 'Char' with the necessary byte size.
So far, this seems to be working well.

---------------------------
You could also try using a User-created SQL statement, that casts the MySQL text data into characters.

Example: cast(description as char(2000)) as description

Then in DataStage, I have the data coming in as Varchar, and loading into Varchar2(2000) fields in Oracle. Keep in mind that I know these fields aren't very big, even though they are Text fields.
Perhaps not the most elegant, but it got the job done.

It also worked to have the data coming in as Char, and loading as Char. Make sure the field size is large enough.

Reading Datastage Logs

When using IBM InfoSphere DataStage and QualityStage, it's important to know how to use the logs. They can answer a lot of questions, and help isolate problems.

This is a simple introduction to reading the logs in Director.

1. Change the Refresh
You'll learn pretty quick that the default refresh is way too short. The page will refresh every 5 seconds, making it practically impossible to read anything.
First thing you should do, is lengthen the refresh time, or change it to manuel refresh only.
Go to Tools -> Options... -> General Tab ->  change the refresh time to 60 seconds or disable it.

2. Filter/Search for Warnings/Abort
Open your log file in Director. Go to View -> Filter Entries...
This will open up a box where you can filter for particular errors (ex. Warnings). You can also use Ctrl-F to search for a specific word or phrase within your log. You can also have it show more or less of the logs.

3. Check Related Log(s)

When you are checking a log for a sequence, that has several jobs, you can right-click a "Run Job" line, to see the log for that specific job. Click on Related Log.

4. Check the Details
When jobs abort, check the details. This is obvious, but some people gloss over these. Often there is more information about what caused the job to abort. Bad queries may display an error of bad syntax. A record with bad data may cause the job to abort, and often the record's data will be displayed in the error, so you can find the record and fix it, or delete it.

This is very basic information, but this is the best place to start when troubleshooting. For any just beginning in Datastage, hopefully this was helpful and Good Luck!

Remote Desktop .exe

In Windows, hit Start, and Run...  then mstsc.exe to open Remote Desktop.

Done!

I always forget this. What does it stand for?  Microsoft Terminal Services Client.
Or maybe it's easier to remember as... My STupid Sucky Computer

The program is usually located here:
C:\WINDOWS\system32