PowerPivot System Service
PowerPivot System Service is a feature of PowerPivot for SharePoint that enables PowerPivot workbook access and server management in a SharePoint 2010 farm. Together with other server components, it provides the middle tier infrastructure that establishes user connections to PowerPivot data, loads and unloads data, and monitors and reports on server health.
PowerPivot System Service is a web service that is installed alongside an Analysis Services instance that runs in SharePoint integrated mode. The two services are tightly coupled and share the task of making PowerPivot data available in the farm. Provisioning of the service is through a SharePoint service application that is either created for you during setup, or manually as a post-installation task in SharePoint Central Administration.
Click the following links to go to a specific section in this topic:
Service Operations
Service Configuration and Administration
Service Dependencies and Requirements
Server Connections Within the Farm
Request Allocation
Caching PowerPivot Data
Cache Discovery and Maintenance
Service Operations
The PowerPivot System Service performs the following operations:
Server operations |
Description |
|---|---|
Handles on-demand requests for PowerPivot data |
PowerPivot System Service handles requests for PowerPivot data that is stored in content libraries in a SharePoint farm. It accepts requests from the MSOLAP data provider on an application server, or from the PowerPivot Web service on a Web front-end server. PowerPivot System Service retrieves the PowerPivot workbook from a SharePoint library, impersonating the SharePoint user who requested the data. PowerPivot System Service then forwards a data load request to the local Analysis Services. Analysis Services extracts the PowerPivot data from the workbook, loads it into memory, and returns the result set to the client or server application that requested the data. PowerPivot System Service continues to monitor requests for the data and updates that information in the PowerPivot service application database. If the file is inactive for 48 hours, PowerPivot System Service will issue an unload request to the local Analysis Services instance. |
Handles requests for PowerPivot data refresh |
PowerPivot System Service also monitors a queue of data refresh jobs that are scheduled to run on a recurring basis. At the scheduled time, it will direct the local Analysis Services instance to load PowerPivot data and re-import data from external data sources. Data refresh is a server operation that is performed on the behalf of a user. Note that additional services and configuration is required to support this type of operation. For more information, see Enable and Configure PowerPivot Data Refresh. |
Allocate processing to Analysis Services service instances |
PowerPivot System Service load balances requests for query processing across available servers if there are multiple instances of PowerPivot for SharePoint in the farm. SharePoint maintains a list of available services on each server. The PowerPivot System Service uses this list to find other servers in the farm. To determine which PowerPivot for SharePoint server gets a particular request, the PowerPivot System Service uses an allocation methodology. Methodologies include round-robin (default) or server health based methodology. You can configure a service application to use either one. For more information about allocation, see Request Allocation in this topic. |
Sets up user and administrative connections |
PowerPivot System Service manages active, reusable, and cached connections to an Analysis Services instance. Ongoing connection requests to PowerPivot data that is already loaded can be serviced through connection pools that are maintained for user connections to the same in-memory data source. PowerPivot System Service also creates and manages administrative connections to other PowerPivot System Service instances in the farm. |
Issues requests to cache data |
To reduce the wait time that is incurred when retrieving PowerPivot data from a content library, PowerPivot System Service uses a caching methodology that stores and loads a local copy of the data from the file system. For more information about caching, see Caching PowerPivot Data in this topic. |
Issues requests to unload data |
PowerPivot System Service monitors PowerPivot data access and instructs the Analysis Services instance to unload inactive data sources from memory. Once PowerPivot data is loaded in-memory, it remains there to service additional requests for the same data. After a period of inactivity, PowerPivot System Service issues an unload request to the Analysis Services instance to unload the PowerPivot data and free up memory on the application server. By default, a session is considered inactive if more than 48 hours pass without any user interaction with the data. |
Collects usage data |
PowerPivot System Service registers usage events for connection, load, unload, query reporting, and server status checks and generates usage data for these events. Usage data is available in PowerPivot Management dashboard. For more information, see PowerPivot Usage Data Collection. |
Monitors server health |
PowerPivot System Service monitors Analysis Services service availability and health. At intervals determined by the PowerPivot Health Statistics Collector timer job, PowerPivot System Service requests system information from each computer that has an Analysis Services instance. CPU and memory usage data appears in server health reports in PowerPivot Management Dashboard. For more information, see PowerPivot Management Dashboard. |
Service Configuration and Administration
PowerPivot System Service is configured through a PowerPivot service application that initializes a physical service instance and uses the settings, application data, and security context that is defined for that particular service application. The service application has properties that you can use to configure timeout thresholds, load balancing, event logging levels, usage data collection, and data refresh, but it does not provide properties that allow you to change the predefined intervals for caching, unloading, or deleting data files.
If SQL Server Setup configured a PowerPivot service application for you, the service is assigned to the Service Application Pool - SharePoint Web Services System that is defined for the farm. As a security best practice, you should change the service account later to run a dedicated account. For more information, see PowerPivot Service Application and Change Service Accounts and Passwords (PowerPivot for SharePoint).
Server configuration and administration is through Central Administration and the PowerPivot Management Dashboard that shows usage data for specific PowerPivot service applications. For more information, see PowerPivot Management Dashboard and SharePoint Administration (PowerPivot for SharePoint).
Do not use other tools and applications, such as SQL Server Configuration Manager or Internet Information Services, to manage the services that are installed with PowerPivot for SharePoint. Using Central Administration to start or stop services, or to change service accounts or passwords, ensures that changes to server state or identity is broadcasted to other servers in the farm. Permissions and logins that are managed by SharePoint are also updated when you use SharePoint tools to administer services that run in the farm.
Service Dependencies and Requirements
PowerPivot System Service runs as a shared service on a SharePoint application server. It is tightly coupled with the local SQL Server Analysis Services PowerPivot instance (MSOLAP$PowerPivot) that runs on the same physical computer. It is the sole conduit for all data loading and unloading requests that are directed to the local Analysis Services instance. Every physical instance of PowerPivot System Service always has a local Analysis Services server instance. SQL Server Setup installs and configures the services together. Both services must share the same state (running or stopped) to avoid request failures.
A PowerPivot System Service must have a service application defined for it. The service application provides the HTTP endpoint. Service operations are not available until you complete this step.
PowerPivot for SharePoint has dependencies on other SharePoint features and infrastructure, including Excel Services, Claims to Windows Token service, and Secure Store Service. For more information about operational dependencies on features provided by SharePoint, see Default Configuration for PowerPivot for SharePoint.
Server Connections Within the Farm
Connections to PowerPivot data originate from client or server applications that query PowerPivot data using the Analysis Services client libraries. The client libraries (either ADOMD.NET, AMO, or the Analysis Services OLE DB provider) determine whether the PowerPivot workbook is inside or outside the farm. The client libraries route the request differently depending on whether the workbook URL is internal or external to the farm.
Requests for inside the farm are sent directly to a PowerPivot service application, bypassing the PowerPivot Web service. The most common example of this type of request is for PowerPivot data embedded in an Excel workbook. In this scenario, Excel Calculation Services uses the Analysis Services OLE DB provider to find a PowerPivot service application in the farm. Direct connections to a PowerPivot service application are not restricted to just Excel Calculation Services. Other applications, such as Reporting Services reports or PerformancePoint dashboards, will also make direct connections to a PowerPivot service application if the report or dashboard that queries the data is running in the same farm.
Requests for outside the farm typically come from client or server applications that request just the PowerPivot data. The most common example of this type of request is when a PowerPivot workbook is used as an external data source in Report Builder or as a data source to another Excel workbook. In this scenario, outside connections retrieve just the PowerPivot data inside an Excel workbook, ignoring any data visualization and interaction objects that are also in the same workbook.
Outside connections are sent to the PowerPivot Web service in the source farm. The PowerPivot Web service finds and establishes a connection to a PowerPivot Service application, which in turn forwards the query to the Analysis Services instance that loads and returns the data. For more information about this service, see the PowerPivot Web Service (PowerPivot for SharePoint).
Note
Opening a PowerPivot workbook in Excel (for example, by entering a SharePoint URL to the workbook in the File Open dialog box) does not involve PowerPivot for SharePoint server components. Opening a server document in Excel 2010 downloads the file from SharePoint to the client workstation. In this scenario, the Excel desktop application and the PowerPivot for Excel add-in, via the in-process version of the Analysis Services VertiPaq engine, load and render the data locally.
The following diagram illustrates different paths a request might follow depending on whether the request is from Excel Services within the farm, or a request from a client tool such as Report Builder or Excel that run outside the farm. Requests for inside the farm use WCF connections to other service applications. Requests for outside the farm use HTTP connections that are received by the PowerPivot Web service.
.gif "component connections and transports")
Connections to PowerPivot data are formulated by the Analysis Services OLE DB provider. Services and applications that consume PowerPivot data must use one or more of the SQL Server 2008 R2 versions of the Analysis Services client libraries. These libraries are installed with PowerPivot for SharePoint or with PowerPivot for Excel. Because they are redistributable, you can also download and install them from the SQL Server 2008 R2 Feature Pack web page on this Microsoft web site.
About Embedded Data Connections
Embedded data is indicated by Data Source = $embedded$ in a PowerPivot data connection string in the parent workbook. For embedded data, the Analysis Services OLE DB provider opens a WCF connection to a PowerPivot service application, which then retrieves data using the URL of the parent workbook. The SharePoint address of embedded data is always the URL of the parent workbook.
Embedded data is only in Excel 2010 workbooks. Neither Reporting Services reports nor PerformancePoint dashboards embed PowerPivot data in the parent document that is rendered in a client application or viewer.
Request Allocation
If you have multiple PowerPivot for SharePoint server instances in a farm, a request for PowerPivot data might be handled by any one of the servers. Each PowerPivot service application maintains a list of available servers and loaded data that it uses when allocating query requests. This information is stored in the PowerPivot service application database. Each PowerPivot service application that you define maintains its own version of the server list and references it for each query request. The list is updated immediately if an Analysis Services instance denies a request or if requests start to fail.
PowerPivot System Services uses a caching system and a load balancing scheme to allocate the request. The server performs the following checks until it finds and loads the data:
Data is already loaded. The PowerPivot service application checks its internal list of active PowerPivot datasets to determine whether the data is already loaded in memory by an Analysis Services instance in the farm. If the data is currently loaded, a user connection is opened for the new request.
Data is not loaded but it is cached. The PowerPivot service application checks its internal list of cached PowerPivot datasets that are already on the file system. If the same PowerPivot dataset is cached on multiple servers, the PowerPivot service application will check server health and build a list of servers that have sufficient resources to accept the request. From that list, a server is picked at random.
Data is neither loaded nor cached. In this case, PowerPivot data must be retrieved from the content library. An allocation scheme is used to determine which server gets the request. Once a server is selected, the PowerPivot service application issues a load request to the Analysis Services instance, passing the SharePoint URL of the PowerPivot workbook.
By default, PowerPivot for SharePoint uses a round-robin scheme that allocates requests in repeating order among two or more PowerPivot servers. Alternatively, you can specify a server health-based scheme. This option instructs PowerPivot service to allocate the request to the server that has the smallest load. For more information about specifying the load balancing scheme, see Create and Configure a PowerPivot Service Application.
Caching PowerPivot Data
If PowerPivot data is inactive for 48 hours hours, the data is unloaded from memory and cached to disk. Caching the data file eliminates long wait times that are incurred when retrieving data from a content database. By caching to disk first, the server can quickly reload the data to service a query request or for data refresh purposes. The data remains in the cache for three days before it is physically deleted from the server.
PowerPivot data is cached on the server on which it was loaded. Although cached data is reused whenever possible, the same data might be cached on multiple servers if it is accessed frequently and other servers were allocated the request.
By default, cached files are stored in the C:\Program Files\Microsoft SQL Server\MSAS10_50.POWERPIVOT\OLAP\Backup folder by the Analysis Services service instance. Read and write access to this folder is granted to both the Analysis Services instance and a PowerPivot service application. Only PowerPivot data files are cached; the Excel workbook that provides the data is never cached or reused once PowerPivot data is extracted.
You can manually delete PowerPivot data files from the Backup folder to free up disk space, but the file will be added to the cache the next time it is requested. You cannot turn off caching for the system or for individual PowerPivot workbooks. All PowerPivot data files are cached to the file system on an unload operation.
Cache Discovery and Maintenance
Information about cached files is maintained by the PowerPivot service application. The service runs a clean up and discovery process that removes unused data and finds new cached data files. Updated information is written to the instance map in the PowerPivot service application database.
Clean up and discovery runs in the background. It is not configurable, and you cannot run it manually to force a clean-up or discovery operation.
Maintenance consists of the following actions:
Removes all cached files that have not been used within the last 3 days. Cached files are removed only after they have been inactive for a total of 5 days (2 days in server memory and 3 days in the cache). The process checks for inactive files every 30 minutes.
Removes all cached files that have been updated or deleted in the content database. For example, if data was refreshed on the server, all cached copies of that data are deleted. This check is performed daily.
Adds new entries for new cached files that are found on the file system in PowerPivot service application databases. The database contains tables that store information about cached files. As new files are added to the cache, the clean-up and discovery process finds the new files and updates the instance map in the PowerPivot service application database. The process verifies instance map information about cached files daily.
Verifies that cached files that are listed in the PowerPivot service application database are actually on disk at the expected location. If a file was deleted on the file system, it will be removed from the list. This check is performed daily.
See Also