PowerPivot is designed to be a versatile tool and it is possible to integrate PowerPivot with SharePoint. There are several restrictions as to what version of SharePoint can host PowerPivot and other configuration related requirements. Let’s take a look at them. The PowerPivot for SharePoint requires SharePoint 2010 farm as well as Excel Services and an install of PowerPivot on SharePoint as add-on.
The main advantage of PowerPivot on SharePoint is in centralization of access to PowerPivot workbooks. Data is housed in PowerPivot for SharePoint instances and can be refreshed on scheduled intervals using the PowerPivot data refresh services provided by the server.
The implementation of PowerPivot for Share point is based on service model. It means that access to PowerPivot tables can be managed by administrators, secure and reliable.
In order to deploy PowerPivot for SharePoint successfully, we need to install two different components. They are client and server components and both are required to utilize PowerPivot features to the fullest.
PowerPivot relies on Analysis Services database that in turn needs Analysis Services xVelocity in memory analytic engine called VertiPaq. Both of these tool help with data load and data querying. The PowerPivot xVelocity runs within Excel and SahrePoint run related Analysis Services that handles requests from PowerPivot.
Pic 1: PowerPivot for SharePoint architecture diagram
The PowerPivot Web service is an integral part of the overall PowerPivot on SharePoint architecture. It is required for requests redirects from web application to a PowerPivot System. The PowerPivot issues requests to the Analysis Services then manages connections and data manipulations. It is also used to track overall health of the system.