SQL 2008 Writeback

When attempting to do a writeback in Excel 2010 the following error is returned:

"Data could not be retrieved from the external data source.
Error message returned by the external data source:

Server: The operation has been cancelled due to memory pressure."


I repeated the same action while looking at the resource monitor on the server that the cube was located. The memory linearly increased from 17% to 99% then started hitting huge seg. faults with varying memory consumption until the error was displayed in Excel.

So it's a memory problem.

And the interesting thing about this, SQL 2008 analysis services doesn't flush the memory until someone connects to it. (i.e. the memory on the server didn't drop back to 17% until I did a reconnect in SQL Server Management studio to the cube)

Apparently this cube needs to be designed better or the server needs more than 4GB of RAM.



Not that you wanted to know this...whoever decided to read it.