PowerPivot for SharePoint: Lessons Learned and Useful Links

When designing a new SharePoint farm there are some decisions that are easy to make and for which there are useful examples such as the number of SharePoint servers that will be required and the distribution of the SharePoint Service applications.

It is considerably harder in all circumstances to make design decisions when the expected usage of a SharePoint farm cannot be quantified in advance. In this scenario, experience dictates the decisions that are made, but what do you do if experience is not enough, if the service is directly dependent on the amount of content and the amount of usage?

This is the situation I find when dealing with PowerPivot for SharePoint. I found myself in a scenario where PowerPivot for SharePoint had not been used by the client before but they wanted the specifications of the SQL and SharePoint servers to accommodate demand.

I used my own experience of SQL Server, an understanding of the ways that PowerPivot could be used in SharePoint, a lot of assumptions and (where I could find it, Microsoft documentation) to propose an initial topology and server specification.

Below are links to the documentation I found most useful:

• MSDN: Deployment Topologies for SQL Server BI Features in SharePoint
  • This article describes multiple topologies for the deployment of SharePoint, SQL and PowerPivot for SharePoint
• Technet: Hardware and Software Requirements (PowerPivot for SharePoint and Reporting Services in SharePoint Mode)
  • This article has a good overview of the requirements but has a recommendation of a minimal RAM requirement of 8GB and a recommended requirement of 128GB which is quite a jump!
• MSDN: Hardware Sizing a Tabular Solution (SQL Server Analysis Services)
  • This whitepaper is not focused on SharePoint but does have some very useful tips for monitoring the installation once it is up and running in order to identify when the configuration may need to be changed

And whilst doing my research I also found a variety of very useful references for the actual installation and configuration of PowerPivot for SharePoint:

• MSDN: Install Analysis Services Server in SharePoint Mode for SharePoint 2013 (SQL Server 2012 SP1)
  • This is a detailed step through of the installation process of the SSAS instance
• MSDN: Install or Uninstall the PowerPivot for SharePoint Add-in
  • This article focusses on the installation of the PowerPivot for SharePoint add-in within the SharePoint farm
• Technet: Configure Power Pivot for SharePoint 2013
  • This article focusses on the configuration to allow SharePoint and PowerPivot to communicate and has some overlap with the previous two articles
• MSDN: PowerPivot for SharePoint (SSAS)
  • This is the root page for all MSDN resources for PowerPivot for SharePoint

And finally, when Kerberos is required:

• Technet Blogs: Excel Services 2010 & 2013 - Kerberos Configuration