MDX query performance is poor

We have an application that queries an SSAS 2005 cube to do fast multi-dimensional calculations about customers for a scoring application. The cube has 10 dimensions and five measures. All of the dimensions are low cardinality except for the customer one.

In short, the application loops through the customers and does the calculations it needs. The application processes 50,000 customers at a time (i.e., the customer dimension has 50,000 members in it). The typical MDX query includes the customer dimension, one or two other dimensions, and a couple of the measures.

The queries are executed in VB using an ExecuteCellset, but the same performance issues are found when we copy the MDX SELECT into a query window and run it.

The queries take several minutes each to run, and we need to make it faster. We have tried user based optimizations, aggregations, no aggregations, running queries in parallel, different numbers of customers in the cube, etc. with little or no improvement. Also, the application was significantly faster in SSAS 2000.

The problem appears to be related to caching, since the process page faults quite a bit. Since we are iterating through the data, and never hitting the same cell twice, caching does not really help us at all. We are looking at converting the cellset to a data reader to eliminate the bulk of the caching, but would rather not have to go that direction since it is a major rewrite.

Any ideas about how to speed this up would be greatly appreciated. It just does not seem reasonable that a query with 3 axes against a relatively small cube should take several minutes to run.

There are quite a few things affecting query performance and I am not sure I can go around talking about all of them.

In your case you should look at serveral things.

One. Where is the bottleneck for your queries? Is your system spending majority of time reading data from the disc to answer every individual query? If this the case, you should indentify the granularity your query operates on and build exact aggregation that is going to help answer the query.

If every query causes AS to read data on the lowest level of granularity you can also take a look at partitioning your data into several partitions.

If much of the time spent calculating complex MDX, you might take look at better way to write a query.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.