Executing Multiple MDX Statements

Just a quickie, but here’s something I didn’t know was possible – open an MDX query window in SQLMS, connect to Adventure Works and paste in the following:

[Adventure Works]


SELECT [Measures].[Internet Sales Amount] ON 0,
[Date].[Day of Week].MEMBERS ON 1
FROM [Adventure Works]

Apparently, you can string multiple MDX statements together with the GO keyword in SQLMS (but only SQLMS – this isn’t valid MDX, it’s just SQLMS parsing the query text itself)
Very useful…

7 responses

  1. Chris, do you find this to be reliable? Running on my dev box with very little load I am finding that this use of the GO keyword is crashing the SQL Server Analysis Services service quite frequently via SSMS and always when using an Execute SQL task in SSIS

    • I’ve always found it to be reliable. However, as I said in the post, this is not a feature of MDX! This is a feature of SQL Server Management Studio only that allows you to run multiple queries and nothing to do with MDX or SSAS, which is why it won’t work in the Execute SQL Task. I don’t know why it’s having this effect on the SSAS service though.

  2. Hello Chris,
    I’m trying to run a drillthough in SSRS and as I’m using multimember filters, I need to create a subcube. The dataset looks like this:

    create subcube [Cube_eCC_SSCDW] as
    Select ({[Dim Ticket].[Ticket Business].&[RT],[Dim Ticket].[Ticket Business].&[MA]}
    ,[Date ticket created].[Date Month].&[2013-05],
    [Dim Ticket].[Ticket Category Solver Department].&[SA-FO],
    [Dim Ticket].[Ticket Country Grouped].&[France],
    [Dim Ticket].[Ticket Category Solver Unit].&[SAC],
    [Dim Ticket].[Ticket Requesting Company].&[Partner]) on 0
    From [Cube_eCC_SSCDW]
    Select ([Measures].[Ticket Number-of-tickets]) on 0
    From [Cube_eCC_SSCDW]
    RETURN [$Dim Ticket].[Ticket Code],
    [$Dim Ticket].[Ticket Title],
    [$Dim Ticket].[Ticket Company],
    [$Dim Ticket].[Ticket Grouped],
    [$Dim Ticket].[Ticket Affected Company], [$Dim Ticket].[Ticket Business]

    drop subcube [Cube_eCC_SSCDW]

    …. but a dataset in reporting services does not allow to run multiple statements or at list not with “go” statement. Do you know how to do that in SSRS????

    • Hi Jon,

      You can’t run multiple statements, no. I think you’ll need to use a subselect or a where clause in the MDX query used in your DRILLTHROUGH statement instead.


      • Hi Chris,
        You can not use a subselect because (unbelieveable!!!) when you use a subselect the result of the drillthrough is the same as if you wrote the query against the whole cube insteed the subselect. Why? No idea, but it doesn’t work although the select throws the expected result.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: