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:
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.
On Wednesday May 15th 2024 I will give a free webinar on MSSQLTips.com about Microsoft…
I loaded some built-in sample data from Wide World Importers into a Fabric warehouse. You…
It was great being at dataMinds Saturday 2024 this past weekend. A great crowd of…
Today I was having a nice discussion with some colleagues about Fabric and pricing/licensing came…
I recently purchased and read the book Deciphering Data Architectures - Choosing Between a Modern…
A while ago I had a little blog post series about cool stuff in Snowflake. I’m…