Share via

  • Article

Troubleshoot Data Refresh

This section provides detailed information about data refresh.

  • How data refresh works

  • Impact of data refresh

  • Determining where data comes from

  • Determining when a data source was refreshed

  • Restrictions on enabling automatic refresh

  • Restrictions on changes to data sources

How Data Refresh Works

When you refresh data, the data in the PowerPivot window is replaced with new data. You cannot import just new rows of data or just changed data. PowerPivot for Excel does not track which rows were added previously.

Refreshing of data takes place as a transaction. This means that once you begin updating data, the entire update must either fail or succeed; you will never have data that is partly correct.

Manual data refresh, which you initiate from the PowerPivot window, is handled by the local in-memory instance of Analysis Services. Therefore, the data refresh operation can affect the performance of other tasks on your computer. However, if you schedule automatic refresh of data in a workbook that is saved to a SharePoint site, the instance of Analysis Services that is hosted within the SharePoint site manages the import process and its timing.

Impact of Data Refresh

A refresh of data usually triggers recalculation of data in formulas and PivotTables and charts. Refreshing data means getting the latest data from the external sources; recalculating means updating the result of all formulas that use data that has changed. Arefresh operation usually triggers recalculation.

Therefore you should always be mindful of the potential impact before you change data sources or refresh the data that is obtained from the data source, and consider these potential consequences:

  • Some parts of the PowerPivot data may be broken as a result of changes in the data source. If not all the columns can be retrieved from the data source (for example, if they have been deleted or changed), refresh will fail, and you must update the mappings between the source data and the PowerPivot data. For more information, see Edit the Properties of an Existing Data Source.

  • After refreshing, some columns might be flagged as containing an error. This can happen because the DAX formula in the column uses data that became unavailable when you refreshed, the data type of a column changed, or an invalid value was added to the external data. To resolve the issue, you can edit the formula, or you can delete the column if it is based on data that is no longer available.

  • Formulas that use the updated data will need to be recalculated. Depending on the size of the workbook, this can take some time.

  • If your workbook contains multiple data sources, you might need to refresh the entire workbook even if only one external data source has changed. For example, if you create measures that rely on calculated columns, and those calculated columns use values from other calculated columns, PowerPivot first analyzes the dependencies and then processes the entire chain of related objects in order. Depending on the complexity of the dependencies, this can take a long time.

  • When you change a filter, the entire workbook must be recalculated.

For more information about recalculation of formulas, see Recalculate Formulas. You can also temporarily disable recalculation, to mitigate the performance impact of large-scale data refreshes.

Determining the Source of Data

If you are not sure where the data in your workbook came from, you can use the tools in the PowerPivot window to get the details, including the source file name and path.

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 find the source of existing data

  1. In the PowerPivot window, select the table that contains the data for which you want to know the source.

  2. On the Design tab, in the Properties group, click Table Properties.

  3. In the Edit Table Properties dialog box, make note of the value listed for Connection Name.

  4. On the Design tab, in the Connections group, click Existing Connections.

  5. In the Existing Connections dialog box, select the data source with the name you found in step 3, and click Edit.

  6. In the Edit Connections dialog box, view the connection information, such as the database name, file path, or report path.

Determining When Data was Last Refreshed

You can use the tools in the PowerPivot window to discover when the data was last refreshed. If you have saved the workbook to a SharePoint site and automatic data refresh is scheduled on the server, the Last Refreshed date includes the date of the automatic refresh as well.

To find the date that a table was last refreshed

  1. In the PowerPivot window, select the table that contains the data for which you want to know the refresh date.

  2. On the Design tab, in the Properties group, click Table Properties.

  3. In the Edit Table Properties dialog box, Last Refreshed shows the last date that the table was refreshed.

Restrictions on Refreshable Data Sources

Some restrictions apply to the data sources that can be automatically refreshed from a SharePoint site. Be sure to select only those data sources that meet the following criteria:

  • The data source must be available at the time that data refresh occurs and available at the stated location. If the original data source is on a local disk drive of the user who authored the workbook, you must either exclude that data source from the data refresh operation, or find a way to publish that data source to a location that is accessible through a network connection. If you move a data source to a network location, be sure to open the workbook in PowerPivot for Excel and repeat the data retrieval steps. This is necessary to re-establish the connection information that is stored in the PowerPivot data source.

  • The data source must be accessed using the credentials that are embedded in the PowerPivot data source or that are specified in the schedule. Embedded credentials are created in the PowerPivot data source when you connect to the external data source from PowerPivot for Excel. If you want to override these credentials, you can specify a different user account in the schedule as long as the server is able to store them. For more information, see Configure Stored Credentials for PowerPivot Data Refresh on the Microsoft Web site.

  • Data refresh must succeed for all of the data sources that you specify. Otherwise, the refreshed data is discarded, leaving you with the last saved version of the workbook. Exclude any data sources that you are not sure about.

  • Data refresh must not invalidate other data in your workbook. When you refresh a subset of your data, it is important that you understand whether the workbook is still valid once newer data is aggregated with static data that is not from the same time period. As a workbook author, it is up to you to know your data dependencies and ensure that data refresh is appropriate for the workbook itself.

    An external data source is accessed through an embedded connection string, URL, or UNC path that you specified when you imported the original data into the workbook using the PowerPivot window. Original connection information that is stored in the PowerPivot data source is reused for subsequent data refresh operations. There is no separate connection information that is created and managed for data refresh purposes; only existing connection information is used.

Restrictions on Changes to a Data Source

There are some restrictions on the changes that you can make to a data source:

  • The data types of a column can only be changed to a compatible data type. For example, if the data in the column includes decimal numbers, you cannot change the data type to an integer. However, you can change numeric data to text. For more information about data types, see Data Types Supported in PowerPivot Workbooks.

  • You cannot multi-select columns in different tables and change properties of the columns. You can work with only one table or view at a time.