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