Hardware and Software Resources for Analysis Services

How can I determine what hardware and software resources my SQL Server 2000 Analysis Services instance requires?

First, because Analysis Services 2000 loads all dimension members and their member properties into memory at startup, and also loads a copy of this dimension information into memory during dimension processing, your hardware platform must support sufficient memory address space for these dimension members and their properties. In addition, if you're using dimension-based security roles extensively, the replica dimensions generated (as needed) for each role might also consume a significant amount of memory address space. Replica dimensions are the subset of the dimension members that a role has permission to view. Using proper server configuration in the boot.ini file, the maximum amount of memory the Analysis Services 2000 can address on a 32-bit version of Windows (Advanced or Enterprise Edition) is 3GB. If your dimensions are too large to fit into memory (at startup as well as during processing and when replica dimensions are used), you should use a 64-bit version of Windows for best performance (or use Relational OLAP—ROLAP—dimension and cube storage). Overlarge dimensions also mean that you must use the Intel Itanium platform. For more information on hardware and software resource usage by SQL Server 2000 Analysis Services, see http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ansvcspg.mspx.

Second, you need to consider using partitions if your cube is large. With appropriate partitioning, you need only process selected partitions rather than the entire cube, which can substantially increase processing performance. Partitioning requires SQL Server 2000 Enterprise Edition. If you have the appropriate hardware platform, Analysis Services 2000 can process 1GB of daily updates in an hour or less.

SQL Server 2005 Analysis Services uses a dimension member cache, which means dimension members aren't statically mapped into memory. This means that the 32-bit version of Analysis Services 2005 can handle large dimensions that have several million members without running out of memory address space.

In addition, for dimension-based security roles, Analysis Services 2005 doesn't load replica dimensions into memory. It creates one bitmap per role to mark secured members and eliminates memory drain. Furthermore, Analysis Services 2005 runs on both the Intel Itanium platform and the AMD Opteron platform.