Power Pivot Usage Data Collection
APPLIES TO: SQL Server Analysis Services Azure Analysis Services Power BI Premium
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.
Usage Data Collection and Reporting Architecture
Power Pivot usage data is collected, stored, and managed using a combination of features from the SharePoint infrastructure and Power Pivot server components. SharePoint infrastructure provides a centralized usage service and built-in timer jobs. Power Pivot for SharePoint adds longer term storage for Power Pivot usage data and reports that you view in SharePoint Central Administration.
In the usage data collection system, event information enters the usage collection system on the application server or Web front end. Usage data moves through the system in response to timer jobs that cause data to move from temporary data files on the physical server to persistent storage on a database server. The following diagram illustrates the components and processes that move usage data through the data collection and reporting system.
Note: Verify usage data collection is enabled. To verify, go to Monitoring in SharePoint Central Administration. For more information, see Configure Usage Data Collection for (Power Pivot for SharePoint.
|1||Usage data collection is triggered by events that are generated by Power Pivot components and Analysis Services data providers in SharePoint deployments. Configurable events that can be turned on or off include connection requests, load and unload requests, and query response timing events that are monitored by the Power Pivot service on the application server. Other events that are managed solely by the server and cannot be turned off. These include data refresh and server health events.
Initially, usage data is collected and stored in local log files using the data collection features of the SharePoint system. The files and their location are part of the standard usage data collection system in SharePoint. The location of the files is the same on every server in the farm. To view or change the location of the logging directory, go to Monitoring in SharePoint Central Administration, and then click Configure usage and health data collection.
|2||At scheduled intervals (every hour by default) the Microsoft SharePoint Foundation Usage Data Import timer job moves usage data from the local files to the Power Pivot service application database. If you have multiple Power Pivot service applications in a farm, each one will have its own database. Events include internal information that identifies which Power Pivot service application produced the event. The application identifiers ensure that usage data is bound to the application that created it.|
|3||Data is copied to an internal reporting database that is available to the Power Pivot Management Dashboard in Central Administration.|
|4||The data source is a Power Pivot workbook that you can access to create custom reports in Excel. There is only one instance of the source workbook. The localized reports are all based on the same source workbook.|
|5||Usage data is presented in reports for Power Pivot service applications for administrators who manage server performance and availability. Localized instances of the workbooks are created for the supported SharePoint languages. For more information, see Reporting on Usage Data in this topic.|
Sources of Usage Data
When usage data collection is enabled, data is generated for the following server events.
|Connections||Server connections made on behalf of a user who is querying Power Pivot data in an Excel workbook. Connection events identify who opened a connection to a Power Pivot workbook. In reports, this information is used to identify the most frequent users, the Power Pivot data sources that are accessed by the same users, and trends in connections over time.||You can enable and disable Configure Usage Data Collection for (Power Pivot for SharePoint.|
|Query response times||Query response statistics that categorize queries based on how long they take to complete. Query response statistics show patterns in how long it takes the server to respond to query requests.||You can enable and disable Configure Usage Data Collection for (Power Pivot for SharePoint.|
|Data load||Data load operations by the Analysis Services service. Data load events identify which data sources are used most often.||You can enable and disable Configure Usage Data Collection for (Power Pivot for SharePoint.|
|Data unload||Data unload operations by Power Pivot service applications. An Analysis Services service unloads inactive Power Pivot data sources if it is not being used, or when the server is under memory pressure or needs additional memory to run data refresh jobs.||You can enable and disable Configure Usage Data Collection for (Power Pivot for SharePoint.|
|Server health||Server operations that indicate server health, measured in CPU and memory utilization. This data is historical. It does not provide real time information about the current processing load on the server.||No. Usage data is always collected for this event.|
|Data refresh||Data refresh operations initiated by the Power Pivot service for scheduled data updates. Usage history for data refresh is collected at the application level for operational reports, and is reflected in the Manage Data Refresh pages for individual workbooks.
Note: For SQL Server 2012 SP1 (11.0.3x) and SharePoint 2013 deployments, data refresh is managed by Excel Services and not the Analysis Services Server.
|No. Data refresh usage data is always collected if you enable data refresh for the Power Pivot service application.|
Services and Timer Jobs
The following table describes the services and data collection stores in the usage data collection system. For instructions on how to override the timer job schedules to force a data refresh of server health and usage data in Power Pivot Management Dashboard reports, see enter link description here. You can see the timer jobs in SharePoint central Administration. Go to Monitoring, then click Check Job Status. Click Review Job Definitions.
|SharePoint Timer Service (SPTimerV4)||This Windows service runs locally on every member computer in the farm and processes all timer jobs that are defined at the farm level.|
|Microsoft SharePoint Foundation Usage Data Import||Every 30 minutes in SharePoint 2010. Every 5 minutes in SharePoint 2013.||This timer job is configured globally at the farm level. It moves usage data from local usage log files to the central usage data collection database. You can run this timer job manually to force a data import operation.|
|Microsoft SharePoint Foundation Usage Data Processing timer job||Daily at 3:00 A.M.||Starting with SQL Server 2012 Power Pivot for SharePoint, this Time job is supported for upgrade or migration scenarios where you may have older usage data still in the SharePoint usage databases. Starting with SQL Server 2012 Power Pivot for SharePoint, the SharePoint usage database is not used for Power Pivot usage collection and Management dashboard workflow. The time job can be manually run to move any remaining Power Pivot related data in the SharePoint usage database to the Power Pivot service application databases.
This timer job is configured globally at the farm level. It checks for expired usage data in the central usage data collection database (that is, any records that are older than 30 days). For Power Pivot servers in the farm, this timer job performs an additional check for Power Pivot usage data. When Power Pivot usage data is detected, the timer job moves the data to a service application database using an application identifier to find the correct database.
You can run this timer job manually to force a check on expired data, or to force a data import of Power Pivot usage data to a Power Pivot service application database.
|Power Pivot Management Dashboard Processing timer job||Daily at 3:00 A.M.||This timer job updates the internal Power Pivot workbook that provides administrative data to the Power Pivot Management Dashboard. It gets updated information that is managed by SharePoint, including server names, user names, application names, and file names that appear in dashboard reports or web parts.|
Reporting on Usage Data
To view usage data on Power Pivot data, you can view built-in reports in the Power Pivot Management Dashboard. The built-in reports consolidate usage data that is retrieved from reporting data structures in the service application database. Because the underlying report data is updated on a daily basis, the built-in usage reports will show updated information only after the Microsoft SharePoint Foundation Usage Data Processing timer job copies data to a Power Pivot service application database. By default, this occurs once a day.
For more information about how to view reports, see Power Pivot Management Dashboard and Usage Data.