While building out a SharePoint demo farm with SQL 2012 I ran into the following error when trying to view a PowerPivot spreadsheet:
Unable to refresh data for a data connection in the workbook
After a lot of digging around I realized that it was a pretty simple issue. My farm includes a web front end (WFE), an applications server, and the SQL 2012 database server. One step in configuring PowerPivot on the application server is to ensure that both version 4 and 5 of the MSOLAP provider is installed. The one piece of information that isn’t mentioned is that you need to also install the MSOLAP providers on the web front ends if they also have Excel Services enabled. Once the MSOLAP providers were installed on the WFE the error went away and the workbook functioned as expected.
Check out this TechNet article for information on installing the Analysis Services OLE DB Provider (MSOLAP) on SharePoint.