Performance - Best Practices

Hello,

I am having some pretty major performance issues with a cube I've built. My goal for this post is to give specifics related to the data and Analysis Services hardware to try and get some feedback from the community on whether or not I am having valid expectations performance-wise.

Hardware:

-Two dual-core Xeon 2.8GHz processors
-3GB RAM

Data:

The data is retail transaction data which is at the line item level. The most recent years have 50-60 million rows and I am partitioning by year. In addition, each transaction has related discount and tender (payment) data. The tender data has approximately 15-20 million rows per year while the discount data has approximately 10-15 million rows per year.

In my cube, each of these tables have their own separate fact group. I am pulling data from all three fact groups to create quite a few calculated members which build on each other to finally arrive at calculations such as GAAP Sales, sales for a particular product type, etc.

I believe I have my hierarchies and relationships set up correctly in my dimensions. In addition I have aggregations designed on each partition at about the 40% level.

Am I having reasonable expectations that data should be able to come back fairly quickly? I have some reports which take 10-15 minutes that bring back quite a lot of data for the whole company across the last two years. However, even some simple slicing and dicing in the cube browser can be slow. What is interesting to me is that even if I drop my time dimension on the rows axis before adding any measures or other dimensions, it can take 20 seconds or more to respond. All the while when reports are running or when I am browsing the cube myself, the CPU (all 8 in Task Manager [dual dual-core processors]) are spiking at 100%.

While I believe I can get some good responses from MS folks and MVPs for my own benefit, I think any thoughtful posts will benefit the community as a whole by setting performance expectations and pointing out potential pitfalls. I have found that there are not a ton of good resources out there on performance, so I appreciate any tips anyone can offer.

Thanks!
Todd

Dear Todd,

May be you can find useful information from this blog:
http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!907.entry

For best practices in SSAS 2005, you can find it in presentation from TechEd 2006 (BIN 316):
http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!906.entry

Best Regards,
|||

Hello. You have some good links to information posted here already regarding aggregations. I would only advice you to start with checking your attribute relations in your user hierarchies. This is mentioned in Chris Webbs blog.

Mosha has a good post here regarding MDX and performance: http://www.sqljunkies.com/WebLog/mosha/archive/2006/11/05/non_empty_behavior.aspx

You should also be aware of performance issues with using several measure groups in a cube. Here is a good blog post about this: http://prologika.com/CS/blogs/blog/archive/2006/06/28/1331.aspx

HTH

Thomas Ivarsson