Configuration Setting Reference (Power Pivot for SharePoint)


Applies To: SQL Server 2016

This topic provides reference documentation for configuration settings used by Power Pivot service applications in a SharePoint farm. If you are using PowerShell script to configure a server, or if you want to look up information for a specific setting, the information in this topic provides detailed descriptions.

Configuration settings are set for each Power Pivot service application. Within a farm, you can create multiple service applications as a way to configure independent logical instances of the same physical service instance. Configuration settings are stored in the Power Pivot application database created for each service application that you configure.

If you change configuration settings, the changes are picked up immediately and used for subsequent requests and connections. Operations that are in progress are governed by the settings that were in effect when the operation began.

Click the following links to read about specific configuration areas:

Data Load Timeout

Connection Pools

Load Balancing

Data Refresh

Usage Data Collection

For instructions on how to create a Power Pivot service application, see Create and Configure a Power Pivot Service Application in Central Administration.

Power Pivot data is retrieved and loaded by Analysis Services server instances in the farm. Depending on how and when the data was last accessed, it will either be loaded from a content library or from a local file cache. Data is loaded into memory whenever a query or processing request is received. To maximize overall server availability, you can set a timeout value that instructs the server to stop a load data request if it cannot be completed within the allotted time.

NameDefaultValid ValuesDescription
Data Load Timeout1800 (in seconds)1 to 3600Specifies the amount of time a Power Pivot service application will wait for a response from a specific Analysis Services server instance.

By default, the service application will wait 30 minutes for a data payload from the Engine service instance to which it forwarded a specific request.

If the Power Pivot data source cannot be loaded within this period of time, the thread will be stopped and a new one will be started.

The Power Pivot service application creates and manages connection pools to enable connection reuse. There are two types of connection pools: one for data connections to read-only data, and a second for server connections.

Data connection pools contain cached connections for Power Pivot data source. Each connection pool is based on the context that was set when the database was loaded. This context includes the identity of the physical service instance, the database ID, and the identity of the SharePoint user who is requesting data. A separate connection pool is created for each combination. For example, requests from different users of the same database running on the same server will consume connections from different pools.

The purpose of a connection pool is to use cached connections for read-only requests for the same Analysis Services database by the same SharePoint user. The Power Pivot service instance is the server that has the data loaded in memory. The database ID is an internal identifier for the in-memory data structures of the data model (a model is instantiated as an Analysis Services cube database). Version information is implicitly incorporated in the identifier.

Server connection pools contain cached connections from a Power Pivot service application instance to an Analysis Services server instance, where the service application is connecting with Analysis Services Sysadmin permissions on the Analysis Services server. These connections are used to issue a load database request and monitor system health.

Each type of connection pool has upper limits that you can set to ensure best use of system memory for connection management.

NameDefaultValid ValuesDescription
Connection Pool Timeout1800 (in seconds)1 to 3600.This setting applies to data connection pools.

It specifies how much time an idle connection can remain in a connection pool before it is removed.

By default, the service application will remove a connection if it is inactive for more than five minutes.
Maximum User Connection Pool Size1000-1, 0, or 1 to 10000.

-1 specifies an unlimited number of idle connections.

0 means no idle connections are kept. New connections to a Power Pivot data source must be created each time.
This setting applies to the number of idle connections in all data connection pools created for a specific Power Pivot service application instance.

Individual connection pools are created for unique combinations of a SharePoint user, Power Pivot data, and service instance. If you have many users accessing a variety of Power Pivot data sources, server performance might benefit from an increase in connection pool size.

If there are more than 100 idle connections to a Power Pivot service instance, newly idle connections are disconnected rather than returned to the pool.
Maximum Administrative Connection Pool Size200-1, 0, or 1 to 10000.

-1 specifies an unlimited number of idle connections.
The maximum number of idle server connections in all administrative connection pools created for Power Pivot service application connections to an Analysis Services server instance. Server connections are used for requests to load databases and to save changes back to the SharePoint database.

One of the functions that the Power Pivot service performs is to determine where Analysis Services data will be loaded among the available Power Pivot service instances. The AllocationMethod setting specifies the criteria against which a service instance is selected.

NameDefaultValid ValuesDescription
Allocation MethodRoundRobinRound Robin

Health Based
A scheme for allocating load requests among two or more Analysis Services server instances.

By default, the Power Pivot service will alternate requests based on server health. Health based allocates requests to the server that has the most system resources available based on available memory and CPU utilization.

Round robin rotates requests amongst the available servers in sequential order, regardless of current load or server health.

Specify the range of hours that defines a normal or typical business day for your organization. These configuration settings determine when after-hours data processing occurs for data refresh operations. After-hours processing can begin at the end time of the business day. After-hours processing is a schedule option for document owners who want to refresh a Power Pivot data source with transactional data that was generated during normal business hours.

NameDefaultValid valuesDescription
Start time04:00 a.m.1 to 12 hours, where the value is a valid integer within that range.

Type is Time.
Sets the lower limit of a business hour range.
End time08:00 p.m.1 to 12 hours, where the value is a valid integer within that range.

Type is Time.
Sets the upper limit of a business hour range.
Power Pivot Unattended Data Refresh AccountNoneA target application IDThis account is used to run data refresh jobs on behalf of a schedule owner.

The unattended data refresh account must be defined in advance before it can be referenced in the service application configuration page. For more information, see Configure the Power Pivot Unattended Data Refresh Account (Power Pivot for SharePoint).
Allow users to enter custom Windows credentialsEnabledBooleanDetermines whether the scheduled data refresh configuration page shows an option that allows a schedule owner to specify Windows user account and password to run a data refresh job.

Secure Store Service must be enabled in order for this option to work. For more information, see Configure Stored Credentials for Power Pivot Data Refresh (Power Pivot for SharePoint).
Maximum Processing History Length3651 to 5000 daysDetermines how long data refresh history is retained in the Power Pivot service application database. For more information, see Power Pivot Usage Data Collection.

Usage reports that appear in the Power Pivot Management Dashboard can provide important information about how Power Pivot-enabled workbooks are used. The following configuration settings control aspects of usage data collection for Power Pivot server events that are subsequently presented in usage or activity reports.

NameDefaultValid valuesDescription
Query Reporting Interval300 (in seconds)1 to n seconds, where n is any valid integer.To ensure that usage data collection does not consume too much of the data transfer capacity of the farm, query statistics are collected on each connection and reported as a single event. The Query Reporting Interval determines how often an event is reported. By default, query statistics are reported every 5 minutes.

Because connections are immediately closed as soon as a request is sent, the system generates a very large number of connections for even a single user accessing a single Power Pivot data source. For this reason, connection pools are created for each user and Power Pivot data source combination so that once a connection is created it can be reused by the same user for the same data. Periodically, at intervals specified through this configuration setting, the Power Pivot service application reports the usage data for each connection in the connection pool.

Increasing the time-to-report value will cause fewer events to be logged. However, if you set it too high, you risk losing event data if the server restarts or a connection is closed.

Lowering the value will cause more events to be logged with greater frequency, adding more Power Pivot-related usage data to the data collection system in the SharePoint usage database.

Generally, do not change this configuration setting unless you are trying to resolve a specific problem (for example, if the usage database is growing too quickly as a result of Power Pivot usage data).
Usage Data History365 (in days)0, or 1 to n days, where n is any valid integer.

0 means that history is always retained and never deleted.
By default, usage data is kept for one year in the Power Pivot service application database. Records that are older than one year are dropped from the database.

A check for expired historical data occurs daily, when the Microsoft SharePoint Foundation Usage Data Processing job runs. The timer job will read this setting and trigger a data deletion command for expired history in the Power Pivot service application database.
Trivial Response Upper Limit500 (in milliseconds)1 to n milliseconds, where n is any valid integer.By default, the threshold for trivial requests is half a second.

Trivial requests include server pings, requests for metadata, and starting sessions.
Quick Response Upper Limit1000 (in milliseconds)1 to n milliseconds, where n is any valid integer.By default, the threshold for quick requests is one second.

Quick requests are those that have an extremely small dataset, or requests for metadata that span large member sets.
Expected Response Upper Limit3000 (in milliseconds)1 to n milliseconds, where n is any valid integer.By default, the threshold for expected requests is three seconds.

This threshold sets the upper limit of an expected query time.
Long Response Upper Limit10000 (in milliseconds)1 to n milliseconds, where n is any valid integer.By default, the threshold for long requests is ten seconds.

These are requests that run longer than expected, but still fall within an acceptable range.

Create and Configure a Power Pivot Service Application in Central Administration
Power Pivot Data Refresh with SharePoint 2010
Configure Usage Data Collection for (Power Pivot for SharePoint
Configure Power Pivot Service Accounts
Power Pivot Management Dashboard and Usage Data

Community Additions