We have been seeing some issues with customers using PowerPivot workbooks as a source file in new Excel PowerPivot files. There are a variety of errors that can arise from this process, and this blog is intended to provide you with some troubleshooting steps to resolve the issue.
You may or may not be aware, but it is possible to use a PowerPivot workbook as a data source for other data applications. For instance, one can use Excel and connect to a PowerPivot file on a SharePoint site as a PowerPivot data source. There may be a file on SharePoint site named BIFinance.xlsx. When creating a new PowerPivot workbook, we could specify our data source as the following:
Fig 1: Using PowerPivot workbook as a data source
In a nutshell, when the data source is the PowerPivot Mid-Tier hosted PowerPivot workbook, the connection goes through a Redirector service on the SharePoint server and is ultimately routed to the SQL Server Analysis Services PowerPivot instance.
For more information on how to use PowerPivot workbooks as a Data Source, please see the following video:
Using PowerPivot Workbooks as a Data Sourcehttp://technet.microsoft.com/en-us/sqlserver/dn151361.aspx
A few sample errors that may arise when attempting to connect to the PowerPivot workbook as a data source:
Possible scenarios that might cause this process to fail:
1. The SharePoint Web application is setup with Kerberos. If you are seeing the DB_SEC_E_AUTH_FAILED(0x80040E4D) error, then the web application is more than likely set up with Kerberos.
a. Please follow the steps in the following article to resolve the issue. You will need to modify the web.config file on the web front end servers. http://blogs.msdn.com/b/johndesch/archive/2012/04/23/using-powerpivot-workbooks-from-a-mid-tier-server-configured-for-kerberos-authentication.aspx
2. The client machine making the call does not have updated Microsoft SQL Server Analysis Server OleDB drivers. Please ensure that the Analysis services (SQL_AS_OLEDB) drivers are up to date on the client machine: You may need to check with your SharePoint administrator to determine what version of PowerPivot is installed in your SharePoint environment.
a. If using SQL Server 2008 R2 PowerPivot: http://www.microsoft.com/en-us/download/details.aspx?id=30440
b. If using SQL Server 2012 PowerPivot: http://www.microsoft.com/en-us/download/details.aspx?id=35580
3. Make sure the SharePoint Web application where the workbook is stored does not have multiple bindings in IIS.
a. Open IIS on the SharePoint Web Front End servers and select the SharePoint web application and click on Bindings.
b. If there is more than one this will fail.
c. If your web application needs more than one binding, you may extend the web application in SharePoint. Please see the following article on how to extend the web application in SharePoint.
Extend a Web application (SharePoint Server 2010) - http://technet.microsoft.com/en-us/library/cc261698(v=office.14).aspx
4. The user that is making the connection needs to have limited read access the Root Web application.
5. If you see this error: "XML parsing failed at line 1, column 1: Incorrect document syntax." Please review the following article:
The steps above should resolve most of the errors that are seen when attempting to use a PowerPivot workbook as a data source within Excel PowerPivot.
SQL_AS_OLEDB - This needs to be installed where? On the Application server/WFE or the client machine itself? If client machine, if I have 20 users - do I have to install on all 20 of them?
Is it poss to connect to the SSAS instance assoc with PP workbook instead of import as above and avoid VS. It seems the name/path changes from time to time.
The link to correct the "XML parsing failed at line 1" problem is broken. (404 not found) It's too bad because I've been trying to solve that problem all day.
M.W.: Try the following blog post to fix the "XML parsing" error: