Prologika Forums
Making sense of data
PowerPivot Data Refresh in Excel 2013

Blogs

Prologika (Teo Lachev's Weblog)

Books

Applied Microsoft SQL Server 2012 Analysis Services (Tabular Modeling)Learn PowerPivot and Analysis Services-Tabular at your own pace with our latest book Applied Microsoft SQL Server 2012 Analysis Services (Tabular Modeling). It is designed as an easy-to-follow guide for learning how to implement BI solutions spanning the entire personal-team-organizational BI spectrum.

Training

Applied Microsoft SQL Server 2008 Reporting Services

We offer onsite and online Business Intelligence classes!  Contact us about in-person training for groups of five or more students.

For more information or to register click here! 

Syndication

Archives

What options does a PowerPivot user have to refresh data in a PowerPivot model on the desktop, aka PowerPivot for Excel?

Prior to Excel 2013, the answer was just one – manual refresh by either clicking the Refresh button in the PowerPivot window or the Refresh button in the Existing Connections dialog box. Note that the Refresh button in the Excel ribbon doesn't work since Excel doesn't know anything about PowerPivot. Not does the checking the "Refresh data when opening the file" checkbox in the PowerPivot connection. In Excel 2010, these options won't reopen the PowerPivot connections to the data sources. Instead, the net effect is that they will simply refresh the pivot reports from the pivot cache which is not what you're after.

Starting with Excel 2013, however, Excel and PowerPivot play better together as I discussed in my What's New in Office 2013 BI blog. And, now we have additional options to refresh data:

  1. Right-click the pivot report, go to PivotTable Options, click the Data tab, and then click "Refresh data when opening the file". Enabling this option will refresh the data in the PowerPivot tables that are used on the report when you re-open the Excel workbook file.
  2. In the Excel Data ribbon, click Connections, select the connection you want to refresh, and then click Properties. Notice that all PowerPivot connections are now exposed in the Connections dialog. In the Connections Properties dialog box, check the "Refresh data when opening the file". This option will open the connection when you open the file, and refresh all PowerPivot tables using the connection.

3.    Finally, you can also automate PowerPivot tasks, including refreshing data. For example, if you want to refresh the ResellerSales table on open, you can add the following line to the Workbook_Open() event:

ActiveWorkbook.Model.ModelTables("ResellerSales").Refresh

Speaking of automation, everything you can do in the Excel UI is exposed in the object model and automatable in Excel 2013. Here is another example of adding a table from the Excel workbook to the model and then creating a relationship between that table and another table which is already in the model:

Workbooks("Book1").Connections.Add "WorksheetConnection_Book1!Table2", "", "WORKSHEET;Book1", "Book1!Table2", 7, True, False

ActiveWorkbook.Model.ModelRelationships.Add ActiveWorkbook.Model.ModelTables("Table1").ModelTableColumns("Name"), ActiveWorkbook.Model.ModelTables("Table2").ModelTableColumns("Name")

And, don't forget then when you deploy your PowerPivot model to SharePoint you can automate the data refresh on a schedule.


Posted Mon, Feb 4 2013 9:00 AM by tlachev
Filed under: