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
2. You must then specify the database that you are using
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.
So we can now write a normal MDX query against our cube to test that everything is working correctly.
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)
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.
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