A Note About Performance
The performance gain from SQL 2005 Analysis Services can change the way users analyse their data. I have seen that when they receive answers in seconds rather than minutes they often use the extra time to dig much deeper into the data.
From a technical point of view, memory is very important for SQL 2005 and OLAP cubes. I have found that we need 2Gb on the machine with the cube to really start to perform. Much will depend on the machines and the network but here are a few indicative numbers.
Config : Dual core 3GHz with 2Gb memory
Average response times using a database with 8 million sales transactions :-
1. Connect to SSAS and create a Pivot Table programmatically â€“ 3 to 4 seconds
2. Select a page item – 0.1 to 0.2 seconds
3. Drag a single dimension to another pivot area – less than 0.5 seconds
4. Drill into a multidimensional hierarchy – less than 1 second
5. Drag one multi dimensional hierarchy into a pivot area containing another multi dimensional one â€“ 2-3 seconds
6. Analyse : To search through all dimensions looking for the contents of the active cell, decide on the type of analysis based on the dimension found, create the four OWC charts and display them in the form â€“ 4 to 5 seconds
7. With the OWC charts you do not use so many dimensions so I have not seen any responses that go over 0.5 seconds.
When you start to drag around three or four multidimensional hierarchies (especially using multi selected items) responses slow and memory starts to peak. Fortunately very few users get that far.
I believe it is much better to use Office 2003 on the client side because of the increased limits on the Pivot Tables and memory handling. I would suggest minimum 512K memory on the client although machines with 256K do work.