PowerPivot Service Application
In SharePoint 2010 architecture, a service application is a farm-level resource that is shared across sites in the farm. It provides an endpoint to one or more services that run on an application server. Access Database Services, Business Data Connectivity, and Excel Calculation Services are all examples of services that are accessed through a service application that must be created when you deploy the service. In a PowerPivot for SharePoint installation, the PowerPivot System Service also requires a service application. The service application provides a run time context for querying and managing PowerPivot data in the farm.
This topic contains the following sections:
How a PowerPivot service application is created and configured
How a PowerPivot service application is used in the farm
PowerPivot Application Database
How a PowerPivot service application is created and configured
A PowerPivot service application is created and configured automatically when you install SQL Server PowerPivot for SharePoint using the New Server installation option. Otherwise, a farm or service administrator must create it manually in SharePoint Central Administration as a post-installation step when deploying PowerPivot for SharePoint in the farm.
You must have at least one PowerPivot service application to provide a run time context for the PowerPivot System Service. The service application specifies the service identity and configuration settings that are associated with the PowerPivot System Service. The service application will use its own internal database for storing information about data refresh schedules, usage data that is collected for reporting purposes, and the status of PowerPivot data files currently in use in the farm.
Although you only need one service application to enable PowerPivot query processing, you can create additional service applications to manage them separately in PowerPivot Management Dashboard or to support different configurations. For example, your organization might have data confidentiality requirements, where individual departments require that their application data is not visible to other departments. In this case, creating separate service applications for each department helps you meet that requirement by displaying administrative data separately for each service application in the management dashboard.
For more information about how to create a service application, see Create and Configure a PowerPivot Service Application.
How a PowerPivot service application is used in the farm
A PowerPivot service application provides an endpoint to the PowerPivot System Service. It runs on an application server that has a deployment of PowerPivot for SharePoint. In a scale-out deployment that has multiple PowerPivot servers in the farm, a single service application will be available on all of the physical servers.
To make service applications available to Web applications and site collection in a farm, SharePoint 2010 architecture provides service association lists. A service association list is a set of shared services that can be used by a Web application. There is one default list that can be used by many Web applications, or a custom list that is created specifically for a single Web application. A service association list can include any or all of the shared service in the farm, including Excel Calculation Services, Secure Store Service, and PowerPivot service applications.
To make PowerPivot query processing available to sites across the farm, you add a PowerPivot service application to either the default or a custom service association list. If you create multiple PowerPivot service applications to vary configuration or isolate data, you must create custom service associations to ensure that a Web application uses the specific PowerPivot service application you created.
Always include one PowerPivot service application in the default connection group. Use custom service associations for any new service applications that you add. Having a PowerPivot service application in the default connection group is required for PowerPivot Management Dashboard in Central Administration. Central Administration uses only the default connect list for its service associations. If a PowerPivot service application is not in the list, the dashboard will not work properly.
For more information about how PowerPivot query requests flow through the farm, see PowerPivot System Service. For more information about how to add a PowerPivot service application to a service association list, see Connect a PowerPivot Service Application to a SharePoint Web Application.
PowerPivot Application Database
Each PowerPivot service application stores application data and schedules in a SQL Server relational database. Application data includes data refresh schedules, usage data, and which PowerPivot data is loaded or cached in the system. The application data is for internal use by PowerPivot service applications. The service application database does not store content files. PowerPivot data is stored in Excel workbooks in a content database store.