Chapter 35: Introducing Power Pivot
35
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 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 your data. You can refresh manually, or you can set up an auto-
matic refresh.
Manually refreshing your Power Pivot data
On the Home tab of the Power Pivot window, you will see the Refresh command. Clicking
the drop-down arrow below it will display two options: Refresh and Refresh All.
Use the Refresh option to refresh the Power Pivot table that’s currently active. That is, if
you are on the Customers tab in Power Pivot, clicking Refresh will reach out to the external
data source and request an update for just the Customers table. This works nicely when you
need to refresh strategically only certain data sources.
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 pull the latest data and refresh Power Pivot automati-
cally. Go to the Data tab in the Excel Ribbon, and select the Queries & Connections com-
mand. This will activate the Queries & Connections task pane illustrated in Figure 35.22.
FIGURE 35.22
The Queries & Connections task pane