Loading External Data into Power Pivot - Microsoft Business Intelligence Tools for Excel Analysts - page 85

Refreshing and Managing 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 inter-
nal Data Model.
The external data source may change and grow as new records are added. However, Power Pivot is
still using its snapshot so it can't incorporate any of the changes in your date 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 your data. You can refresh manually, or you can set up an automatic refresh.
Manually refreshing your Power Pivot data
On the Home tab of the Power Pivot window, click the drop-down arrow on the Refresh button and
choose one of these options:
➤ Refresh: Use the Refresh option to refresh the Power Pivot table that's currently active. For
example, if you are on the Dim_Products tab in Power Pivot, clicking Refresh reaches out to
the external SQL Server and requests an update for just the Dim_Products table. This works
nicely when you need to strategically refresh only certain data sources.
➤ Refresh All: Use the Refresh All option to refresh all the 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.
Here's how:
1. Click the Data tab on the Ribbon and click the Connections button.
The Workbook Connections dialog box opens.
2. Select the data connection you want to work with and then click the Properties button.
3. With the Connection Properties dialog box open, click the Usage tab.
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 (see Figure 4-22).