In scanning through some recent discussions on the PowerPivot forums, I noticed this very nice posting by Marco Russo (http://sqlblog.com/blogs/marco_russo/archive/2010/01/26/memory-considerations-about-powerpivot-for-excel.aspx) . I strongly recommend it.
The only additional point I would like to make is that the RTM version of PowerPivot has an additional check on the in-memory database when saving to disk. The CTP3 (and earlier) versions of the add-in allowed unlimited memory use. With CTP3, this means that users could create very large files; we’ve seen some as large as 15 or 20GB. Unfortunately SharePoint has a 2GB maximum file upload size – thus it was easy for users to create datasets that could not be shared with other users. In RTM, we now check to see if in-memory database use exceeds 4GB. If so, we won’t allow the in-memory database to be saved to disk. With the 2:1 compression ratio that we normally see when building the ‘blob’ that is stored in the workbook, this gets the file size under the SharePoint 2GB limit.
<< BTW: and before, the geeks out there ask the obvious question, “Why does SharePoint have this limit if RBS and other technology allows >2GB blobs to be stored in SQL Server?” The answer is that unfortunately the internal file offsets in SharePoint are still 32-bit signed integers – thus the 2GB limit regardless of the storage mechanism used. This architectural limit will likely be with SharePoint for a long, long time >>
Enjoy. Good job, Marco!
Dave,
I’d like to know if we can overcome this limit by:
1) Saving a “small” version of data in SharePoint, for example by using a view which filters only a part of data from the data source
2) Changing the view on SQL Server by removing the filter condition
3) Refreshing the PowerPivot data on SharePoint with a scheduled action – at that point, the workbook should be processed by SSAS engine, but will it be saved on SharePoint?
In other words, is the limit just for upload or for internal storage?
Marco
Unfortunately the limit is the storage mechanism in SharePoint; not the upload process itself. If you look at the schema for the SharePoint content database you will see that the actual *file* contents are stored as a sequence of blobs — which the SP OM knows how to string together into the final results of the file when uploading/downloading. It uses 32-bit signed integers as offsets into the content — which is where the 2GB limit comes from. It is an artifact of the file at rest.
_-_-_ Dave
Thank you Dave.
Regarding memory considerations, I added a post about specific investigations on table denormalization. All feedbacks are welcome.
http://sqlblog.com/blogs/marco_russo/archive/2010/02/04/table-denormalization-study-in-powerpivot.aspx
[...] Read more… [...]