During PowerPivot integration, I have found some issue which requires troubleshoot. Following are some errors and troubleshoot I have made
Error message during test connection procedure “A connection cannot be made. Ensure that the server is running.”.
Error raised when you press “Test connection” during creation of new connection type.
Make sure that you have associated web application and correct service application proxy. A different connection type may be served by different service application proxy.
Error message during test connection procedure “Error 404 file can not be found.” .
You are trying to create new BISM connection to XSLX file. Then you press “Test connection” , you receive Error 404 file can not be found.
Make sure that PowerPivot Service Application has been created.
During PowerPivot installation, you receive error message “Instance name is missing…”.
During PowerPivot installation and after defining the configuration, you receive Instance name is missing .. error. And then you can not proceed the installation procedure.
a. Close all SQL Setup application
b. Restart PowerPivot installation. During Setup Role, select “Add SQL Server Database Relational Engine Services to this installation”.
c. Proceed to the Feature Selection. (Ignore warning that you already have an SQL instance)
d. You will notice that Instance name is no longer empty. Click back, to Setup Role, and at this time deselect “Add SQL Server Database Relational Engine Services to this installation”.
e. Then follow the rest of the wizard to install PowerPivot.
During PowerPivot configuration, Configure SharePoint Dependencies failed.
Configuration tools can not create unattended account for DataRefresh. The Configure SharePoint Dependencies section failed, so that configuration can not be completed.
a. In Central Administration, goto Secure Store Service application management.
b. Add New Target Application with default configuration
Target Application ID : PowerPivotUnattendedAccount
Type : Individual Ticket
c. Run PowerPivot configuration again.
I hope those experience will help solving your problem.