I've a reasonably sized cube - eventually I aim to get it to around 200
million rows in the fact tables, with around a dozen dimensions. I'm
running Analysis Services 2000 on a reasonable multi-processor machine
with 4Gb RAM, so getting decent results using MOLAP. (Against a SQL
Server 2000 Standard Edition database on Win2k Advanced Server.)

The question is - what are the numbers I'm seeing when the Storage
Design Wizard quotes sizes to me?

If I design something for say 50% performance gain, it will quote me a
number of aggregates and a size.

But, having processed the cube, if I open the wizard again, it tells me
that there are existing aggregations and the size then quoted tends to
be about 50% higher than the number reported before processing.

In the docs it suggests that all the MOLAP aggregations are stored in
the SQL Server AS Data sub-directory, but when I go look in there the
sum of the file sizes is a way smaller (15-20% or less) than either of
the numbers given in the Storage Design Wizard.

Can anyone tell me (or point to me at a decent discussion as) to what
these numbers *actually* mean?

Apparently MOLAP hits problems when the MDB approaches the size of the
available RAM - but which number tells me when this is likely? I've run
processes where the size quoted by the Storage Design Wizard is >100% of
the machine's RAM, but I'm yet to see any particularly obvious
degradation in time taken.

Many thanks for any help.

I think the following section of the Analysis Services Performance Guide
should answer most of your questions
http://www.microsoft.com/technet/pro...cspg.mspx#EMAA

I don't know where you heard the story about AS MOLAP cubes running into
problems when their size approaches the amount of available memory - it might
be true of other tools, but not for AS.

HTH,

Chris
http://spaces.msn.com/members/cwebbbi

"chris harrison" wrote:

Chris Webb wrote:

Thanks for the link - I'll look into that.

I was told by a MSFT contact:
MOLAP in SQL Server 2000 caches the dimensions in memory so it can
require a lot of memory on the box. On a 32bit system you are limited to
4Gb and can’t use extended memory like you can for SQL Server. So
ultimately MOLAP will hit scalability limits.


The MOLAP issue isn't quite available memory, although given that I have
4Gb on my box, it is coincidentally the amount I have.

Yes, it's true about dimensions being loaded into memory on startup and that
being a scaleability issue. If you are expecting to build dimensions with
millions of members and/or large numbers of member properties and/or use
large numbers of distinct roles on dimension security, then you can run into
problems. The storage design wizard is concerned with the size of
aggregations, which is something else completely from the size of your
dimensions.

Chris

"chris harrison" wrote: