Power Pivot workbooks need to be refreshed on a schedule. One of the coolest things about Power Pivot is that all that time that users had to spend gathering and scraping data from here, there, and everywhere is how seamless Power Pivot makes it. So why do all of that work to gather all that data to have it be static in your model. I don’t know anyone that wakes up every day thinking they just can’t wait to get into the office to click that magic refresh button. Fortunately, once you’ve loaded your Power Pivot workbook into your SharePoint Power Pivot gallery it’s easy to manage the day to day refresh schedule.
Start by opening up your Power Pivot workbook. You should see a little calendar icon at the top right of the viewing area:
If you click on it, it will open up a data refresh management screen:
First, check the Enable box. This will turn the refresh capabilities on for that workbook.
Then select how often you want the workbook refreshed. Daily, Weekly, Monthly, or once. (Personally, I’m not sure of the once option?) We’ll set this workbook to refresh every day. Leave it as Daily, and then we’ll change the right side to every weekday.
earliest start time tells the workbook when to refresh. If you select After Business Hours, SharePoint will use the business hours set by your admin and determine when to refresh based on availability. To me, this is like telling SSIS “yeah, just anytime you see fit between 9 PM and 6 AM, go for it. You kids have fun!” Uhh…no. Let’s change it to a specific earliest start time, and set a time of day.
email notifications tells the workbook who it should email if the unthinkable happens and it fails the refresh process. I usually put my email address in here, you might yours, the users, or your boss’. (I’d caution STRONGLY against that last one :))
credentials allows you to set what user you want to run the refresh process. You might set it refresh off of your credentials, a specific account already created in the Secure Store Service, or the account the SharePoint admin configured for this refresh process already.
Data sources lets you select what Data sources you want to refresh. If you have a Power Pivot workbook pointing to multiple locations, you might choose to refresh one at this time of day and the other at a different time of the day (on another refresh schedule).
Side note: Please forgive the fact that my blacked out server name looks like my 2 year old did it, I’m using the trackpad on my laptop while sitting in the airport to do it
Once you’re all set and you’re happy with your settings, click OK. You’ll be returned back to the Power Pivot gallery with a workbook scheduled for some refreshed awesome-sauce!
Intelligent SQL 