Executing Multiple MDX Statements

MDX 1 Minute
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:
 

SELECT MEASURES.MEMBERS ON 0 FROM
[Adventure Works]

GO

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…

Published by Chris Webb

My name is Chris Webb, and I work on the Fabric CAT team at Microsoft. I blog about Power BI, Power Query, SQL Server Analysis Services, Azure Analysis Services and Excel.

Published

7 thoughts on “Executing Multiple MDX Statements”

  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

    Reply

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

      Reply

  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]
    GO
    DRILLTHROUGH MAXROWS 1000
    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]
    GO

    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????

    Reply

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

      Chris

      Reply

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