OBIEE: Gotcha #1.1

Another, funny in hindsight, continuation on Gotcha #1.

Sadly I can't speak to the specifics of what I was doing but it did involve the coolest thing ever, Exadata.

We ran into some problems when are reports weren't returning the correct results. Specifically, year over year data (last year's).

I suspected one thing, someone else suspected another and a third person got it right.

Turns out, we had added a "blank" logical column to the RPD. Not sure of the exact reason why or what was trying to be accomplished, but there it was.

As I dug through the physical SQL, I noticed a bunch of '' (that's back-to-back single quotes), essentially an empty string. I have read on how Oracle handles empty strings and how many vendors deal with NULL values differently, so I suspected this was the cause. My first thought wasn't correct though.

First a sample of how Oracle handles both empty strings and NULL values in a WHERE clause.

CJUSTICE@TESTING>SELECT * FROM dual WHERE '' = '';

no rows selected

So comparing an empty string to itself evaluates to false.

CJUSTICE@TESTING>SELECT * FROM dual WHERE NULL = NULL;

no rows selected

Of course everyone knows that NULL is the absence of value, so comparing it to itself evaluates to false.

My original thought was that somehow the '' (empty string) was being used in the GROUP BY clause...but that didn't make much sense as even if it was used, it would be the same "value" as in the SELECT.

Then I saw it...after digging through 500 lines of OBIEE generated SQL, there it was.

In the first Gotcha, I had been capturing physical SQL not tuned specifically for Oracle...it was just using a generic ODBC connection. I'm glad I made that mistake though, because I had all that SQL.

For this specific report using the generic ODBC connection, 4 queries were sent to the database and stitched in OBIEE to display the reports.

When the driver was switched to an Oracle specific one, it create one giant WITH statement. Actually, it was about 8 different WITH statements...which made it hard to analyze because of the dependencies on previous WITH statements in the same SQL.

I found it be running the first 2 SELECT statements individually. I got the results from this year and the results from last year. Perfect. But it added to the Why? of what was going on.

Further down in this massive SQL statement, I found 2 FULL OUTER JOINs. Could it be? Could it really be joining on that '' column?

Yes it could and it would explain exactly why incorrect results were being returned.

Per the example above, when comparing an empty string against itself, the expression evaluates to false...so the table being joined to would never return results.

We decided to add a character to it, a dot (.) and sure enough, the correct results returned.

Another realization after the fact (made by my colleague), was that using an Oracle database would decrease the number of queries being sent to the database thus allowing for more connections and less work by the BI Server. Super win!