Configure Usage Data Collection (PowerPivot for SharePoint)
SharePoint provides a usage data collection system that gathers, stores, and reports on user requests, system events, and server operations in the farm. PowerPivot for SharePoint uses and extends this system to support built-in reports that show how PowerPivot data and services are used.
Depending on how you configured your SharePoint server, usage data collection might be turned off for the farm. As a SharePoint farm administrator, you can use Central Administration to enable and configure usage data collection in the following ways:
Enable usage data collection and choose events that trigger data collection
Configure the timer jobs used in usage data collection
Set log file location and database properties
Limit how long usage data history is stored
Define fast, medium and slow query response categories for reporting purposes
Specify how often query statistics are reported to the usage data collection system
Open the PowerPivot Service Application page to access configuration settings
Learn about the default configuration for PowerPivot usage data collection
Important
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.
Enable usage data collection and choose events that trigger data collection
Use Central Administration to enable usage data collection. If you installed SQL Server PowerPivot for SharePoint using the New Server option, you can skip these steps because usage collection is enabled for you.
In Central Administration, click Monitoring.
In the Reporting section, click Configure usage and health data collection.
Select the Enable usage data collection checkbox.
In Events to log, select or clear the checkboxes to either enable or disable the following Analysis Services events:
PowerPivot Connection event is used to monitor PowerPivot server connections that are made on behalf of a user.
PowerPivot Unload Data Usage is used to monitor requests for unloading a PowerPivot data source after a period of inactivity. Caching a PowerPivot data source to disk will be reported as an unload event.
PowerPivot Load Data Usage is used to monitor requests that load PowerPivot data into server memory. A load event is generated for PowerPivot data files loaded from a content database or from cache.
PowerPivot Query Usage is used to monitor query processing times for data that is loaded in an Analysis Services service instance.
Note
Server health and data refresh operations also generate usage data, but there is no event associated with these processes.
Accept the default values on this page.
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 Event Logging (PowerPivot for SharePoint).
Configure the timer jobs used in usage data collection
PowerPivot server health and usage data is moved to different locations in the usage data collection system through two timer jobs. First, the Microsoft SharePoint Foundation Usage Data Processing timer job moves PowerPivot usage to the PowerPivot service application database. Second, the PowerPivot Data Management Dashboard Processing timer job moves it to a PowerPivot workbook that provides data to the built-in administrative reports. By default, these timer jobs run once a day, in sequential order.
If you need to refresh the administrative reports that appear in the PowerPivot Management Dashboard more frequently, follow these steps.
In Central Administration, click Monitoring.
Click Review job definitions.
Click Microsoft SharePoint Foundation Usage Data processing.
Click Run Now.
In the Job Definitions list, click PowerPivot Data Management Dashboard Processing Timer Job.
Click Run Now.
Check the reports to view the refresh data. For more information, see View Administrative Reports (PowerPivot for SharePoint).
Set log file location and database properties
PowerPivot usage data is initially stored in usage log files on the local server, and then moved at regular intervals to the central usage data collection database for the farm. Both the log file location and the database settings are set in Central Administration. To view or change these properties, use the Usage Logging 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.
In Logging Database Server, view or modify the database settings. For instructions and recommendations, see SharePoint 2010 product documentation.
Limit how long usage data history is stored
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 PowerPivot application database and a reporting database for longer term storage. The usage data history setting controls how long usage data is retained in the PowerPivot application databases. The same limit is applied equally to all types of stored usage data in the same PowerPivot service application database.
Open the PowerPivot Service Application Page.
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.
Click OK.
For more information about how usage data is collected and stored, see PowerPivot Usage Data Collection.
Define fast, medium and slow query response categories for reporting purposes
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 PowerPivot 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 PowerPivot 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.
Open the PowerPivot Service Application Page.
In the Usage Data Collection section, in Trivial Requests 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 Requests 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 Requests 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.
Click OK.
Specify how often query statistics are reported to the usage data collection system
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.
Open the PowerPivot Service Application Page.
In the Usage Data Collection section, in Time to Report 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.
Click OK.
Open the PowerPivot Service Application page to access configuration settings
You must be a farm or service administrator to modify service application settings. If you defined multiple PowerPivot service applications in the farm, you must modify each one individually.
On the Home page in Central Administration, in Application Management, click Manage service applications.
Find the PowerPivot Service application. You can identify a service application by its type. A PowerPivot service application type is PowerPivot Service Application.
Click the PowerPivot service application name. The PowerPivot Management Dashboard appears.
In Actions, click Configure service application settings. The PowerPivot Service Application Settings page will appear.
Learn about the default configuration for PowerPivot usage data collection
Usage data collection for PowerPivot 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.
Setting |
Default Value |
Type |
Valid range |
How to change |
|---|---|---|---|---|
Analysis Services events (Connection, Load, Unload, Requests) |
<enabled> |
Boolean |
These values are either enabled or disabled. |
How do I... |
Time to report interval |
300 (in seconds) |
Integer |
1 to any positive integer. The default is 5 minutes. |
How do I… |
Usage data history |
365 (in days) |
Integer |
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). |
How do I… |
Trivial Request Upper Limit |
500 (in milliseconds) |
Integer |
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. |
How do I… |
Quick Request Upper Limit |
1000 (in milliseconds) |
Integer |
Sets an upper boundary that defines a quick request-response exchange. |
How do I… |
Expected Request Upper Limit |
3000 (in milliseconds) |
Integer |
Sets an upper boundary that defines an expected request-response exchange. |
How do I… |
Long Running Request Upper Limit |
10000 (in milliseconds) |
Integer |
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. |
How do I… |