Released: Service Pack 1 for Microsoft SharePoint Server 2010
Today is a great day for us PowerPivoteers because Service Pack 1 for Microsoft SharePoint Server 2010 has been released. You can download it from our new and fantastic Microsoft.com Download Center at https://www.microsoft.com/download/en/details.aspx?id=26623.
Why is this SharePoint Service Pack so important for PowerPivot? Among other things, it lowers the number of query requests Excel Calculation Services (ECS) generates in response to user actions in the browser. Over the course of the last 12 months, we worked hard with the Excel Services team to achieve this and it’s great to have it because it improves the scalability of PowerPivot for SharePoint overall.
The following diagram illustrates the communication behavior in somewhat simplified form. The important point is that when a user interacts with a PowerPivot workbook in the browser, the browser sends requests to Excel Web Access (EWA), which forwards the requests to ECS, which queries the workbook’s embedded PowerPivot database loaded on a PowerPivot application server. Depending on the workbook complexity, ECS might generate multiple query requests, especially when the workbook includes multiple slicers that are related to each other. It’s not unusual for ECS to send 10 or 20 requests to the PowerPivot application server in response to a slicer action in order to determine the state of each individual slicer corresponding to the user’s current selection.
In SharePoint 2010 Service Pack 1, our Excel Services colleagues were able to cut the number of ECS query requests nearly in half, which means half the load on PowerPivot application servers per user action and therefore almost double the PowerPivot scalability. Our tests showed a scalability increase of roughly 80% in comparison to SharePoint 2010 RTM. It varies to some degree depending on workbook complexity, but the improvement is definitely significant enough to update every PowerPivot-enabled farm—after proper testing and signoff in your organization, of course. Needless to say that performance and scalability improvement is a continuous effort in our team. We’ll keep pushing the envelope in SQL Server codenamed “Denali” and beyond… the sky is the limit!