Chris Webb's BI Blog

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

Committing Writeback Transactions in MDX

with 4 comments

Today I answered a question on the MSDN Forum about writeback by saying that it was only possible to commit a writeback transaction using code. Turns out I was wrong:
If you execute a BEGIN TRAN statement in MDX before you do your UPDATE CUBE then execute a COMMIT TRAN statement, then the writeback changes get committed to the cube; alternatively you can execute a ROLLBACK statement and any changes you made with UPDATE CUBE are lost. This works for AS2K as well as AS2005.

Written by Chris Webb

February 9, 2007 at 10:29 pm

Posted in MDX

4 Responses

Subscribe to comments with RSS.

  1. Hey Chris,
    Came across the posting that you refer to above, and thought you might be able to advise me on something. I have an AS2005 cube, and I\’ve got a writeback partition to support user forecasting. It\’s a trivially simple implemtation, and the back end works fine – I can execute the code in Managment Studio and the new value appears. So far, so good.
    Now all (ALL! HA!) I have to do is give the users an appropriate tool to enter the forecasts with. I thought that  Excel 2007 plays so nicely with MSAS, that I would be able to use that,  but after some fairly serious Googling, I see that it doesn\’t and the Add-in that worked with 2003 doesn\’t work with 2007. [We\’re using Excel 2007 exclusively]
    I thought Reporting Services action, but there\’s nowhere to enter the data.
    Do you have any ideas how to write back to the cube? How are you doing it? How is anyone doing it?
    Any advice gratefully received


    August 13, 2007 at 4:43 pm

  2. You need to use a front-end tool that supports write-back and luckily almost all do. You can find a list of third-party Excel addins here:


    August 22, 2007 at 4:22 pm

  3. Hi Chris,
    I use this following mdx statement,
    UPDATE CUBE [Adventure Works DW] set ([Measures].[Sales Amount Quota],[Dim Employee].[Dim Employee].&[285],[Dim Time].[TimeKey].&[1]) = 669002.0000USE_EQUAL_ALLOCATION
                              COMMIT TRAN
    but it give back me an error:
    Executing the query …Query (3, 1) Parser: The syntax for \’UPDATE\’ is incorrect.Execution complete
    i use sql server 2005 with sp2 on window xp.


    November 4, 2008 at 3:42 am

  4. You could alternatively writeback data using VBA and updating the base data and then refreshing the cube again. A note on the methodology is posted here …


    August 22, 2009 at 2:04 pm

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your 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


Get every new post delivered to your Inbox.

Join 4,002 other followers

%d bloggers like this: