Partition Solution

At a prior firm, I designed a solution to deal with updating numbers during the day. I had a small cube and added a dimension called "Iteration". It had 2 members Iteration-1 and Iteration-2. While the partition into the main cube went against Iteration-1, I would clear, load and calc data in Iteration-2. When the process was complete, I had a script that would switch the partition over to show the user the new data. The next time new data came in, it would use the other iteration member. The deal worked pretty well and the numbers would never disappear for the users. I liked the solution and so did the users.

The only issue with this process was when the partition wouldn't update because a user was pulling data. I wrote a script that would test for this and keep trying to reset the partition until it was successful. It rarely had to try more than once.

Well I'm faced with the same situation again and rather resorting to this methodology, I'm going to create an ASO cube. In theory I'll be able to clear and load the data quickly enough that the users won't even notice anything has happened. We'll see what happens.

@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...

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...

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.

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.