Unable to refresh data for a data connection in the workbook. Try again or contact your system administrator. The following connections failed to refresh: PowerPivot Data

Details

Explanation and Resolution

Excel Services cannot connect to or load the PowerPivot data. Conditions that cause this error to occur include the following:

Scenario 1: Service is not started

The SQL Server Analysis Services (PowerPivot) instance is not started. An expired password will cause the service to stop running. For more information about changing the password, see Change Service Accounts and Passwords (PowerPivot for SharePoint) and Start or stop services on a PowerPivot for SharePoint instance.

Scenario 2a: Version mismatch between client and server applications

The workbook you are attempting to open might have been created in a different version of PowerPivot for Excel. As such, the embedded PowerPivot database cannot be queried by the current version of PowerPivot for SharePoint that is running in your SharePoint farm.

To determine the version of the workbook, you must check with the author or open it in PowerPivot for Excel. There is no version information provided about the workbook on a SharePoint site.

To work around this issue, install the version of the PowerPivot for Excel add-in that corresponds to the version of PowerPivot for SharePoint used in your SharePoint environment. Currently, there is no supported method for using a PowerPivot workbook created in a SQL Server 2012 version of PowerPivot for Excel on a SQL Server 2008 R2 version of PowerPivot for SharePoint.

Scenario 2b: Wrong version of MSOLAP Provider on the server

In Central Administration, open the Excel Services service application page and check the Trusted Data Providers page to verify that that the MSOLAP provider appears in the list.

• If you are using a server, verify that both MSOLAP.4 and MSOLAP.5 appear in the list.

• If you are using a SQL Server 2008 R2 server, you will only have MSOLAP.4, but you must check its file properties to determine whether you have the correct version. If you are using a SQL Server 2008 R2 server, you must check the version of the .dll file to determine whether you have the correct file.

There are two versions of MSOLAP.4: the SQL Server 2008 version that was originally installed with Excel Services, and the SQL Server 2008 R2 version that is installed with PowerPivot for SharePoint or with a SQL Server 2008 R2 redistributable.

The SQL Server 2008 version of MSOLAP.4.DLL does not support PowerPivot data access. You must have the SQL Server 2008 R2 version or higher. To verify MSOLAP.4 is from SQL Server 2008 R2, check the file version information. The file version should be 10.50 or higher.

If Excel Services is running on an application server that does not have the Analysis Services OLE DB provider installed, install it on the server. For more information, see How to: Install the Analysis Services OLE DB Provider on an Excel Services Computer.

Scenario 3: Domain controller is unavailable

The cause might be that a domain controller is not available to validate the user identity. A domain controller is required by the Claims to Windows Token Service to authenticate the SharePoint user on each connection. The Claims to Windows Token Service does not use cached credentials. It validates the user identity for each connection.

You can confirm the cause of this error by viewing the SharePoint log file. If the SharePoint logs include the message "Failed to get WindowsIdentity from IClaimsIdentity", the user identity could not be authenticated.

To work around this problem, join the computer to the same domain as the PowerPivot server, or install a domain controller on your local computer. The second solution, installing the domain controller, will require you to create local domain accounts for all services and users. You will need to configure service accounts and SharePoint permissions for the accounts you define.

Installing a domain controller on your computer is useful if your objective is to use PowerPivot for SharePoint in an offline state. For detailed instructions on how to use PowerPivot offline, see the blog entry for "Taking your PowerPivot server off the network" on http://www.powerpivotgeek.com.

Scenario 4: Unstable server

One or more services might be in an inconsistent state. In some cases, running IISRESET will resolve the problem.