Making Session-Level MDX Script Assignments

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:
[Date].[Day of Week].MEMBERS ON 1
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…

3 thoughts on “Making Session-Level MDX Script Assignments

  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?

  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.

Leave a ReplyCancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.