SSAS: Executing MDX Scope statements using SSMS

So usually when I want to test an MDX expression in SSMS I'll write a query with a "WITH MEMBER…" clause to create a query scope calculated measure. But sometimes you may want to test a scoped assignment before putting it in your cube script. The following steps show you how to do this.

1. Click on the button to open an new MDX window, enter your server name and then click on the "Options >>" button

image

2. You must then specify the database that you are using

image

3. Then under the "Additional Connection Properties" tab you need to add a "Cube=" parameter with the name of the cube that you wish to apply the scope to.

image

So we can now write a normal MDX query against our cube to test that everything is working correctly.

image

Then we can execute our SCOPE statements. Note that SSAS will only let you execute one statement at a time, so I had to execute each of the 3 lines below separately. The following scope simply overrides the value of the Sales Amount measure to always return a value of 1. While this is not something that you would do in a real world scenario, it makes it very easy to see if our scope has been applied. (note: a statement can span multiple lines, but each statement will be terminated by a semi-colon)

image

After running the 3 separate statements one at a time we can then run our original query again to see the affects of our scope assignment.

image

Note that this is a session based assignment which will only be visible to queries run from the same session. As soon as you close this session the temporary assignment will disappear If you open another MDX window or connect using Excel you will not see the affects of the temporary scope statement unless you use the "SessionID" parameter on the connection string (or in the "Additional Connection Parameters" for SSMS).

You can find the SessionID by running "SELECT * FROM $SYSTEM.DISCOVER_SESSIONS" and getting the SESSION_ID column

Print | posted on Wednesday, December 19, 2012 6:52 PM

Comments on this post

# re: SSAS: Executing MDX Scope statements using SSMS

Requesting Gravatar...
Good post! I haven't used this stuff since the MDX Script Performance Analyzer, and that was a long time ago.

It's also worth mentioning the CLEAR CALCULATIONS command that wipes all the calculations from the current cube:
http://msdn.microsoft.com/en-us/library/ms145603(v=sql.100).aspx
Left by Christopher Webb on Dec 20, 2012 12:04 AM

# re: SSAS: Executing MDX Scope statements using SSMS

Requesting Gravatar...
Hi,

you can use GO in between.
Than you do not have to run the
scopes separately.
Thank you


scope (measures.[sales amount]);
GO
this = 2;
GO
end scope;
GO
select measures.[sales amount] on 0
,[Product].[Product Categories].[Category].members on 1
from [Adventure Works];
Left by joschko on Dec 20, 2012 4:15 AM

# re: SSAS: Executing MDX Scope statements using SSMS

Requesting Gravatar...
Hey Chris, I have that planned for a follow up post. :)
Left by Darren Gosbell on Dec 20, 2012 9:10 AM

# re: SSAS: Executing MDX Scope statements using SSMS

Requesting Gravatar...
I want to create a report in BIDS(Business Intelligence Business Studio). I write a query in SSMS which is running smoothly in SSMS 2012. But when I try to execute the same query in Visual Studio 2010 BIDS it shows an error like "The query cannot be prepared: The query must have at least one axis. The first axis of the query should not have multiple hierarchies, nor should it reference any dimension other than the Measures dimension..
Parameter name: mdx (MDXQueryGenerator)"

What will be the solution for it.
Left by Piyush on Apr 24, 2014 3:34 PM

# re: SSAS: Executing MDX Scope statements using SSMS

Requesting Gravatar...
The solution is to either switch the query to DMX mode. Or change to using an OLEDB connection. If you use the standard Analysis Services provider in SSRS it will try to force you to only have Measures on the columns.
Left by Darren Gosbell on Apr 24, 2014 4:43 PM

Your comment:

 (will show your gravatar)