I stumbled across a small MDX performance tuning trick when using SSRS reports (or any other client tool where you can edit the MDX). When you create the MDX query using the designer, it will append some cell properties at the end of the query. Basically it’s just metadata about the cells of the result set.
An example of what those values can contain:
- Value: 6360
- Formatted value: € 6.360,00
- Format string: \€ #,##0.00;\€ -#,##0.00
Some of them are quite useful, especially the FORMAT_STRING property as you can use it to force SSRS to display the formatting you configured in the cube. Be sure to check Jason Thomas’ excellent blog post on this: Using SSAS formatting in SSRS. FORMATTED_VALUE seems very useful, but since it’s a string value SSRS doesn’t know how to aggregate it, so using it in a tablix might result in an error.
However, most likely you won’t need most of those cell properties. So you can leave out the cell properties that you don’t need and it saves you some data that has to be retrieved from the cube and sent to the SSRS report. Don’t expect a 10-minute query to suddenly run under 5 seconds, but every little bit counts to make your reports as fast as possible.
I tested a simple query using MDX Studio, where you can easily wipe the cache of the cube.
It also has a nice Perfmon pane showing you some performance metrics about the executed query.
Here’s the query on a cold cache, with all cell properties:
On a cold cache with only two cell properties:
Warm cache with all properties:
Warm cache with two properties:
On a cold cache we got about 14.9% performance increase. On a warm cache about 16.5% increase. Quite a nice result for just deleting a couple of lines at the end of the MDX query. The more measures you include in your query, the bigger the impact that this trick has. If you only have one single measure, you probably won’t notice much difference.
------------------------------------------------
Do you like this blog post? You can thank me by buying me a beer 🙂
