OBIEE: Gotcha #4

Database Features

This may have been self inflicted, there is really no way to know. This is somewhat related to Gotcha #1.

The Problem

We kept tipping over the development database with only 10 or so people on it. The DBAs said we weren't including the date predicate on any of our tables. How could that be? (Quick aside, this problem is compounded; in that we have no summary tables and close to 2 billion records in the fact table). So instead of pulling back say, 100K records, we were pulling back 23 million. Yikes.

I started to pour through the query logs and confirmed that none of the physical SQL statements had any date predicates. Ouch.

But wait, reviewing the BI Server execution plan, the date filter was there...the catch was that the BI Server was filtering. Not good. The BI Server is not built to filter on some 23 million records. Most of us know that we should filter as much as possible, with Exadata pushing that concept down to storage.

Being responsible for the metadata, I naturally assumed this was somehow my doing.

So my colleague, Frank Davis, fired up a GoToMeeting session and we poured over the logs and anything we could find. We spent a few hours last night to no avail.

With a partial nights sleep, I began to use one of Frank's methods to further eliminate the offending table (possibly). Since it was the date predicate, I started with Time.

First, I pulled in Fiscal Year and created a filter for 2010. Ran the report, checked the log and the filter was in the physical SQL.

Next, Fiscal Week. Followed the same process, added the column, the filter and checked the log. It was there.

Finally, I brought over Calendar Day, created the filter, ran the report and checked the log. It wasn't there. WTF? I could see it in the execution plan, but not in the physical SQL.

Then Frank and I got back on a shared session and Frank had an idea...

The Solution

Frank wondered aloud about the database features, specifically, which were turned on.

If you aren't familiar with that, in the property section of the physical database, under the Features tab, you'll see the list of values, like this:



See the ones I am pointing to? DATE_LITERAL_SUPPORTED? Yeah, that was turned off. Quite a few were turned off in fact. Frank found the guide for setting up the features on our particular database (Neoview) and we went through and turned everything on that was recommended.

I reran my small test and voila! The date predicates were being pushed to the database. Ran some of the other offending reports and verified that they too were passing the date predicates.

I don't know if I somehow reset the database features or if someone else did. I'll never know in fact (yes, bigger problem). Thankfully it was only in a development environment so the impact was minimal.