Refreshing or Changing Imported Data
When you import data from an external data source into your workbook, you can choose to keep the data as is, to capture a point in time for further analysis, or you can update the data from the source whenever you choose. The latter option is handy if you are getting data from relational databases that contain live sales information, or data feeds that are updated several times a day.
There are two kinds of data refresh:
Manual refresh. You can refresh the data in the workbook manually at any time. You can refresh all data, which is the default, or you can manually choose the tables and columns to refresh for individual data sources.
Automatic or scheduled refresh. If you have published your workbook to a PowerPivot Gallery or Sharepoint site that supports PowerPivot, you or the Sharepoint administrator can create a schedule for automatically updating the data in the workbook.
In addition to getting updated data from an existing source, you will need to refresh data in your workbook whenever you make changes to the schema of the source data. These changes can include adding columns or tables, or changing the rows that are imported.
This topic provides the following information:
How to perform manual data refresh in the workbook.
How to schedule automatic data refresh through SharePoint.
How to make changes to data sources, such as connection properties.
How to update the data in the workbook after source data has changed.
For additional troubleshooting tips and requirements, see Troubleshoot Data Refresh.
Note
Note that addition of data, changing data, or editing filters always triggers recalculation of any columns or formulas that depend on that data source. This may take some time. For more information, see Recalculating Formulas.
Manually Refresh an Existing Data Source
If you need to update column metadata or get recent data for designing new formulas, you can manually refresh your data at any time. The following topics provide instructions about how to manually refresh a data source and to edit its properties.
Manually Refresh Data in a PowerPivot Workbook
Edit the Properties of an Existing Data Source
Schedule Data Refreshes using Sharepoint
You can schedule data refresh to occur at regular intervals when you save a PowerPivot workbook to a SharePoint site. Using connection information in the workbook or in the schedule, the server will connect to the data sources and retrieve updated data on your behalf.
For information about setting up a workbook for automatic refresh from SharePoint, see the following topics:
Automatically Refresh PowerPivot Data in SharePoint
Changing a Data Source
If you need to change the data associated with a workbook, you can use the tools in the PowerPivot add-in to edit the connection information or to update the definition of the tables and columns used in your PowerPivot data.
The changes that you can make to existing data sources include the following:
Connections |
Tables |
Columns |
|---|---|---|
|
|
|
Note
In Windows Vista and Windows 7, features in the PowerPivot window are available on a ribbon, which is discussed in this topic. In Windows XP, features are available from a set of menus. If you are using Windows XP and want to see how the menu commands relate to the ribbon commands, see The PowerPivot UI in Windows XP.
To make changes, follow these steps in the PowerPivot window:
On the Design tab, in the Connections group, click the Existing Connections button, choose your connection, and use the editors that are provided. For more information, see Edit the Properties of an Existing Data Source.
Click the tab that contains the table that you want to modify, and then on the Design tab, in the Properties group, click the Table Properties button. For more information, see Edit the Properties of an Existing Data Source.
Select the column that you want to change by clicking on its header, and then in the Home tab, use the controls in the Formatting group to modify the column's properties. For more information, see Set the Data Type of a Column.
Changing the Rows that are Imported
When you import a data source, you can define filters to restrict the total number of rows that are imported. This may be handy when you are beginning to develop a set of data for analysis and want to work with just a subset of the data for better performance. You might also want to create different workbooks for different regions: for example, you could use the same data source but filter on a particular region. For more information, see Change the Rows that are Imported from a Data Source.
If you later decide to add more data back in you do not need to recreate the model with a different filter; instead, you can simply edit the filters that are applied to the data source to allow more data, or less.