Chris Webb's BI Blog

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

Tuning SSRS-Generated MDX Parameter Queries

with 33 comments

Sometimes you’ll find yourself in the position of building SSRS reports where you have parameters with a large number of available values. Using the Adventure Works cube as an example, if you were to drag the Customer attribute from the Customer dimension onto the filter area of the Query Designer for a simple query and check the Parameters box like so:

image 

…you’d end up with a parameter where you can choose any customer to filter on – and there are 18485 customers on that hierarchy.

If you right-click on your data source in the Report Data pane (in BIDS in SSAS 2008) and check the Show Hidden Datasets option, you can see the MDX query that BIDS generates to return the list of available values for the parameter query:

image 

Here’s what the query will look like for the Customers hierarchy for the Customer dimension:

WITH
MEMBER [Measures].[ParameterCaption]
AS [Customer].[Customer].CURRENTMEMBER.MEMBER_CAPTION
MEMBER [Measures].[ParameterValue]
AS [Customer].[Customer].CURRENTMEMBER.UNIQUENAME
MEMBER [Measures].[ParameterLevel]
AS [Customer].[Customer].CURRENTMEMBER.LEVEL.ORDINAL
SELECT
{[Measures].[ParameterCaption]
, [Measures].[ParameterValue]
, [Measures].[ParameterLevel]}
ON COLUMNS
, [Customer].[Customer].ALLMEMBERS
ON ROWS
FROM [Adventure Works]

On my laptop this query executes in just over 1.5 seconds. Not bad, you might think, for a query that returns a fairly large number of rows. But we can do better!

This query returns all customers on rows and three columns: the caption of each member, the unique name, and the ordinal of the level (which is used for indenting the caption of each member in the dropdown list for the parameter, so you can easily distinguish between members on different levels). These values are returned as calculated members, but they can also be obtained as member properties and this is the key to tuning the query. So, if you create a new OLEDB connection to the cube (ie you don’t use the built-in Analysis Services connection type but you create a data source that connects to the cube using the OLEDB connection type)…

image

…and then create a new dataset with the following query:

WITH
MEMBER MEASURES.DUMMY AS NULL
SELECT
{MEASURES.DUMMY}
ON COLUMNS
, [Customer].[Customer].ALLMEMBERS
DIMENSION PROPERTIES UNIQUE_NAME, MEMBER_CAPTION, LEVEL_NUMBER
ON ROWS 
FROM [Adventure Works]
CELL PROPERTIES VALUE

You’ll find you get almost exactly the same data back, although this time the query returns in around 0.2 seconds.

A few things need to be noted here. First of all, although MDX allows you to put an empty set on columns, in the query above I had to create a dummy calculated measure that returned null because otherwise the query didn’t return any rows from the OLEDB data source. Secondly, in the new query the All Member unique name and caption come out as nulls – that’s normal behaviour for flattened rowsets (which is what you get when you run queries through an OLEDB connection), unfortunately, and again something we’re going to have to work around ourselves. Thirdly, we also need to create a column with indented member names – the original parameter dataset did this using a SSRS expression in a calculated field – although in this case, where there’s only one level underneath the all member, we could probably skip this and not hurt usability.

To trap the nulls and make sure the All Customers member appears as a parameter option, you can use a calculated field on the new dataset with an expression like this:

=iif(
Fields!Customer_Customer_Customer_UNIQUE_NAME.Value is Nothing
, "[Customer].[Customer].[All Customers]"
, Fields!Customer_Customer_Customer_UNIQUE_NAME.Value)

And to generate the indented captions you can use an expression like this:

=iif(
Fields!Customer_Customer_Customer_UNIQUE_NAME.Value is Nothing
, "All Customers"
, " " + Fields!Customer_Customer_Customer.Value)

You then need to delete the original parameter dataset, point the report parameter to the new dataset and bind these two columns to it value and label fields. And lo and behold, you have a report that runs just over a second faster than it did before. This might seem like a lot of hassle to go through for such a small gain, but if you have more than one large parameter the time savings will add up and your users will notice the difference.

Written by Chris Webb

May 9, 2010 at 10:13 pm

Posted in Reporting Services

33 Responses

Subscribe to comments with RSS.

  1. Any idea why the performance gain is due to? Did you get a chance to look at the profiler? I was just thinking why there is so much of gain.

    jason

    May 10, 2010 at 3:56 am

  2. Profiler doesn\’t tell you anything here. I think it\’s just that the rewritten version goes direct to the formula engine to get the properties, whereas the original has the overhead of calculated members. But I\’m not really sure.

    Chris

    May 10, 2010 at 11:49 am

  3. If you want to edit the auto-gen\’d MDX instead of recreate, you can use the suppressing flag. You\’ll probably want to do this for a couple of reasons.1. If you simply EDIT the MDX and save…then go back to the Original Datasource that created the Parameter Dataset…it WILL overwrite your changes.2. I haven\’t tested this…but even if you use the "create a new parameter DS and delete the auto-gen\’d one"…again, going back into the original dataset and modifying may simply create another hidden DS for you. Whether or not that gets wired up automagically to your Parameter is irrelevant..you now have that super slow hidden Param DS sucking up resources again.So drop to Design and use the Suppressing flag on the dataset. Exact syntax here:http://blog.summitcloud.com/2009/12/suppress-auto-update-of-mdx-parameter-datasets-in-reporting-services-2008/

    David

    May 11, 2010 at 3:02 pm

  4. Thanks David – that\’s very useful to know!

    Chris

    May 11, 2010 at 3:38 pm

  5. For performance I usually run the parameter-MDX as part of SSIS after recalculating the cubes. I store the result in a SQL Server table and pulls the parameter list from the SQL-source. I guess this will always be the fastest way to load a parameter list in SSRS.

    Tomm

    May 15, 2010 at 6:52 pm

  6. [...] Tuning SSRS-Generated MDX Parameter Queries [...]

  7. Was just wondering about your comment on the query being faster maybe because it picks from the value from FE. Since the WITH clause is there, wouldn’t it be from the SE cache rather than the FE cache?

    Jason Thomas

    March 16, 2011 at 3:37 pm

    • No, what I meant is that the value is being calculated and returned by the formula engine. The presence of the WITH clause would prevent these values being cached after the query has finished, but I assumed in this article that all queries were running on a cold SE and FE cache.

      Chris Webb

      March 16, 2011 at 4:23 pm

      • Just had a look at the traces and I think the reason for the extra time is because
        – there is an extra activity in terms of querying the dimension which goes for Scenario 1 (you basically have to query the dimension to display the 3 calculated members) while in scenario 2, you dont have to query the dimension as null is being displayed
        – also, since 3 measures are there in the axes, the Serialize EventsSubClass would be 3 times more in scenario

        When I changed the first query also to display one measure like
        WITH
        MEMBER [Measures].[ParameterCaption]
        AS [Customer].[Customer].CURRENTMEMBER.MEMBER_CAPTION
        SELECT
        {[Measures].[ParameterCaption]
        }
        ON COLUMNS
        , [Customer].[Customer].ALLMEMBERS
        DIMENSION PROPERTIES UNIQUE_NAME, LEVEL_NUMBER
        ON ROWS
        FROM [Adventure Works]

        then the only difference in the trace activity was the query dimension for the one measure.

        Time taken in my laptop was
        1) Scenario 1 with 3 measures – 1.1 secs
        2) Scenario 1 with 2 measures – .9 secs
        3) Scenario 1 with 1 measure – .7 secs
        4) Scenario 2 with dummy measure – .5 secs

        which indicates that around .2 secs were being used for the Query dimension activity for each measure being displayed.

        Disclaimer : Test conditions might not have been ideal, but each scenario was tested 3 times each and all times, similar results were returned.

        Jason Thomas

        March 16, 2011 at 5:21 pm

  8. You could simplify a little bit more the query by using this kind of syntax even I’m not sure it will provide any performance benefit on a cold cache
    SELECT
    {[Measures].[Customer]}
    ON COLUMNS
    , [Customer].[Customer].ALLMEMBERS
    DIMENSION PROPERTIES UNIQUE_NAME, MEMBER_CAPTION, LEVEL_NUMBER
    ON ROWS
    FROM $Customer
    CELL PROPERTIES VALUE

    It returns exactly the same data and avoid the WITH clause.
    Furthermore it works perfectly well with both provider (integrated one and OLEDB, but your query is working as well with the 2 kinds of provider)

    Romuald Coutaud

    March 28, 2011 at 3:27 pm

    • Hi Romuald,

      The problem with this approach is that you can only query dimension cubes (ie use $DimensionName in the FROM clause) if you’re an administrator, and you probably don’t want to let SSRS connect to SSAS using an administrator account.

      Chris

      Chris Webb

      March 28, 2011 at 3:40 pm

      • Chris,

        I totally forgot this limitation !
        For sure it’s not an option in the “real world”.
        Therefore, you can use an intermediate version of the query and skip the dummy measure :
        SELECT
        {}
        ON COLUMNS
        , [Customer].[Customer].ALLMEMBERS
        DIMENSION PROPERTIES UNIQUE_NAME, MEMBER_CAPTION, LEVEL_NUMBER
        ON ROWS
        FROM [Adventure Works]
        CELL PROPERTIES VALUE

        Romuald Coutaud

        March 28, 2011 at 5:07 pm

      • But there’s another problem with this query – which is why I didn’t use this approach in my post! If you use this query with the SSAS data source it doesn’t return the dimension properties; and if you use this query with the OLEDB data source then it doesn’t return any rows. The reason why I used a dummy measure is to get around this second problem.

        Chris Webb

        March 28, 2011 at 5:21 pm

  9. Chris,

    I’ve tested it with the SSAS data source. I’ve added the 2 calculated fields to fix the All Customer UniqueName and indented Caption as you explain in your post and it works without any problem. I use the 2008 R2 RTM version (with CU6) maybe you encounteur the missing property behavior on another version ?

    HTH

    Romuald

    Romuald Coutaud

    March 28, 2011 at 5:37 pm

    • I’m using R2 but not CU6 – maybe it’s been fixed, then.

      Chris Webb

      March 28, 2011 at 8:53 pm

      • Hi Chris,

        I’ve tested the query on R2 RTM (w/o any CU) on a another machine and it works. In fact, It works also on SSRS 2005, once again with SSAS datasource, so I’m confident it will also work on 2008.
        What I’ve noticed, is that the DIMENSION PROPERTIES must contain UNIQUE_NAME (and not MEMBER_UNIQUE_NAME as query designer put by default) and in this case you will have one field named as well which contains the correponding data in addition of the Caption one.

        Romuald Coutaud

        March 30, 2011 at 9:35 pm

  10. Hmm, that’s interesting – thanks! You’re right, I’d been using MEMBER_UNIQUE_NAME when testing…

    Chris Webb

    March 30, 2011 at 11:36 pm

  11. Hi Chris,

    I am facing a problem with SSRS parameters passing for summary to detail report (implementing drill through functionality) . My datasource is SSAS Cube.

    I checked in the sql profiler to troubleshoot the MDX script error. I noticed that the error is not constant. I suspect it might be with the sequence in which the parameters MDX scripts excuted to load the dataset for the paramters (i have 6 parameters in total)

    Please advice…

    Regards,
    Sandesh

    mdxdna

    April 2, 2012 at 4:43 pm

    • Can you give more details about what you’re doing exactly, and what the error is?

      Chris Webb

      April 2, 2012 at 9:08 pm

      • Thanks for reply. Actually, I have 2 dimensions (i.e geography and time). geography dimension as country, ,state and city roll-up. Time dimension as Year, Quarter and Month roll-up.
        I have parametrized these roll-ups in the mentioned sequence. The Summary report works perfectly fine. And when I navigate to detail report from summary by passing the parameters. I am getting error like “Query execution failed for dataset “Month” Parser: The syntax for ‘ ‘ is incorrect.”

        When, I extracted only the MDX Script from the sql profiler trace. I noticed that when detail report execution the MDX script to load Month’s parameter’s dataset is executed before Quarter Dataset.

        Regards,
        Sandesh

        mdxdna

        April 2, 2012 at 10:45 pm

      • I’m not really a SSRS expert and I don’t know the answer to this one – have you tried asking on the SSRS MSDN Forum?

        Chris Webb

        April 4, 2012 at 10:31 pm

  12. […] par l’éditeur de requête avec une version revue et corrigée de ce que propose Chris Webb http://cwebbbi.wordpress.com/2010/05/09/tuning-ssrs-generated-mdx-parameter-queries/ On remplacera donc ça […]

  13. I was hoping this would work with the Tabular Model, but it doesn’t seem to. As results, I only get the member caption and the measure columns. No unique names or levels. Does tabular model not recognize member properties?

    Elaine

    December 9, 2013 at 8:00 pm

    • It works ok for me – can you try using a DMX query instead of an MDX query in SSRS?

      Chris Webb

      December 9, 2013 at 9:56 pm

      • With MDX, I got the UNIQUE_NAME to appear, but no luck with the LEVEL_NUMBER

        Elaine

        January 23, 2014 at 5:27 pm

      • Hmm, which version of SSRS are you using?

        Chris Webb

        January 23, 2014 at 5:36 pm

      • Visual Studio 2010, SSRS 11.0.3000.0

        Elaine

        January 23, 2014 at 7:15 pm

      • Hmm, well sorry, I have no idea what’s going on here I’m afraid.

        Chris Webb

        January 23, 2014 at 8:12 pm

  14. Hi,

    Good evening. I have some question in SSRS.

    Question :–

    I am using SSRS.

    I have below requirement from the client side.

    1.) In report viewer , We want filter the result set of report viewer from user end like excel sheet filter.

    Is it Possible ?

    2) In Report view , I am showing only five column from dataset into table but While export reports to excel then I want to export all coumns of dataset.

    Dataset has 25 columns.

    3.)Generate a drop down list from report view result set column and after that apply filter on that.. using SSRS

    I want to make a drop down list from the report view table result set .

    I want to column name as value of drop down list .

    After that I want to filter result set on selected value filed + operator

    like,=,>, + text box value.

    expression like —

    ddl value + operator = 100;

    salary(ddl value) >= 2000(text box value) .

    Is it Possible ?

    4.) show report header above the report parameters in report view using SSRS?

    Please suggest.

    Mukesh Bhagat

    August 19, 2014 at 3:21 pm

    • Hi Mukesh, to be honest I’m not the best person to ask these questions. Can I suggest you post them on the SSRS MSDN forum?

      Chris Webb

      August 19, 2014 at 9:44 pm

      • Thanks for kind info…
        I have already posted above question at SSRS MSDN.
        I get reply on this. but I still have one doubt .
        show report header above the report parameters in report view using SSRS?
        is It possible?

        mukeshbhagatat

        August 19, 2014 at 11:15 pm

      • No, I don’t think so

        Chris Webb

        August 20, 2014 at 8:49 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,310 other followers

%d bloggers like this: