Best Practices

The following are best practices while working with PowerPivot for SharePoint:

• Don’t skimp on memory: Because PowerPivot for SharePoint loads the workbooks into the server’s memory, the application server hosting PowerPivot services should have plenty of memory. The recommended amount of memory for an enterprise PowerPivot server is 32GB to 64GB. This will vary from organization to organization, but the determining factor is the number and size of concurrent workbooks that will be used.
  
  
• Review and set file size limits: Because PowerPivot applications include the actual data, the files can be larger than what is commonly stored in SharePoint. By default, SharePoint limits the size of uploaded files to 50MB and Excel Services limits the size of a workbook to 10MB. These limits should be reviewed and adjusted as necessary to meet the requirements for the environment.
  
  
• In a multiserver SharePoint farm, turn off Excel Calculation Services on the WFEs: If the instance of ECS on the WFE tries to connect to the PowerPivot application service, it will fail because the Analysis Services OLE DB driver is not installed on the WFE.
  
  
• Turn off the Excel Web Access warning: Because PowerPivot applications require Excel Services to query external data, users will frequently be prompted with the SharePoint Excel Web Access external data warning. In the Excel Services Application settings, the Warn on Refresh check box should be cleared.