“Choose your next words carefully, PowerPivotPro, for they may be your last… well, your last before your next mtg anyway.”
Most pivot operations are “Updates”
Quick primer: when I worked on the Excel team, we described most pivot manipulations as “Updates.”
- Changing selections in a slicer or report filter
- Moving a field from rows to columns, or changing its order, etc.
- Adding removing fields to/from the pivot
- Drilling up or down (expanding/collapsing) within a field
- Toggling subtotals/grandtotals
- Etc. – I could be here all day trying to list them all
These are all Updates, even though that word appears (almost) nowhere in the UI. These are all generally very fast, especially with PowerPivot.
But a Refresh, well, a Refresh can take an hour in the right conditions.
What does Refresh do and when is it a lot slower than Update?
Refresh does two things that Update does not. The first is very straightforward, the second is a bit more mysterious.
Difference #1: Refresh processes EVERY pivot that shares the connection!
Let’s say you have 10 worksheets in your workbook with 4 pivots on each, all of them created using PowerPivot.
Then you right-click one of those 40 pivots and select Refresh. What happens?
All 40 pivots refresh, that’s what. Because all 40 are connected to the PowerPivot data in the workbook, they all share the same connection. Doesn’t matter what worksheet you are on.
So, if there is any reasonable complexity to your pivots, you are in for a bit of a wait, perhaps about 40x as much as an Update of one pivot.
Wait, so a Refresh is really the same thing as a Refresh All?
Notice on the Data ribbon in Excel, you also find a refresh button, and this one offers two options – refresh the selected pivot, or refresh everything. The Refresh option does the same as the commands shown in the previous section.
Refresh All is not precisely the same thing, but 99% of the time, it is. If you added a 41st pivot, and this one was connected directly to, say, an Access db, then that pivot would NOT be on the same connection as the others, and Refresh would affect either 1 pivot or 40, whereas Refresh All would touch all 41.
What’s that second difference you mentioned?
Refresh also includes something that Update does not, something we’ll call “field list refresh.”
Whenever Excel is processing an Update action, it generally assumes that the underlying data structure in the source (PowerPivot’s db, over in the PowerPivot window) has not changed. So if you added a new field, the field list will NOT pick that up.
That’s actually the reason why you see the three most common PowerPivot error messages – Excel’s “picture” of the PowerPivot db is stale.
The addin works VERY hard to prevent this ever happening, however. Notice how when I add a new calc column in the PowerPivot window, the addin gives me a warning on the Excel side?
When out of sync like that, the Addin turns Updates into Refreshes!
Whenever the addin detects that it’s in that state, you have a Refresh coming whether you want it or not. You see, the addin is “listening” for changes to pivots that are connected to PowerPivot, and if one of those gets an Update, and Excel is out of sync with the PowerPivot db, the addin intervenes and calls Refresh to get everyone synchronized.
Morals of the story
1) Don’t Refresh when an Update will do. I know, you went and changed a formula and you want to see how that impacts your pivots. It’s tempting to click Refresh. In small workbooks that won’t matter, but often, you are MUCH better off changing a slicer value or something like that.
2) Add calc columns in bulk before modifying any pivots. If you’ve got 5 calc columns to add in the PowerPivot window, consider doing them all at once before adding any of them to pivots, so you only pay the refresh once.
3) Try to finish building all measures before building lots of report sheets. The addin rightly assumes that every measure you write, is likely something you are about to add to the pivot, so it adds it to the pivot for you. And since the measure is new, Excel doesn’t know about it, so that’s right, Refresh. No big deal if all you’ve got is one pivot, but I have workbooks with forty. So plan ahead. I know, this is easier said than done. That’s why there’s a fourth trick.
4) Add measures in bulk using the Defer Layout Update trick. See this earlier post for a quick rundown.
One more moral
This whole Refresh vs. Update thing rears its head one other place, and with some pretty nasty implications. It happens on SharePoint, and is the topic of an upcoming post.