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:
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.