Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Speeding up the Query Parameters Dialog in the SSRS Query Designer

with 3 comments

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:

image

The query designer would run an MDX query like this:
image

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:

  1. Build an aggregation for the above query, or
  2. 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:
  3. 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

Written by Chris Webb

January 22, 2009 at 1:28 pm

Posted in Reporting Services

3 Responses

Subscribe to comments with RSS.

  1. 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\’.

    Unknown

    March 27, 2009 at 12:09 pm

  2. You have to add the MDX to the MDX Script of your cube, ie on the Calculations tab of the Cube Editor in BIDS

    Chris

    March 27, 2009 at 6:19 pm

  3. how can i use the parametre ProductCategory in my MDX query?

    said

    February 24, 2010 at 11:01 am


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 3,072 other followers

%d bloggers like this: