Colin Phillips (Alumni)
If you’re unable to refresh the data and you’re seeing the following issue (below) when trying to refresh content, follow the steps described below. Note this was written with SharePoint 2010 in mind, but it should apply equally to SP2013.
The “PowerPivot Data” value will be whatever the name of your connection is (see below).
Configuring Data Refresh Settings
First, determine whether or not you have refresh turned on in your Excel Workbook.
- Choose the Data tab in the ribbon, and select Connections
- Choose the connection you want to edit (“PowerPivot Data” in this instance)
- Edit the properties of the connection
- Ensure you’re on the Usage tab
You may or may not have these checked, it’s fine either way. If the checkboxes above are enabled, this will explain why you’re seeing the error message above – as the connection is unable to connect to the data, due to a misconfiguration. Continue on below to resolve this.
Next, check which authentication mechanism you’re using for the workbook. Find the Excel Services settings in the “Definition” tab of the Connection Properties dialog and choose “Authentication Settings…”.
I recommend you use the Secure Store Service Account for Excel Services. The value for this in my case was “SP2010ExcelServices”.
This should resolve your issue, as this account is designed to be used for Excel Services / PowerPivot Data Refresh purposes.
To Determine the Excel Services Account
To find out the Excel Services account name, go to Central Admin and under Application Management, choose “Manage Service Applications”.
In the Service Applications tab, scroll down to find “Secure Store Service”, and click the link, or select the row and choose “Manage”.
Always use the “Excel Services” account – even though this is a PowerPivot Excel file, you’re using Excel Services to render the content, not PowerPivot. The PowerPivot Unattended Account is used for another purpose. Be sure to use the “ID” and not the “Name”.
