PowerPivot for Excel and SharePoint
- 4/14/2011
Monitoring with PowerPivot for SharePoint
The PowerPivot Management Dashboard provides administrators responsible for the server side of PowerPivot with the capabilities they need to understand usage patterns of the PowerPivot workbooks in SharePoint and to take appropriate actions. For example, the growing size of a particular workbook may indicate the need to acquire more memory. You can access the PowerPivot Management Dashboard by browsing to SharePoint’s Central Administration and then clicking General Application Settings, as shown in the following illustration.
)
The Dashboard page appears as shown in the following illustration.
)
The PowerPivot Management Dashboard can be broken down into the main areas (Web Parts) described in the following table.
|
Web Part |
Description |
|
Infrastructure—Server Health |
Provides information about infrastructure; it shows the CPU and memory usage for the PowerPivot Analysis Services service. It also contains a histogram of overall query response for the PowerPivot service application. |
|
Workbook Activity |
Provides a high-level representation of the number of users, the number of queries sent to a workbook, and the size of the workbook over time. |
|
Actions |
Allows an administrator to configure PowerPivot-specific settings within a SharePoint farm. |
|
Data Refresh |
Provides a breakdown of the recent activities and recent failures for PowerPivot data refresh in SharePoint. |
|
Reports |
Enables administrators to view source Excel workbooks and databases used by the PowerPivot Management Dashboard. |
Infrastructure—Server Health
This section of the PowerPivot Management Dashboard provides indicators of the server’s health. It does so through the following indicators:
Query Response Times
Average Instance CPU
Average Instance Memory
Activity
Performance
Query Response Times
As shown in the following illustration, the Query Response Times view is the default view of the Server Health Web Part. The purpose of this chart is to provide a quick overview so that you can determine whether the majority of the queries are running as expected or whether they are running too slowly.
)
When query response time increases, you will want to determine which queries are running slowly, and why.
The following table summarizes the default query response time definitions. These definitions can be modified by selecting Central Administration | General Application Settings | PowerPivot | Configure Service Application Settings.
|
Category |
Definition (in milliseconds) |
|
Trivial |
0 < time < 500 |
|
Quick |
500 < time < 1000 |
|
Expected |
1000 < time < 3000 |
|
Long |
3000 < time < 10000 |
|
Exceeded |
>= 10000 |
Average Instance CPU
Switching to the Average Instance CPU view in the Server Health Web Part shows the CPU load on the SharePoint Application Server that has PowerPivot installed, as shown in the following illustration.
)
The preceding illustration shows that for this SharePoint Application Server, CPU load is not an issue because, on average, it’s using less than one percent of the CPU’s capacity.
Average Instance Memory
Memory can become a concern for your environment because the PowerPivot VertiPaq engine loads the workbook in memory. As the number of users and the size of their workbooks grow, they require an increasing portion of the server’s memory. Taking a quick look at the Average Instance Memory view, you can easily see when more memory is being used over time, as shown in the following illustration.
)
Activity and Performance
Although you can toggle between the Infrastructure—Server Health Activity and Performance views, you can get an even better view of this data by using the Workbook Activity and Server Health reports directly. To do that, click in either the Workbook Activity or the Server Health workbook located in the Reports area of the PowerPivot Management Dashboard. (See the second illustration in the section “Monitoring with PowerPivot for SharePoint”.)
Workbook Activity
This area contains two parts: a Chart section and a List section.
Chart
)
This Chart Web Part is a Silverlight control that displays a bubble chart. The chart’s axes represent the number of users and the number of queries sent to a workbook. A sliding bar indicates the date. As you move the pointer over each bubble, the name of the corresponding workbook and the number of users connected to that workbook display, along with the number of queries sent to the workbook. Also, as you move the date sliding bar, the bubble size indicates how the workbook has grown over time.
List
The Workbook Activity - List section provides a quick way to view the current activity attributes (workbook name, number of queries, users, and size) of the server, as shown in the following illustration.
Data Refresh
The PowerPivot data-refresh mechanism performs many activities in the background. In the PowerPivot Management Dashboard, you will find a section dedicated to reporting the recent data-refresh–related activities in the environment
Recent Activity
As the name suggests, this Web Part shows recent data-refresh activity in the environment. It reports the most recent PowerPivot workbook data refreshes, along with the time each refresh completed and its duration, as shown in the following illustration.
Clicking a workbook in the Recent Activity report redirects you to that workbook’s data-refresh history page, where you can find details about the failure. The following illustration shows the data-refresh history page for the BiBool_Contoso.xlsx workbook.
)
Recent Failures
This Web Part focuses on reporting recent data-refresh failures. With this information in hand, you can go back to the Recent Activity Web Part and start investigating the underlying reasons for why a particular data refresh failed.
Reports
As shown in the preceding illustration, the Reports Web Part contains the Excel workbooks that are the source for the PowerPivot Management Dashboard charts. Clicking a workbook opens that workbook in the browser, and you can identify the charts shown in the PowerPivot Management Dashboard.