I found this strange behaviour when I was looking at SSAS traces of a specific reprot.
It seems that if you configure report parameters to get default values from a dataset, then the query of this dataset will be executed multiple times. This has a very negative impact on performance.
I've tried to change the advanced settings to "never refresh" but it didn't work.
Let say I have in my report 3 parameters all getting their default values via data set/MDX query:
- param1 - MDX1
- param2 - MDX2
- param3 - MDX3
What I've noticed is that e.g. MDX1 will be executed multiple times, first to get param1 default value and... I assume that additionally param1 will be reloaded 2x, when default values for param2 and param3 are set, i.e. MDX1 will be executed 3 times...
Well as far as i know this is general behavior that if you are having parameters which consume values from dataset fields. the query seems to run multiple times ( 2 times if you have 2 params using the fields of that datset).
But if you check the profiler, the subsequent executions of the datset will be against the cache.
Thank you for your question.
I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated.
Thank you for your understanding and support.
If you have any feedback on our support, please click here.
TechNet Community Support
You can check from this article http://blogs.msdn.com/b/jtupitza/archive/2011/11/23/improve-the-performance-of-reporting-services-parameterized-reports.aspx, especially under topic "How to Disable the "Cascading" Behavior of Multiple Parameters"
Microsoft Online Community Support
Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
i guess the issue stan mentioned is as described below.
There is a report which two parameters . lets suppose they are called p1 and p2.
we have a dataset(d1) which is getting me two fields called cur month and cur year
iam mapping my parameters to pick values from the above dataset . And in this case p1 and p2 are not dependent on each other ( no cascading effect)
Now if you run the report and check in profiler you will notice that the dataset (d1) query is executed twice.
Why is this happening and how can we stop this.