SSRS and MDX: Think about Cell Properties

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.

mdxtuning03

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.

mdxtuning04

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:

mdxtuning01_coldcache

On a cold cache with only two cell properties:

mdxtuning02_coldcache

Warm cache with all properties:

mdxtuning01_warmcache

Warm cache with two properties:

mdxtuning02_warmcache

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.

Koen Verbeeck

Koen Verbeeck is a Microsoft Business Intelligence consultant at AE, helping clients to get insight in their data. Koen has a comprehensive knowledge of the SQL Server BI stack, with a particular love for Integration Services. He’s also a speaker at various conferences.

Leave a Reply

Your email address will not be published. Required fields are marked *