Speed up MDX query preparation in Reporting Services
Tweet
We often need to build some advanced functionality into our reports and since the Reporting Services query designer does not provide a very advanced way to create MDX statements, our only option is to get our hands dirty and write some code ourselves. One of the things we must consider is the time that the query designer needs to validate our syntax, execute the query and determine what fields to be created based on the data set it receives from Analysis Services.
When we specify a number of query parameters, we have the option to give them default values:
If we do not do this, the query will be executed with the default dimension member instead of the a specific value as a parameter, which can be slow and unnecessary in the usual case where the default member is the (All) member. Instead, we should always specify a value which is to be used for limiting the sample data set. Ideally, is should be small but not empty. If the parameter is too limiting and the MDX query does not return any values the data fields will not be populated.
Specifying default values speeds up development time, especially when using complex cubes, where Reporting Services queries can be slow as its dimension cross joins can be very big.
Chris Webb also blogged in regards to this issue. I strongly recommend you read his post.
Related posts:
- Reporting Services Styles in a Database Table
- Passing unCONSTRAINED Set and Member parameters between reports in Reporting Services
- Advanced javascript in Reporting Services
Loading ...
Hi Boyan,
Quick one on this… I use this approach myself, and thought I'd add a refinement that I've discovered in SSRS 2005. When you first create the default value, SSRS will produce a data set to fetch the default member from the SSAS DB. When you're over-riding the default parameter value in the MDX designer with a default parameter specified by the report shell itself, you can just delete the autogenerated data-set.
I've found that deleting the dataset increases report performance considerably when you're passing a member unique name to a StrToMember function in your MDX report query.
As I said – haven't checked this out in SQL 2008 yet, but I'd be willing to bet it's the same.
Sounds good. A few other recent related posts:
http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!4123.entry
http://prologika.com/CS/blogs/blog/archive/2009/06/08/suppressing-auto-generation-of-mdx-parameter-datasets.aspx
http://richardlees.blogspot.com/2008/09/reporting-services-2008-overwrites-mdx.html
The best solution is it add true in the report XML file. It works well.