Wednesday, November 28, 2007

@CALCMODE(BOTTOMUP) Saved My Life

So I finally figured out my problem. I added @CALCMODE(BOTTOMUP) to some member formulas that are giving me fits -- taking way too long to retrieve. Some of them worked and some of them didn't. With some careful tweaking of the ones that didn't, I was able to get retrievals that were taking 20 seconds to return in less than 1 second. Woo Hoo!

One user's report that had been taking over 20 minutes to retrieve now comes back in 62 seconds. Who needs ASO when you've got this setting? That reminds me, I need to fix a little issue I've been having with updating numbers during the day in one of my cubes. Will address that one tomorrow...

Friday, November 23, 2007

More Performance Problems

I worked through trying to speed some variance calculations up today. I made use of the @CALCMODE(BLOCK) and @CALCMODE(BOTTOMUP). They helped a little bit, but I'm still not satisfied. Question of the day: does Direct I/O yield better performance on an empty cube getting data via a transparent partition than Buffered I/O? Doesn't seem to matter. I don't think the data file cache can be used on an empty cube. Not sure why the system takes up Index Cache in that case...

Thursday, November 22, 2007

Bringing Block Into Memory Runs All Dynamic Calcs?

I ran into my very first performance issue of a dynamic calc, that was not being retrieved (or referenced in a member being retrieved) severely slowed down performance of a cube. I cleared the formula from the offending member and the length of retrievals was decreased significantly. I'm going to need to work this one out. I've got too many dynamic calcs in there I think...

No compression?

I changed a partition for a cube on Tuesday night, then cleared upper level blocks, then ran a calc. I checked on the cube a little later and the filesystem was full! Looks like the compression setting was changed to 'No Compression'. Not cool at all. I changed the setting to bitmap, cleared upper level blocks and ran a restructure to fix the problem. Anyone else see this happen before? I know I didn't change that setting. IMO 'no compression' shouldn't even be an option.

Tuesday, November 20, 2007

Messing with partitions

Added an application between the one with the data and the one the user sees via a transparent partition so I could get some variances to work. I'm not happy that I had to do it, but I had no other choice.