Configure Usage Data Collection for (Power Pivot for SharePoint
Applies To: SQL Server 2016 Preview
Usage data collection is a farm-level SharePoint feature. Power Pivot for SharePoint uses and extends this system to provide reports in the Power Pivot Management Dashboard that show how Power Pivot data and services are used. Depending on how you install SharePoint, usage data collection might be turned off for the farm. A farm administrator must enable usage logging to create the usage data that appears in the Power Pivot Management Dashboard.
For information on usage data in the Power Pivot Management Dashboard, see Power Pivot Management Dashboard and Usage Data.
In this topic:
Usage data provides insight into how users are accessing data and resources, but it does not guarantee reliable, persistent data about server operations and user access. For example, if there is a server restart, event usage data will be lost and will not be recoverable. Similarly, if the temporary log files reach maximum size, no new data will be added until the files are cleared. If you require audit capability, consider using the workflow and content type features that SharePoint provides to build out an auditing subsystem for your farm. For more information, look for product and community documentation on the web.
Configure usage data collection in SharePoint Central Administration.
In Central Administration, click Monitoring.
In the Reporting section, click Configure usage and health data collection.
Select the Enable usage data collection.
In the Events to log section, select or clear the checkboxes to either enable or disable the following Analysis Services events:
Power Pivot Connections
Power Pivot Connection event is used to monitor Power Pivot server connections that are made on behalf of a user.
Power Pivot Load Data Usage
Power Pivot Load Data Usage is used to monitor requests that load Power Pivot data into server memory. A load event is generated for Power Pivot data files loaded from a content database or from cache.
Power Pivot Unload Data Usage
Power Pivot Unload Data Usage is used to monitor requests for unloading a Power Pivot data source after a period of inactivity. Caching a Power Pivot data source to disk will be reported as an unload event.
Power Pivot Query Usage
Power Pivot Query Usage is used to monitor query processing times for data that is loaded in an Analysis Services service instance.
Server health and data refresh operations also generate usage data, but there is no event associated with these processes.
You can also update the log file location. For more information, see the next section.
Click OK to save your changes.
Optionally, you can specify whether all messages or just errors are logged. For more information on how to throttle event messages, see Configure and View SharePoint Log Files and Diagnostic Logging (Power Pivot for SharePoint).
Power Pivot usage data is initially stored in usage log files on the local server, and then moved at regular intervals to the Power Pivot service application databases. The log file location is set in Central Administration. The default location is:
C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\logs
To view or change these properties, use the Usage and health data collection page.
On the Home page in Central Administration, click Monitoring.
In the Monitoring section, click Configure usage and health data collection.
In Usage Data Collection Settings, view or modify the file location, name, or maximum file size. If you specify a file size that is too low, the file size will reach the maximum limit and no new entries will be added to it until its contents are moved to the central usage data collection database.
Power Pivot server health and usage data is moved to different locations in the usage data collection system through two timer jobs.:
The “Microsoft SharePoint Foundation Usage Data Import” timer job moves Power Pivot usage to the Power Pivot service application database.
The “Power Pivot Management Dashboard Processing timer job” the data to Power Pivot workbook that is the source of data for built-in administrative reports.
If you need to refresh the administrative reports that appear in the Power Pivot Management Dashboard more frequently, follow these steps.
In Central Administration, click Monitoring.
Click Review job definitions. In the Timer jobs section.
Click Microsoft SharePoint Foundation Usage Data Import.
Click Run Now. If the Run Now button is disabled, click Enable and then click Run Now.
In the Job Definitions list, click Power Pivot Data Management Dashboard Processing Timer Job.
Click Run Now.
Check the reports to view the refresh data. For more information, see Power Pivot Management Dashboard and Usage Data.
Usage data history is stored for events (connections, load, unload, and on-demand query processing) and data refresh (scheduled data processing). Although usage data is collected through the SharePoint usage data collection system, the reporting data is moved to a Power Pivot application database and a reporting database for longer term storage. The usage data history setting controls how long usage data is retained in the Power Pivot application databases. The same limit is applied equally to all types of stored usage data in the same Power Pivot service application database.
In the Usage Data Collection section, in Usage Data History, enter the number of days for which you want to keep a record of data refresh activity for each workbook.
The default is 365 days.
0 specifies unlimited storage where usage data is kept indefinitely.
Alternatively, you can also specify a range between 1 and 5000.
Decreasing the retention period to a smaller number of days will delete any data that exceeds the new limit. For example, changing the value from 365 to 30 will result in usage data deletion for all historical information that occurred more than 30 days ago. Only data from the last 30 days is retained.
Data is actually deleted when the next event occurs. The limit on usage data history is checked only when the system processes an event.
For more information about how usage data is collected and stored, see Power Pivot Usage Data Collection.
Query processing performance is measured against predefined categories that define a request-response cycle by how long it takes to complete. Predefined categories include: Trivial, Quick, Expected, Long-running, and Exceeded. Every request to a Power Pivot server will fall into one of the categories based on time to completion.
Query response information is used in activity reports. Within the reports, each category is used differently to better reveal the performance trends of the Power Pivot system. For example, trivial requests are excluded completely because doing so removes noise in the data and shows more meaningful trends using the remaining categories. In contrast, Long-running or Exceeded request statistics are prominent in the report so that administrators or workbook owners can take corrective action immediately.
Although you cannot add or delete categories, you can define the upper and lower limits that determine where one category stops and the next one begins. If your organization uses Service Level Agreements (SLA) to define acceptable levels of server availability and performance, you can tune these categories to reflect the SLA you create.
In the Usage Data Collection section, in Trivial Response upper limit , enter a value (in milliseconds) that sets the upper boundary for completing a trivial response. Requests that fall into this category typically include server pings, session initiation, and metadata query. The default is 500 milliseconds (or half a second).
In Quick Requests Upper Limit, enter a value (in milliseconds) that sets the upper boundary for completing a quick response. Requests that fall into this category include queries of very small datasets or metadata servers of large datasets. The default is 1000 milliseconds (or 1 second).
In Expected Response Upper Limit, enter a value (in milliseconds) that sets the upper boundary for completing a response in an expected or average time frame. Requests that fall into this category include loading data into a viewer. The default is 3000 milliseconds (or 3 seconds).
In Long Response Upper Limit, enter a value (in milliseconds) that sets the upper boundary for completing long running response. Requests that fall into this category run longer than expected, but within range that is still acceptable. The default is 10000 milliseconds (or 10 seconds).
Any requests that exceed this limit are categorized as Exceeded. There is no configurable threshold for Exceeded. It is inferred from the upper limit you specify on Long Requests Upper Limit. Requests that fall into the Exceeded category run longer than is allowed by an SLA you have defined.
The time-to-report interval specifies how often query statistics are reported to the usage data collection system. Query statistics accumulate in a process and are reported as a single event at regular intervals. You can adjust the interval to write to the log file more or less often.
In the Usage Data Collection section, in Query Reporting Interval, enter the number of seconds after which the server will report the query statistics for all categories (trivial, quick, expected, long running, and exceed) as a single event to the usage data collection system.
The range is 1 to any positive integer.
The default is 300 seconds (or 5 minutes). This value is recommended for dynamic farm environments that run a variety of applications and services.
If you raise this value to a much larger number, you might lose statistical data before it can be reported. For example, a service restart will cause query statistics to be lost. Conversely, if your built-in activity reports show insufficient data, consider decreasing the interval to get time-to-report events more frequently.
You must be a farm or service administrator to modify service application settings. If you defined multiple Power Pivot service applications in the farm, you must modify each one individually.
In SharePoint Central Administration, in Application Management, click Manage service applications.
Find the Power Pivot Service application. You can identify a service application by its type. A Power Pivot service application type is Power Pivot Service Application.
Click the Power Pivot service application name. The Power Pivot Management Dashboard opens.
In Actions, click Configure service application settings. The Power Pivot Service Application Settings page will open.
Usage data collection for Power Pivot service operations can be enabled with default settings to make it immediately available in applications that support the Analysis Services integration feature. The default settings include events that trigger usage data collection, limits on how long usage data is stored, and thresholds for categorizing query response times.
The following table shows the default values for usage data collection configuration.
Analysis Services usage events (Connection, Load, Unload, Requests)
These values are either enabled or disabled.
Query Reporting interval
300 (in seconds)
1 to any positive integer. The default is 5 minutes.
Usage data history
365 (in days)
0 specifies unlimited, but you can also set an upper limit to expire historical data and have it deleted automatically. Valid values for a limited retention period are 1 to 5000 (in days).
Trivial Response Upper Limit
500 (in milliseconds)
Sets an upper boundary that defines a trivial request-response exchange. Any request that completes between 0 to 500 milliseconds is a trivial request, and ignored for reporting purposes.
Quick Response Upper Limit
1000 (in milliseconds)
Sets an upper boundary that defines a quick request-response exchange.
Expected Response Upper Limit
3000 (in milliseconds)
Sets an upper boundary that defines an expected request-response exchange.
Long Running Response Upper Limit
10000 (in milliseconds)
Sets an upper boundary that defines a long running request-response exchange. Any requests that exceed this upper limit fall into the Exceeded category, which has no upper threshold.