OBIEE: Gotcha #1

When generating SQL for testing, make sure you set the database to the database you will be running against.

I found this out the fun way, of course, by going through, grabbing the SQL from the logs, and then modifying said SQL to run against Oracle. I should have paid attention to that "Check Consistency" warning.



I spent an hour or so "fixing" the SQL, removing stuff like the following:

fn ifnull
{ curly brackets }
d '2004-04-10'
ts '2004-04-10 13:44:39'

mostly small stuff, but annoying none-the-less. Strangely (to me anyway), this was the SQL submitted to an Oracle database...without errors. I'm guessing that somewhere, either in OBI or in the driver itself, the SQL is rewritten.

After I realized what had happened, I went back into the RPD and updated the database properties.



It was ODBC basic



I changed it to Oracle 10R2/11g



I tried the Consistency Check again and still received the warning. Opened up the database properties again and selected the Features tab



Then selected "Revert to Defaults"



Run the Consistency Checker again and voila!



So don't waste your time like I did. Make sure you use the Consistency Check Manager and make sure your database defaults match.