Power Pivot in Excel 2013: Troubleshooting - Problems When Reloading and Reading Data
Problem Descriptions
George UK
Source: Power Pivot forum
Our organisation is currently in the process of moving from Office 2010 to 2013 so I've been looking at upgrading some of our existing Power Pivot reports to the new version to ensure a smooth transition. Unfortunately, while some of our smaller reports (~5MB) appear to work okay under the new version, I'm having problems with the large reports (~1.5GB).
The Power Pivot tables in one of the problem reports consists of:
- One table imported from SQL Server (~9 million rows)
- Eight static tables containing lookup data (between 50 and 20K rows each)
When I open this report and click on any of the pivot table filters I receive the "Reading Data" message while it loads the Power Pivot data, which can take around a minute. Eventually the filter options are displayed, but when I select a new value for the filter and click Ok Excel appears to dump all the previously loaded data and begins to reload it from scratch resulting in the application freezing for around a minute (I can tell that Excel is dumping the loaded data as the memory usage of the running process drops from 3GB to 100MB).
This behavior does not occur under Excel 2010 and makes the report completely unusable as every time the user clicks on or applies a filter they are essentially reloading 3GB of data. Oddly I have determined that if I open the 2013 version of the report and then open the Power Pivot Window before accessing the pivot table filters then the data is not dropped/reloaded when I subsequently change the pivot filters (i.e. it reverts to the Excel 2010 behavior). Unfortunately this is only a temporary solution however as if I save, close and reopen the report the problem is back again.
Kevin M
Source: Power Pivot forum
We have the same problem with a file with 40 pivot tables that constantly is "reading data".
In Excel 2013, in Power Pivot, we have a table with around 500K lines and 30 columns that has a handful of calculated columns. From this table, we have around 80 different pivot tables pulling from the powerpivot table. We initially had this in excel 2010 and it worked fine. Once we migrated to 2013, we saw that the file is continually “reading data” as if any change to any one of the 80 pivot tables requires reading data for the other 79 tables. We then built this file from scratch in 2013 and see similar behavior. To address this problem we built a server with a 5.0GHZ processor and excellent I/O so that the “reading data” progresses quicker.
Lourson
Source: Power Pivot forum
I have a similar problem. When I go to "tables properties" and I try to filter the column of my data source, each time I click on a column it reloads all the data from the source, freezing everything in the process. With big data source (150 MB text file on the network is enough) it becomes nearly impossible to filter anything.
Bas K
Source: SQL Server Connect
We have a Power Pivot file of approx 10MB and 40 (power) PivotTables on 35 worksheets that freezes on opening in Excel 2010 x64 with Power Pivot add-in v10.50.2500. The "reading data" in the bottom right is moving much slower than normally. The memory consumption is typically around 200-300MB, but at a certain moment builds up until the maximum memory in the laptop (in our case 16GB) or server (in our case 64GB).
Victor M
Source: SQL Server Connect
I've detected that this is happening, at least in my case, when columns that plays as keys in tables relationships are bigint (int64) data types. When the manage Power Pivot window is opened, Excel begins to consume all available memory until system hangs.
Seems like the int64 data type is too heavy to operate as relationships key. The data model that I've been working and facing this, is about 45Mb in disk size, over 120.000 rows in the "facts" tables.
Nicky V
Source: SQL Server Connect
When trying to open my Power Pivot file Excel starts to consume more and more memory (I've seen limits of 9-10 GB) and eventually my laptop will hang on the point I think it is trying to swap to the page file. It keeps writing to disk, but nothing else can be done and the screen is frozen, although the cursor is still visible and movable. The workbook recently opened fine and is now just over 4MB. When only opening the file nothing happens, but when I click on a graph in the workbook, Excel starts to read the data and consume memory.
I had this issue yesterday already, but when copying the file to a new location, renaming and opening it, it worked fine for the rest of the day. When trying it again this morning it started to crash again when opening it for the first time. I'm running MS Office Professional Plus 2010 (64-bit) on Windows 7 (64-bit) with 8 GB RAM (and no SSD installed). My Power Pivot edition is 10.50.1600.1. I could update to the new version anyway. A colleague was able to open the file (he is running 32-bit Office with 16GB) with no problems. He sometimes does get the "unable to load the Vertipac engine" error, but can continue working with the file.
Workarounds
Bas K
Source: SQL Server Connect
We found a workaround by:
- pressing ESC immediately when seeing the first "Reading data"
- select a (random) slicer value
- pressing ESC immediately again
- select the slicer value again and let it read all the data
Most of the times the workbook is working again. When using this workaround the memory consumptions stays in between 200-300MB. At the end of this proces we do get a couple of messages "Initialization of the data source failed. Check the database server or contact ...". These messages don't really seem to have a negative behaviour on the functioning of the workbook.
Victor M
Source: SQL Server Connect
The best workaround that I found to fix it is
- empty the source database
- Refresh data from Excel data ribbon
- Open the Manage PowerPivot window
- Change bigint columns to text
- Reload data from the filled database
Nicky V
Source: SQL Server Connect
A colleague was able to open the file (he is running 32-bit Office with 16Gb) with no problems. He sometimes does get the "unable to load the Vertipac engine" error, but can continue working with the file.