PowerPivot Scheduled Refresh – Twists & Turns, Pt 1

“Rico, we’ll need special tactical equipment. We’re going to face extreme peril. The private probably won’t survive.”

OK, with a new year it’s time to get serious on the blog. (Yeah, nothing says “serious” like cartoon penguins, but really, I *do* mean it). This is gonna kick off a series of inter-related posts that I’ve had in my head for a long time now. So buckle up, Rico.

Let’s say you’ve built a bunch of nifty PowerPivot workbooks. You’ve published them to SharePoint. And naturally, now you want to set things up so those workbooks automatically refresh, with the latest data, every night (or perhaps once a week).

As a recap, the simplest way to get started is to go to the PowerPivot Gallery view and click the highlighted button:

PowerPivot Gallery Schedule Refresh Button

Which leads to this page:

Refresh ASAP vs. Refresh Once

Most of the options in that scheduling page are quite straightforward. The only one that’s ever tripped me up is “Once.” “Once” does NOT mean “Now.” Instead, “Once” typically means refresh will occur tonight – either after business hours or after your specified earliest time.

If you want a refresh to occur now, you either need to specify a time (from today) that has already passed, OR don’t use “Once” at all.

Instead, you can schedule a refresh to occur Daily/Monthly/Weekly, and then check the “Also refresh as soon as possible” checkbox.

Personally, I think the lack of a “One time, right now” option is an oversight. As a workaround, I often found myself scheduling something to run once every 12 months just so I can check the “ASAP” checkbox.

Another Way to Turn “Once” into ASAP

At Pivotstream, we have found another way to schedule ASAP refreshes: we’ve changed the centralized definition of “business hours” to be so narrow that there are essentially no business hours at all. (Right now we have biz hours set to be 7-7:15 AM – I haven’t tried setting Start Time = End Time, but a 15 minute window is narrow enough for our purposes).

The downside of essentially disabling Business Hours, of course, is that it impacts everyone. That may not be a good option for your organization, as a refresh scheduled for “after business hours” will end up running sooner than expected. It works well for us at Pivotstream however.

For details on how to modify Business Hours, see Vidas’s item in the FAQ: https://powerpivotfaq.com/Lists/TGPPF/DispForm.aspx?ID=51

Behind the Scenes – the Basics

When a scheduled refresh does occur, a lot more happens than meets the eye. This rough diagram illustrates the steps behind the scenes:

(Click for Larger Version)

Notes on the diagram

I won’t repeat the descriptions on the diagram above, so spend a couple of minutes going over it ok? 🙂

With that done, I do want to call out a few points:

The timer service only checks about once per minute – so if you schedule an ASAP refresh but nothing happens immediately, just be patient, it will start shortly.
You can set different schedules per connection – I just discovered this recently, thanks to the watchful eyes of a training/consulting client of mine. George and company, you rock 🙂
The whole process will fail if the original file is checked out, or otherwise open for editing, during Step 6. So it’s extra important that you not leave your published PowerPivot workbooks checked out. I recommend editing locally on your desktop for this reason (and also because SharePoint is not terribly speedy for open/save of large files… and you should be saving often.)

Refresh is Actually TWO Refreshes

OK, one last piece of background in this post. When PowerPivot runs a refresh, it is refreshing the PowerPivot model – which corresponds to the contents of the PowerPivot window in Excel client.

In order for report consumers to see the latest and greatest data when they first navigate to it, however, the pivots in the Excel sheets themselves ALSO need to be refreshed. So there are TWO refreshes that need to happen – we’ll call the first one “PowerPivot Refresh” and the second one “Excel Refresh.”

PowerPivot Refresh and Excel Refresh