Auto-Refresh PowerPivot Table in Excel?

Question

Hi,
Using Excel (no SharePoint), the only way it seems I can refresh the PowerPivot data is manually choosing the Refresh option within PowerPivot. Is there to be another way designed? Basically, I want to set the properties of Excel workbook so that I open it and it refreshes the data from PowerPivot automatically and then refreshes the pivot table as well.
Thanks!

Answer 1

Using client, you cannot trigger automatic refresh.
Automatic refresh/update mode can be set only for linked tables.

However you can publish your workbook to Sharepoint and schedule refresh which refreshes your data automatically based on your schedule.

Thanks,
Deva [MSFT]

---

Deva

Answer 2

Is it possible for Microsoft to consider building this refresh from Excel for PowerPivot??
Without it, the Execl "Refresh" button is basically worthless when source is PowerPivot table.
Rather, I would suggest that the feature request be to make the Excel Refresh functionality refresh PowerPivot source.
I do hope that this will be considered... if there is some other way I can try to get this considered other than this forum, please let me know.
Thanks.

Answer 3

Excel Refresh button is basically refreshing the pivot table values after the source data has been refreshed.
Note: It's a two step process even in pre-PowerPivot world,
a) Source data refresh
b) Pivot data refresh

Normally user won't see or have permission to do source data refresh all they can do is pivot data refresh.

With powerpivot, your source is right inside excel and so you have the capabilities to refresh your source.
Thus you have two steps process to refresh your values.

Hope this clarifies your question.

Thanks,
Deva [MSFT]

---

Deva

Answer 4

Deva,

Please consider from a special kind of end-user -- specifically the kind of user who presently loads an Excel 2007 workbook which some other analyst built for them. This user is therefore trained to open file and click refresh, whereby it refreshes the data from MS queries and/or cube information. I have built many powerful real-world solutions for CFO's, CEO's in this very scenario... they open the file and click refresh... once click.

As I start to get to know the Excel2010/PowerPivot world, I was starting to envision creating even more powerful visualizations for these very same types of users. But, as I have discovered, the user would have to refresh in two places -- PowerPivot and Excel. And, moreover, it's the first refresh click I would worry about for these kinds of users -- specifically, they would be pretty blown away by just launching the PowerPivot environment to refresh there. But, let's just say that they perform the refresh successfully there, followed by a second refresh back in Excel... at the end of the exercise, they're going to ask me "how come I had to refresh twice? ... I only had to refresh once in Excel 2007".

So, my claim is that there are many many non-technical users in this camp, who presently load up a workbook someone else built for them in Excel 2007 and click 'refresh' once -- and many of these users will get lost in the shuffle, or worse... abandon using the workbook altogether if they were now expected to perform two refreshes -- in PowerPivot and then in Excel. I think that this risk could be avoided by Microsoft if they were to architect the ability to refresh in one click both the PowerPivot source and subsequently the Excel pivot table.

I simply do not know how to make my point any more clear -- but it is such an important topic for my needs, that I will keep trying, if that is what is needed to get my point across. And again, if there is some other way I can communicate my request or some other audience to whom I can communicate the idea, please let me know.

Thanks.

Answer 5

xpdnt1, I do exactly the same stuff as well, but I think that you've missed the point of how PowerPivot was mainly designed to be deployed. I agree that if you share the PowerPivot workbook directly with other Excel users, you will encounter the issues you mentioned. It also means that all these end users must have PowerPivot installed to use the workbook. However, PowerPivot was designed to be shared centrally, where end users don't need to install the add-in to use the data, or have to worry about the PowerPivot environment at all. On the central server, you would set up periodic automatic refreshing, which is the same process that is done today for refreshing data marts, data warehouses, OLAP cubes...End users usually don't have permission to update these sources directly. As Deva mentioned, the Refresh mechanism in Excel was designed to refresh data from the source it's directly connected to - in this case the PowerPivot data. It's the same as connecting to an OLAP source. To be able to refresh a non-directly connected source is an entirely different matter, and would require a completely different refresh mechanism. At that point, it's not a PowerPivot issue but an Excel development issue. The refresh pipeline can get very complex depending on how the data infrastructure is set up and there are many security issues involved.

There are many reasons why PowerPivot was designed for server deployment (SharePoint) and the most recent article on the topic is other here http://powerpivotgeek.com/2009/12/02/why-powerpivot-for-sharepoint/.

Answer 6

Colin - great answer and description.  Unfortunately customers/users have this nasty habit of wanting things that work they way they want... ;-)

Answer 7

I May get shouted at by the good people at Redmond but Marco Russo blogs about an automation solution (unsupported) at http://sqlblog.com/blogs/marco_russo/archive/2011/09/08/automate-powerpivot-data-refresh-in-excel.aspx that was developed by Gobán Saor and is available from his blog at http://blog.gobansaor.com/2011/09/01/automating-powerpivot-refresh-operation-from-vba-the-code/.

It is stated more than once that this solution is not supported and may break your workbook rendering it unreadable.

I'll also throw in my vote for this to be available from a single button in the Excel window, a lot of companies and users are deploying PowerPivot for Excel without going for the full on <Enterprise Edition> of SharePoint needed to host PP on a server. (either because they cannot afford it it cannot warrent it for a low number of users)

John