Improving Excel's Cube Performance

I am often asked to help with Excel's query performance. The cube is fast and efficient, but some Excel queries are slow due to the MDX that Excel is generating.

This is unfortunate, but there a couple of things you can do about it. Let me describe this particular issue. Essentially, you have a dimension with a large number of attributes. In Excel, you have crossjoined many of these attributes on rows, which you feel is reasonable since you have also added a filter so that the query will only return a small number of rows. For example, here is a query of the FoodmartFull database, where I have filtered on just one customer surname (Peacock) and asked for many customer attributes on rows. This query takes 9 seconds for SQL Server 2008 to execute. The reason it takes so long is that the MDX is asking for a crossjoin of all the attributes, at all levels. Note, this issue can be so bad that the results are not returned before timeout.

Read more...