Refresh and Manage Power Pivot External Data Connections

When you load data from an external data source into Power Pivot, you essentially create a static snapshot of that data source at the time of creation. Power Pivot uses that static snapshot in its Internal Data Model.

As time goes by, the external data source may change and grow with newly added records. However, Power Pivot is still using its snapshot, so it can't incorporate any of the changes in your data source until you take another snapshot.

The action of updating the Power Pivot data model by taking another snapshot of your data source is called refreshing the data. You can refresh manually, or you can set up an automatic refresh.

Manually refreshing Power Pivot data

On the home tab of the Power Pivot window, you see the Refresh command. Click the drop-down arrow below it to see two options: Refresh and Refresh All.

Use the Refresh option to refresh the Power Pivot table that's active. That is to say, if you're on the Dim_Products tab in Power Pivot, clicking Refresh reaches out to the external SQL Server and requests an update for only the Dim_Products table. This works nicely when you need to strategically refresh only certain data sources.

Use the Refresh All option to refresh all tables in the Power Pivot data model.

Setting up automatic refreshing

You can configure your data sources to automatically pull the latest data and refresh Power Pivot.

Go to the Data tab on the Excel Ribbon, and select the Connections command. The Workbook Connections dialog box opens. Select the data connection you want to work with and then click the Properties button.

With the Properties dialog box open, select the Usage tab. Here, you'll find an option to refresh the chosen data connection every X minutes and an option to refresh the data connection when the Excel work is opened:

• Refresh Every X Minutes: Placing a check next to this option tells Excel to automatically refresh the chosen data connection a specified number of minutes. This refreshes all tables associated with that connection.
• Refresh Data When Opening the File: Placing a check mark next to this option tells Excel to automatically refresh the chosen data connection after opening of the workbook. This refreshes all tables associated with that connection as soon as the workbook is opened.

Preventing Refresh All

You can refresh all connections that feed Power Pivot, by using the Refresh All command. Well, there are actually two more places where you can click Refresh All in Excel: on the Data tab in the Excel Ribbon and on the Analyze tab you see when working in a pivot table.

In Excel 2010, these two buttons refreshed only standard pivot tables and workbook data connections, and the Power Pivot refresh buttons affected only Power Pivot. They now all trigger the same operation. So clicking any Refresh All button anywhere in Excel essentially completely reloads Power Pivot, refreshes all pivot tables, and updates all workbook data connections. If your Power Pivot data model imports millions of lines of data from an external data source, you may well want to avoid using the Refresh All feature.

Luckily, you have a way to prevent certain data connections from refreshing when Refresh All is selected. Go to the Data tab on the Excel Ribbon and select the Connections command. This opens the Workbook Connections dialog box, where you select the data connection you want to configure, and then click the Properties button.

When the Properties dialog box has opened, select the Usage tab and then remove the check mark next to the Refresh This Connection on Refresh All (as shown).

Editing the data connection

In certain instances, you may need to edit the source data connection after you've already created it. Unlike refreshing, where you simply take another snapshot of the same data source, editing the source data connection allows you to go back and reconfigure the connection itself. Here are a few reasons you may need to edit the data connection:

• The location or server or data source file has changed.
• The name of the server or data source file has changed.
• You need to edit your login credentials or authentication mode.
• You need to add tables you left out during initial import.

In the Power Pivot window, go to the Home tab and click the Existing Connections command button. The Existing Connections dialog box opens. Your Power Pivot connections are under the Power Pivot Data Connections subheading. Choose the data connection that needs editing.

After your target data connection is selected, look to the Edit and Open buttons. The button you click depends on what you need to change:

• Edit button: Lets you reconfigure the server address, file path, and authentication settings.
• Open button: Lets you import a new table from the existing connection, which is handy when you've inadvertently missed a table during the initial loading of data.