Hi!
I have an Excel file with around 1.5 million records linked to 4 Power Pivot tables but response times are poor when using Slicers.
Normally I get the info message "Running OLAP Query", and then need to wait from 20 - 90 seconds for the Pivot Tables to refresh. I assume that Excel is building an index/access path each time a particular slicer is selected. Are there any good tips to speed up responses time or is there a way that the OLAP query can be saved for reuse?
By the way, the PC I'm running this on has 16GB of Memory and a reasonably fast i7 processor.
Cheers,
Martin
Hi Martin,
Is the source data in a Linked Excel Table or is it coming from an external source direct to Power Pivot?
Also, are you running 64-bit or 32-bit Excel?
Mynda
Hi Mynda!
The data is imported in to Power Pivot from an Access database.
I'm running 32bit Excel - Our IT team have advised that the 64 bit version will not make a significant difference.
Cheers,
Martin
Hi Martin,
You can't work with 1m+ rows of data using 32-bit, it will always choke. You need 64-bit. It's the only way to work with this much data in Power Pivot.
"32-bit environment is subject to 2 gigabytes (GB) of virtual address space, shared by Excel, the workbook, and add-ins that run in the same process. A data model’s share of the address space might run up to 500 – 700 megabytes (MB), but could be less if other data models and add-ins are loaded.
64-bit environment imposes no hard limits on file size. Workbook size is limited only by available memory and system resources."
You can read more about the limitations here:
Mynda
Thanks Mynda for the link...............Martin
Well I've finally got around to updating my laptop - 16GB, SSD and 64 bit Excel 2016 but...............no noticiable difference 
The table I have has around 1.5 million records but when I click on any of the slicers, it takes ages to filter and update the 3 pivot tables - 20-30 seconds.
Any ideas from anyone?
Cheers,
Martin
Hi Martin,
My guess is that there are multiple iterators in your data model (an iterating formula is any "X" formula like SUMX for example). Even FILTER function is expensive, slicers. Optimizing the formulas will definitely speed up the model.
Here is a good article on this matter: slicers-and-pivot-update-performance
Thanks Catalin - It seesm that slicers could be the problem for me - Some of our main reports have 9 slicers, all of them are actually needed but even so the link give some good tips.
