PowerPivot for SharePoint DataSource Issues

Over the past several weeks we have been setting up new dev enviornments for Sql Server 2012 upgrades. In doing this, we have had to upgrade the PowerPivot bits on the SharePoint/Reporting Server app servers. This has caused the PowerPivot reports to break or kick an error saying "Can't connect to datasource".  After a good bid of research we found a couple primary causes for this issue.

• Service Account for PowerPivot permissions on the Analysis instance
• Pull up SSMS and servername\powerpivot instance
• Take a look at the properties of the instance
• Make sure your service account has admin permissions
• This is needed to be able to create new "sandbox" dynamic cubes
• Maximum Workbook Size/Increase Excel Services Limits
• Apparently, after upgrading these config edits are default back to a single connection and 1 MB file size. Most any PP report will result in an error as a result of this default
• Go to Central Admin/ Application Management/ Manage Service Applications
• Excel Service Application/Workbook Properties
• Maximum Workbook Size update to 2000 mb
• Maximum Char or Image Size update to 100 mb

I found a great article that helps you walk through confuigruation steps. I found this specific issue to be a major problem when trying to upgrade.


Reference Link:
http://powerpivotgeek.com/docs/PowerPivot_Existing_Farm_Installation.pdf