We have a cube with three large dimensions in it, one million plus members
each. Aggregation usage set to standard.

Every hour we add a new partition (3-4 million rows in that partition) to
the cube and incrementally process the dimensions. While these dimensions
are being processed, the cube is almost unresponsive from the users
perspective. Queries that one/two seconds take one/two minutes. Once the job
stops and finishes, the cube runs quick as can be.

The processing of the dimensions take 90% of the total processing time. The
fact we do it every hour means this cube is in that state for about 15 or so
minutes.

It's a 64 bit Intanium box, with 14GB of memory dedicated to MSAS and 8
procs. Seems like this shouldn't be happening. There's not much else going
on in the box.

Performance settings:
Max threads: 16
Large level: 10000 members

Memory settings:
Min allocated: 7168MB
Min conversation threshold: 14336MB

Processing settings:
Read ahead buffer: 128MB
Process buffer: 7168MB


Any ideas?

Well, when incrementally updating dimensions, AS2k will automatically
refresh the cube partitions below it which may have an impact on this. Due
to the larger size of the dimensions, it may be that you're hitting a point
where you may want to have reporting OLAP cubes and processing OLAP cubes
(once the Olap database is finished processing, you go ahead copy it to
another server where people will query it to report). But before a drastic
change like that, just wondering if you're getting any lazy aggregation
errors within the event log indicating issues with Olap dimension processing
(and hence the slow response)?

--
HTH!
Denny Lee
<dennyglee_at_hotmail_dot_com>

Blog at:: http://spaces.msn.com/members/denster/



"Jesse O" <jesperzz (AT) hotmail (DOT) com> wrote

Thanks for the great response Denny.

Looked at the event log, and didn't see any errors whatsoever related to
OLAP.

How is AS 64bit in shipping files?



"Denny Lee" <dennyglee (AT) hotmail (DOT) com> wrote

About the same as 32-bit. I ended up simply copying the files over
(actually, I copied over only the files that changed or were added). It
gets a little tricky as this technically isn't supported and you want to
span this across two servers where a person could continue to query against
the processing server (or another reporting server), you copy over the
"diff" files, and then people can start querying the new server. Out of
curiosity, how many partitions are you dealing with if you add new
partitions every hour? Its possible that the sheer number of partitions in
combination with the cube refresh is causing some issues - for example, what
happens if you reduce it to daily partitions? Sure you would do full
processing on that daily partition 24 times a day, but that may often help
the situation especially since we're talking about 96m rows per partition,
which isn't too bad for 64-bit.

--
HTH!
Denny Lee
<dennyglee_at_hotmail_dot_com>

Blog at:: http://spaces.msn.com/members/denster/



"Jesse O" <jesperzz (AT) hotmail (DOT) com> wrote

Thanks!

Actually, we have 23 partitions at the end of the day, and as the 24th hour
comes in we delete all the hour partitions and create a daily partition.

So far we have about two months of data, so appx 60 daily partitions and no
more than 23 hourly partitions at a time.

"Denny Lee" <dennyglee (AT) hotmail (DOT) com> wrote

Hmm - I'm guessing that the dimension size is really pushing you. Out of
curiosity, when you incrementally update the dimensions, do you do so
individually or do you do them all in one bulk transaction. Is it possible
to just incrementally update one dimension at a time to debug your way to
determining if a particular dimension is causing this problem? Oh, and
sometimes you may see this problem that you're talking about when you
process all of the dimensions within one transaction vs. processing them
individually.

--
HTH!
Denny Lee
<dennyglee_at_hotmail_dot_com>

Blog at:: http://spaces.msn.com/members/denster/



"Jesse O" <jesperzz (AT) hotmail (DOT) com> wrote

We process them one at a time.

I'm fully convinced it is the size of the dimensions causing the issue. It
wouldn't be too bad, except we have to incrementally process the dimensions
every hour. Three dimensions with appx two million members each isn't
friendly. I've looked to make sure all the indexes are there, ect...and they
are. I think shipping partitions is my only choice.


"Denny Lee" <dennyglee (AT) hotmail (DOT) com> wrote

Yeah, looks like you gotta go that route in that case - good luck, eh?! :-)

--
HTH!
Denny Lee
<dennyglee_at_hotmail_dot_com>

Blog at:: http://spaces.msn.com/members/denster/



"Jesse O" <jesperzz (AT) hotmail (DOT) com> wrote