Using Excel as a front end to SQL2005 Analysis Services (part 3)
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.
Regards,
Carl
Carl,
“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.”
OK, does it mean that they use the data more in the decision process or that they just spend more time with it?
In my part of the world 1 GB RAM is rapidly turning into de facto standard.
Kind regards,
Dennis
Carl,
I should have read this before making my last post!
Those times are ok – not amazing for such a high spec PC.
I guess I really what PT to be effectively instant – that is with drag and drop type functionality.
Still good use of progess bars can make a big differance.
Thanks
Ross
Carl,
>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.
Do You get the impression that they use more the info in the decision process or they just spend the time with it?
Kind regards,
Dennis
Hi Dennis,
“Do You get the impression that they use more the info in the decision process or they just spend the time with it?”
Good question but very difficult to answer. Of course no matter what data is available it is no substitute for a good strategy. I think it depends on the person. There is a time to analyse and a time to act. These tools just make the analysis part easier.
With the operation that has been live since April I see more debates are based on facts rather than asumptions. If it takes you an hour to download data and produce a report you often do not bother.
Carl
Hi Ross,
The examples above are for Db with 8 million transactions, 1 million transaction headers and about 20 analysis tables (one with 10k analysis members and another with 6k). I picked this because this is about the biggest dataset I work with.
Another example has about 500k transactions and everything (apart from the connection) is sub second.
One aspect of SQL OLAP is that everything speeds up after you query it once. There are a few ways to speed things up further. For example calculating more agregates in the cube (the above is using default), pre selecting certain queries and using a connected refreshed PT.
Carl
Carl,
“…I see more debates are based on facts rather than asumptions.”
This is very good and I can only congratulate both You and the company.
Kind regards,
Dennis
Thanks Carl,
One day, when i get into SQL server I’ll look into what other DB’s run like. Interesting to see the speeds of these things.
My uncle is some sort of DB guru, he once told me that for every second he can save off a query, he’ll save the company like a millions quid or something (the figures might be wrong, but it was something impressive), every second counts!
Ross,
I’m a speed freak myself. When somebody says “it doesn’t matter if it takes a few seconds” I think back to 20 years of being a user. It mattered to me.
Another company just showed interest and I’m going to try loading their data up prior to presentation. I’m frightened about this one because looks big. The load files don’t fit on a 2GB USB. I’ll have to think about the speed issue more carefully.
Carl