PowerPivot Management Dashboard
From its inception, a path to enterprise migration has been a part of PowerPivot. The PowerPivot Management Dashboard is a component of that strategy, ensuring usage monitoring was included as “out-of-the-box” functionality for the PowerPivot for SharePoint installation. For example, the dashboard includes monitoring of query performance over time in a manner very similar to the SQL Server 2008 Performance Data Warehouse feature. This is a monitoring tool for PowerPivot, written in PowerPivot.
Accessing the PowerPivot Management Dashboard
The PowerPivot Management Dashboard is most directly accessed via SharePoint Central Administration. If you are using a dedicated development environment such as that described in Chapter 6, then access to SharePoint Central Administration will be no problem. However, if you are using an enterprise SharePoint farm installation, access may not be forthcoming from your SharePoint Administrator.
In the event you do have access to SharePoint Central Administration, there are at least two methods for accessing the PowerPivot Management Dashboard. First, from SharePoint Central Administration, choose the “Manage service applications” option from the Application Management group of management hyperlinks. When the list of service applications renders on the next screen, click the link for the PowerPivot service application you wish to analyze. For an out-of-the-box installation, this will be Default PowerPivot Service Application. The next screen consists of a number of items to understand and predict the health of your PowerPivot for SharePoint installation.
Using the Management Dashboard
In the upper left-hand corner of the PowerPivot Management Dashboard is a Web Part containing a multipurpose report. This report reveals a number of leading indicators of system performance over time. As depicted, the pulldown control allows the user to select between the following diagnostic reports: Query Response Times, Average Instance CPU, Average Instance Memory, (Workbook) Activity, and (Overall Server) Performance. These reports should be taken as a whole to help you understand and perhaps forecast future PowerPivot for SharePoint performance.
One of the truly great features of the PowerPivot Management Dashboard is the automatic nature of data collection. This is a double-edged sword with respect to development environments. If you are running PowerPivot for SharePoint only occasionally in a virtual machine or a bootable image, the SharePoint timer jobs that would normally gather data will not have actual activity. This can cause the PowerPivot Management Dashboard to be underpopulated with data, and thus the diagnostic reports will be rather unimpressive. I use a virtual machine for the example in this book, and I have made a point to run that virtual machine consistently for a number of days. An alternative to populate your virtual machine–based Management Dashboard would be to open a number of workbooks over time, and ensure the data collection jobs for the Management Dashboard are executed either on schedule or via the Execute Now option within Timer Job Schedule management.
Query Response Times
The Query Response Times report is a graphical representation of the categorization of query workload over time. In Figure 7-17, we have query responses for the past four days, graphically indicating the response time category by color. The installation defaults for these time periods are listed in Table 7-1.
Table 7-1. Default Query Response Configuration
| Category | Does not Exceed |
| Trivial | 500 milliseconds |
| Quick | 1,000 milliseconds |
| Expected | 3,000 milliseconds |
| Long | 10,000 milliseconds |
| Exceeded | Greater than ‘Long' upper limit |
Figure 7-17. Query Response Times report
The query response times classification upper bounds are installation-configurable. From the Management Dashboard, in the upper right-hand corner under the “Configure service application settings”, link in the Actions section. The resulting page will allow for configuration of Trivial through Long upper bounds. The Exceeded category is not directly configurable. It is the derived category for any query response taking longer than the Long category upper bound.
The occasional “trough” in query response times, that is the occurrence of query responses consistently in greater response time categories, can be an indication of which time periods are contributing to the greatest server workload and may benefit from tuning. Additionally, cross-checking the Query Response Times report with the Workbook Activity Chart will allow precise identification of resource-intensive workbooks.
Average Instance CPU
With the idea that no single diagnostic image from the Management Dashboard should be taken in isolation, the Server Health report also includes a graph of the measured CPU activity percentage over time. The goal of this report is to assist the SharePoint Administrator in assessing overall server under-utilization or alternatively if the server is consistently exceeding capacity. Figure 7-18 is an example showing the utilization for the “life span” of my virtual machine instance for about five days.
Figure 7-18. Average Instance CPU report
Average Instance Memory
Another key indicator of overall server capacity is how much memory is being consistently used by PowerPivot for SharePoint. When the average instance memory is near peak configured memory on a consistent basis, the server is being over-utilized relative to capacity. Figure 7-19 depicts the historical memory utilization for one of the virtual machines used in the production of this book.
Figure 7-19. Average Instance Memory report
Activity
The Activity report differs from the others, because of the interactivity incorporated into it. While the entire PowerPivot Management Dashboard is built on PowerPivot for SharePoint, the Activity report is the first report that is rendered as a PowerPivot workbook. For example, the vertical slicers for Machine, Year, and Month allow for a customized view of the overall server metrics. The number of connections, queries executed, workbooks loaded (and unloaded), and user volume are all charted based on the slicer settings.
The example in Figure 7-20 shows a large volume of queries occurring on December 15, causing the line for that date to be shaded in red. The days before and after appear to be nominal, so the activity on the 15th could have been a one-time anomaly. However, it could have been the result of a monthly operation that will occur again on the 15th of the next month. Both the PowerPivot Management Dashboard operational facts as well as business operations schedule must be evaluated for tuning decisions.
Figure 7-20. Activity report
Performance
Similar to the Activity report, the Performance report is an interactive PowerPivot workbook that can be sliced by Machine, Year, and Month. The PivotTable indicates visually relative health by CPU, memory, and query execution classification.
Figure 7-21 contains an example of the Performance report. Red highlighting is used to indicate peak levels of CPU and memory usage. Conversely, the lowest levels of CPU and memory resource utilization are formatted with a green cell background to visually indicate the dip in usage.
Figure 7-21. Performance report
Workbook Activity Chart
The Workbook Activity Chart, illustrated in Figure 7-22, is one of the more visually impressive and interactive tools in the PowerPivot Management Dashboard. This chart is comprised of the workbooks currently on the server, with a bubble representing each. The relative size of the bubble is indicative of the relative size of the workbook. In the case of the example virtual machine, all of the workbooks are of similar size. The vertical axis indicates the average number of users per week. The horizontal axis is the number of queries executed by the workbook to the PowerPivot for SharePoint database engine.
The interactive part of this chart is the slider, just below the horizontal chart axis. This control allows the administrator to travel through time, by week, in order to understand which workbooks are gaining in popularity (user count) and activity (query executions). As the slider is repositioned, each bubble may shift position on both axes and in size (as relative data size changes).
One of the more valuable uses of this chart is to understand when a PowerPivot solution, based on user community and/or query activity, may have outgrown the PowerPivot environment and be a candidate for migration to a SQL Server Analysis Services solution.
Figure 7-22. Workbook Activity Chart
Workbook Activity List
The Workbook Activity List is a list of workbooks on the observed server. The report is provided as a convenience for the SharePoint Administrator. Understanding which workbooks are requiring resources from the other elements of the PowerPivot Management Dashboard is sometimes a good first step in tuning. One of the next steps would be to examine the actual high-demand workbooks.
Figure 7-23 shows the report. You can see that it lists each of the workbooks from the usage data collection period.
Note The data collection period is the interval of time for which PowerPivot for SharePoint has been gathering query performance metrics. When PowerPivot for SharePoint is installed, a default schedule for SharePoint timer jobs is established. These jobs gather workbook query activity for presentation in the PowerPivot Management Dashboard.
The list includes the name, the number of users and queries, and the overall size for each workbook on the server. The report is sorted by popularity by default, in descending order by Users. However, sorting by one of the other metrics, Query or Size, is a matter of clicking the column header. The sort order can be inverted by clicking the column header of the current sort column.
Figure 7-23. Workbook Activity List
Configuring Usage Data Collection
Upon installation of PowerPivot for SharePoint, default values are used to establish the boundaries between the categories of query performance: Trivial, Quick, Expected, Long, and Exceeded. While an installation default is set, you can also adjust the time threshold for a reported usage event. Finally, the default is for the PowerPivot Management Dashboard to maintain 365 days of usage data. That history interval can be changed as well.
In order to alter the usage data collection parameters, click the link in the Actions section in the upper-right corner of the PowerPivot Management Dashboard, as illustrated in Figure 7-24.
Figure 7-24. Service application settings
The resulting page will contain all of the settings for the PowerPivot for SharePoint service application, similar to Figure 7-25. Scrolling to the bottom of the page will navigate to the section labeled Usage Data Collection. You may change the parameters by editing the values in the text box corresponding to each setting. Click the OK button to apply your settings.
