OBIEE: Summary Tables

The past couple of days I have been trying to figure out how to force OBIEE to use a summary table.

It was funny timing because I had been playing around with that functionality last week wi th the SH tables. Specifically, the CAL_MONTH_SALES_MV materialized view provided.

A colleague had been attempting it for a few days but was unable to get it working. I thought it would take me about an hour to figure out...I was wrong, very wrong. It's been fun though...being immersed in figuring out a specific thing...it's been awhile since I've been able to do that.

First, I was trying to get the Time Dimension (a logical object in OBIEE) to work correctly (read here, here and here). Some where in the middle of that, I was trying to resolve a join that should not have been there; I located the source of the through a Group Filter, basically, VPD applied through OBIEE.

I had added the summary table to the physical layer and joined it to the time dimension (based on fiscal year week). I then added it to the Source of the fact table in the Business layer and mapped the column.

None of this worked though.

I could disable the main fact table and it would then use the summary table, but that wasn't a solution. Amusingly, after about 50 different permutations of the same thing, I thought I had solved the problem and reported that fact to the person responsible. Near the end of my explanation, I remembered that I had forgotten to re-enable the main fact table.

Then I was on to Fragmentation.

Then Mr. Rittman shot me down.



Have I mentioned I love Twitter?

He was right of course. I read up, more, on Fragmentation and realized that it is more for disparate sources (not databases, but data). At least I can tuck that away for later use.

I left work at 8, changed rooms (internet, hard line, wasn't working), went for a walk/jog on the treadmill, had a beer or two and logged back in to see if I could give it one last go. I'm glad I did.

I then found Mr. Goran Ocko's post...I knew it was something small. Sure enough, from Sources --> Logical Table Source --> Content



I hadn't set the Level. ID10T

Now I ran my report, starting with Year. Checked the logs to see the physical SQL and sure enough, the summary table was being used. Drilled down, same thing. When I got to Day (the summary was at the week Level, despite what that pictures says), it went to the fact table bypassing the summary table.

Thanks Goran for sharing that information. There's at least one person out here who appreciates it.