Committing Writeback Transactions in MDX

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.

4 thoughts on “Committing Writeback Transactions in MDX

  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

  2. 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.

Leave a ReplyCancel reply

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