Chris Webb's BI Blog

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

Executing Multiple MDX Statements

with 6 comments

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…

Written by Chris Webb

August 13, 2006 at 9:15 pm

Posted in MDX

6 Responses

Subscribe to comments with RSS.

  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

    Maxwell Hill

    October 26, 2012 at 12:11 pm

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

      Chris Webb

      October 26, 2012 at 12:25 pm

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

    Jon

    June 27, 2013 at 10:26 am

    • 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

      Chris Webb

      June 27, 2013 at 10:29 am

      • 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. :(

        Jon

        June 28, 2013 at 8:19 am

      • Bizarre… What about a WHERE clause though? That should work.

        Chris Webb

        June 28, 2013 at 9:17 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,131 other followers

%d bloggers like this: