Analysis Services in SharePoint integrated mode (PowerPivot for SharePoint)
Analysis Services in SharePoint integrated mode is an Analysis Services server instance that supports in-memory storage of compressed data that is calculated in real time, in response to queries for PowerPivot data. In SQL Server 2008 R2, SharePoint integrated mode is only available in a PowerPivot for SharePoint installation. In a PowerPivot for SharePoint installation, it is the SQL Server Analysis Services (PowerPivot) Windows service that is listed in the Services console application in Administrative Tools.
Important
SharePoint integrated mode does not support traditional OLAP storage options. You cannot create or run MOLAP, HOLAP, or ROLAP cube databases on Analysis Services in SharePoint integrated mode. However, you can deploy an Analysis Services instance in SharePoint integrated mode and a native mode Analysis Services instance and run them side-by-side on the same computer. If you install a native mode Analysis Services instance and a SharePoint integrated mode instance on the same computer, only the SharePoint integrated instance will be integrated with the farm.
When installed through PowerPivot for SharePoint, an Analysis Services server instance loads and unloads PowerPivot data in the farm, and responds to queries for PowerPivot data from the local PowerPivot System Service. Within a SharePoint farm, an Analysis Services server instance accepts authorized requests only from the local PowerPivot System Service that is installed with it on the same computer.
Analysis Services loads PowerPivot data as raw, un-aggregated data that is restructured dynamically in response to user interaction with data visualization objects in a workbook. The data is highly compressed to allow for fast performance on the server. A load event is triggered by an on demand request for query processing or by a schedule that defines a data refresh operation. In both cases, the local PowerPivot System Service retrieves a read-only copy of the .xlsx file and issues a load request to Analysis Services. Analysis Services opens the workbook, extracts the PowerPivot data, and loads it into memory.
Analysis Services will unload the data if no queries are received or if a newer version of the data becomes available. If there is no memory pressure on the server or newer version to load, the server will hold data in memory for 48 hours before unloading it and releasing memory. Data will be unloaded sooner to make room for other PowerPivot data files if the system is under memory pressure. In this case, the file will be cached so that it is more readily available for subsequent requests. Data is always unloaded to the file cache first. Cached files are automatically removed after three days, but you can manually delete specific files if you need to free up space on your computer hard drive. For more information about caching, see PowerPivot System Service.
Click the following links to go to a specific section in this topic:
Service Configuration and Administration
Service Dependencies and Requirements
Service Configuration and Administration
An Analysis Services instance in SharePoint integrated mode is installed as a named SQL Server Analysis Services 'PowerPivot' instance. It is provisioned by SQL Server Setup using the service account specified for Analysis Services during installation.
A SharePoint integrated mode instance of Analysis Services uses an in-memory data storage and compression methodology. Reconfiguring a SharePoint integrated mode instance of Analysis Services to support MOLAP, ROLAP, or HOLAP storage modes is unsupported.
Server administration must be through SharePoint Central Administration and the PowerPivot Management Dashboard. Never use the Services console application or SQL Server Configuration Manager to change the service account. Using Central Administration ensures that sever configuration changes are broadcasted to other servers in the farm. Permissions and logins that are managed by SharePoint are also updated when you use SharePoint tools to configure and administer services that run in the farm. In addition, Central Administration uses a timer job called One or more services have started or stopped to restart services that it thinks should be running. If you stop Analysis Services using a non-SharePoint tool, the service will be restarted when the timer job runs.
Using Central Administration, you can configure the following:
Service identity. You can use the Configure Service Accounts page in SharePoint Central Administration to update an account or password. For more information, see Change Service Accounts and Passwords (PowerPivot for SharePoint).
Allowable request types. You can configure a server to support either data refresh or queries. For more information, see Configure Dedicated Data Refresh or Query-Only Processing.
Server state. You can start or stop the service in the Manage services on this server page. For more information, see Start or stop services on a PowerPivot for SharePoint instance.
Using PowerPivot Management Dashboard, you can do the following:
- View server health and activity reports to understand how well the servers are performing. Reports and Web parts on this dashboard can help you determine whether you need to add or move a PowerPivot for SharePoint instance to another computer. For more information, see PowerPivot Management Dashboard and Add PowerPivot Servers to a SharePoint Farm.
Service Dependencies and Requirements
The Analysis Services service runs as a backend service on an application server. All requests and responses that are directed to and from the service go through a local PowerPivot System Service instance that runs on the same computer.
Important
SQL Server Analysis Services (PowerPivot) is tightly coupled with the local PowerPivot System Service that is on the same computer. Unless you are troubleshooting a specific issue, both the Analysis Services server instance and the PowerPivot System Service should be started and stopped together. For more information, see Start or stop services on a PowerPivot for SharePoint instance.
A PowerPivot service application provides the application pool for a PowerPivot System Service instance. The application pool identity must have Analysis Services administrative permissions on the Analysis Services instance to issue load and unload data requests on the server. These permissions are automatically granted and updated when you use Central Administration to change service accounts or passwords. For more information, see Change Service Accounts and Passwords (PowerPivot for SharePoint).
See Also