I have worked with huge cubes and this is what I've found. Look at
perf mon while processing, I'll bet you see memory not being spiked too
bad, but processors being killed. Tip: Turn off anything that doesn't
need to be running. If you have calculate measures that involve
crossjoins, your in for a long show on the process screen. The more
crossjoins you have the slower it goes. Tip: Can you write the measure
another way to eliminate the crossjoins. Can you use the Generate
command to limit the number of rows that are brought back to the
client. Remember when viewing calculated measures they are calculated
at THAT time, not in processing. Can you narrow your cubes down. Can
you split them out by month or year. If you do that for your fact
table, try creating a view of large dimensions that will hold only
relevant data for that fact table. As far as processing goes, look at
the properties of the OLAP server, what is the (under process tab)
process buffer and read-ahead buffer size. The read-ahead defaults to
4mb, this is how many rows that are read at a time, if you have the
memory, increase it to 64mb. Whats the process buffer size, again if
you have the memory increase it. There is a point of limiting returns,
so play with the numbers to get the most performance without giving
away too much memory.
> We're developing a OLAP solution that will provide analysis support
> larger software app. We've got quite a few measures (pure and
> and dimensions and our Fact table currently holds 1.6MM rows of data
> down from 3.1MM while testing). We're seeing some huge performance
> when we try and put this in context of Excel 2000. Server has 1GB
> and (2) 800 Mhz CPU's; can anybody shed light on what the upper
> of working with Pivot tables in the context of Excel and the hardware
> described? Processing and opening of Pivot Table take 4EVER! Besides
> paring datasets down to bare minimum, what else should we look at re:
> performance tuning, etc?
> Thanks for taking time to respond to this question.
Sent via Deja.com http://www.deja.com/
Before you buy.