Microsoft Office Tutorials and References
In Depth Information
You probably want to produce reports like this one and then refresh their values periodically to
get the newest data and cover the latest periods in time. Nevertheless, when you import data
to PowerPivot, you do not create a live link between the source table and the Excel workbook.
Instead, you copy data to PowerPivot, which stores the information in its columnar database
and works separately from the original data source. For this reason, if you want to refresh data,
you need to reload the information directly from the source.
To reload data, you need to click the Refresh button on the Home tab of the PowerPivot
ribbon, shown in Figure 2-33.
FIguRE 2-33 To refresh data, we need to use the PowerPivot window.
Because refreshing a table really means reloading it from the database, it can sometimes take
time. It is a pretty fast operation for small tables (less than one million rows, for instance), but
it gets more time-consuming as the table becomes larger.
Note This PowerPivot operation works very differently from the behavior of an Excel PivotTable
linked to a SQL Server Analysis Services database. When you connect Excel to Analysis Services,
Excel stores only the results, not the original data. So whenever the underlying dataset in the
Analysis Services database changes, you can simply refresh the PivotTable to make Excel query
the database again and retrieve the new information. The basic difference is that a PowerPivot
workbook stores data whereas a classic PivotTable is just a presentation layer over data stored
somewhere else (in the example, in the Analysis Services database).
The only kind of tables that are automatically refreshed are Linked Tables—that is, tables that
exist in the Excel workbook and are imported inside PowerPivot through the Create Linked
Table button. You can switch automatic updates of linked tables on or off, using the options
available on the Linked Table tab of the PowerPivot ribbon. The option Update Mode on the
Linked Table tab is set to Automatic by default, and there are very rare cases where it might
be useful to turn it off.