Boyan Penev recently blogged about the virtues of setting default parameters and he’s right: it’s a good idea to set parameter defaults. However I was with a customer yesterday who were waiting for a really long time to even open the Query Parameters dialog in the SSRS query designer. I was a bit curious as to why this was, so I ran a Profiler trace. What I found was then when I opened the dialog when there was a default selection made, for example:
The query designer would run an MDX query like this:
Clearly it was running queries to check the parameters were valid but in this case (although not in other instances) it was forgetting to add an empty set on the opposing axis, as per Mosha’s recommendations here, and therefore bringing back measure values by accident. Now this doesn’t matter for most cubes but my customer had a very, very large cube, their default measure was on their biggest fact table, and hadn’t built any aggregations that this query could use. And so, not surprisingly, the query ran very slowly and editing the reports was a nightmare.
There are two possible workarounds here:
- Build an aggregation for the above query, or
- Create a new, hidden calculated measure that returns null and make that the default measure on the cube. Here’s what to add to the MDX Script to do this:
CREATE MEMBER CURRENTCUBE.MEASURES.UseAsDefaultMeasure AS
NULL, VISIBLE=FALSE;
ALTER CUBE CURRENTCUBE UPDATE DIMENSION Measures,
DEFAULT_MEMBER=[Measures].UseAsDefaultMeasure;
Interestingly the same dialog also seems to generate some errors from the queries it runs which I think reinforces the argument for someone to go back to this code and do some work for the next hotfix. The errors seem to happen when you have a parameter with no default value set – the MDX error message is:
Query (1, 9) Parser: The syntax for ‘on’ is incorrect.
Query Text:
SELECT on 0 FROM [Adventure Works]
It doesn’t really matter from a performance point of view, but it’s not very tidy. Anyway, I’ve opened an item on Connect if you’d like to vote on this:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=403736
Where do you execute the MDX query? If I add it to my Dataset query in the Report Designer I get an error…\’failed to detect whether this is an MDX or DMX query\’…and if I try to execute it in SSMS while connected to an Analysis Services instance I get the error \’no cube specified\’.
You have to add the MDX to the MDX Script of your cube, ie on the Calculations tab of the Cube Editor in BIDS
how can i use the parametre ProductCategory in my MDX query?
I am not sure if this is possible but I would like to know how to execute this script only when Specific dimension is selected
ALTER CUBE CURRENTCUBE UPDATE DIMENSION Measures,
DEFAULT_MEMBER=[Measures].UseAsDefaultMeasure;
Can I use a CASE Statement? If yes how or can I do this? I dont want this applied to all the dimensions in the cube. I will appreciate any help.
Thank you.
Tonia
Hi Tonia, no this isn’t possible. Can you tell me why you want to do this?
I am doing this because after adding this scrip to the cube, when I try to apply filter to other smaller dimension fields, the filters dont work unless I select a measure. If I take off the alter script, the filters work for the dimension but then I have this memory issue with this one dimension.
Thank you.
Tonia
It sounds like your only option is to redesign this large dimension to avoid whatever memory problem it is you’re having, unfortunately.