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
     
    Sam

    Sam

    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:
    http://www.mosha.com/msolap/util.htm#ExcelAddIns
     
      

    Chris

    August 22, 2007 at 4:22 pm

  3. Hi Chris,
     
    I use this following mdx statement,
     
    BEGIN TRAN
     
    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
     
     
     
    why?
    i use sql server 2005 with sp2 on window xp.

    Silva

    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 …http://piglings.blogspot.com/2009/08/excel-2007-olap-writeback-ssas-2008.html

    Vikram

    August 22, 2009 at 2:04 pm


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,083 other followers

%d bloggers like this: