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

Your comment:

 (will show your gravatar)