Fun with OBIEE SQL

I posted a couple of tweets yesterday about this crazy query I had to work with. I won't Storify you to death, so I'll sum it up quickly. 1269 lines (formatted, of course). 13 WITH clauses. 8 base tables.

The error that was occurring was an Oracle one: ORA-00937, "not a single-group function."

That's interesting because OBIEE, the BI Server, generates/builds the SQL based on the logical model you've created. It's doing it wrong, in this instance. I'm willing to wager that it's a bug, in one way or another.

Now, if the logical model doesn't support this particular combination of columns, grain, or whatever, it will tell me so immediately. It will usually tell me while working in the RPD, so I won't even get this far (presentation layer).

Anyway, I found the offending piece of SQL, right at the bottom in the SELECT statement. There were 2 analytic functions, MIN and SUM and no GROUP BY. So I took the non-analytic columns, created a GROUP BY and added them in. It worked.

That didn't solve my problem though. Sure, I could run it in SQL Developer, but that doesn't do the end-user much good.

I tried to trace those 2 columns back up through the SQL...and then my eyes bled. With help from the user, I identified the column (measure) that was breaking the report. Now, could I trace this back through the SQL and figure out where? Not likely, but I tried.

I needed a visual representation, so I tried out FreeMind, a mind mapping software. This is what I came up with:



Nodes in Yellow share the same base tables. Ditto for the other color coded nodes. I couldn't figure out a way to get them to connect or share those child nodes.

Ultimately a fun little exercise (in futility?). Anyone else tried to do something like this?