Chris Webb's BI Blog

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

Making Session-Level MDX Script Assignments

with 3 comments

You’re probably aware of the fun things you can do with MDX Scripts on your cube, such as making assignments, but you may not know that you can do the same things within a session from a client tool. Here’s how to do it…
 
1) Open MDX Sample Application. Unfortunately I’m not joking, you can’t use SQLMS because it relies on setting a connection string property and SQLMS doesn’t support this for AS2005. Hohum. 
 
2) For the purposes of this demo we’re going to use the Adventure Works database. So, edit the text in the ‘Server’ textbox, so that it reads:
MyServerName; Cube=Adventure Works
The new ‘Cube’ connection string property allows you to specify the cube in the database you want your statements to apply to.
 
3) Run the following query and look at the values returned:
SELECT MEASURES.[INTERNET SALES AMOUNT] ON 0,
[Date].[Day of Week].MEMBERS ON 1
FROM [ADVENTURE WORKS]
 
4) Open a new query and run the following:
(MEASURES.[INTERNET SALES AMOUNT], [Date].[Day of Week].&[1])=0;
 
5) Rerun the query in step #3. You’ll see that the value for the member [1] is now zero, and the All Periods value has changed accordingly.
 
Apparently this is how Visual Studio works when you use the MDX Debugger to step through each calculation in your MDX Script.
 
I was talking to (excuse the name-dropping) Reed Jacobson at PASS Europe the other week about doing financial consolidation in AS2005 and the limitations of non-leaf writeback, and he mentioned that it would be interesting to explore using this functionality for this particular problem; a similar idea came up in a ng thread I was involved in earlier in the year. You would let your users do their "writebacks" as session-level assignments and then when they were ready to commit them, push them up to the server and append them to the cube’s MDX Script. Definitely worth further exploration…

Written by Chris Webb

March 7, 2006 at 1:10 pm

Posted in MDX

3 Responses

Subscribe to comments with RSS.

  1. Speaking of Reed Jacobson, does he have new blog co-ordinates – the "Yukon BI by the Hitachi Consulting Yukon Team" link no longer works?

    Deepak

    March 9, 2006 at 12:23 am

  2. Actually, I did ask him about that. The answer is no, though, the Hitachi Consulting blog is no more – they\’re too busy doing actual work to do any blogging. It\’s a shame… luckily I much prefer blogging to doing any actual work.

    Chris

    March 9, 2006 at 10:59 am

  3. > luckily I much prefer blogging to doing any actual work.
     
    Me too!  Actually, I don\’t blog either come to think of it.
     
     

    Unknown

    March 9, 2006 at 11:26 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,190 other followers

%d bloggers like this: