November 19, 2013

How to address the PowerPivot memory error on large files

PowerPivot is a great tool to run in-memory analytics on diverse datasources right from Microsoft Excel extending the familiar pivot table interface. Ease of use and speed on datasets up to a few hundred thousand rows make it a perfect solution for analyses whenever upgrading or uploading new data sources on a data warehouse is too time-consuming or too expensive.

It is so easy to import new data into a PowerPivot file that teams often end up with gigantic files of hundreds of megabytes. Unfortunately the architecture of the tool requires to load up the full database in memory in order to run any query on it. Which brings us to the increasingly common:

Memory error: Allocation failure : Not enough storage is available to process this command. The operation has been cancelled.

Solutions to the problem are unfortunately not many:

  1. Upgrade to Excel 64-bit if you haven't already
  2. Buy more RAM for your computer
  3. Buy and setup a server running Microsoft SQL Server to offload computations from PowerPivot to a dedicated server

We built MegaPivot to address the problem from an architectural point of view. Our backend platform is built on Google Cloud Services to allow you to scale your analyses to billions of rows and still run in seconds pivot table or SQL-like queries. All analyses can be run from a web-based application with no software installation needed.

You can upload your data into MegaPivot very simply from any Dropbox folder and building your analyses collaboratively with your colleagues with an intuitive drag-and-drop interface, a simple version control system and easy data export functionalities. No more monster excel files to be exchanged via email and memory errors.

MegaPivot is a web-based big data analytics application built on Google BigQuery infrastructure.

Build pivot tables on billions of rows in seconds.
Easily import CSV files of any size directly from your Dropbox folder.
Share your reports with your colleagues.

Sign Up with Google