• Article

PowerPivot Management Dashboard

PowerPivot Management Dashboard is a collection of predefined reports and web parts in SharePoint Central Administration that help you administer a SQL Server PowerPivot for SharePoint deployment.

Prerequisites

Dashboard Roadmap

Open PowerPivot Management Dashboard

Edit PowerPivot Dashboard

Create Custom Reports for PowerPivot Dashboard

Source Data in Dashboards

Prerequisites

You must be a service administrator to open PowerPivot Management Dashboard for a PowerPivot service application that you manage.

Dashboard Roadmap

PowerPivot Management Dashboard contains Web Parts and embedded reports that drill down into specific information categories. The following list describes each part of the operations dashboard:

Dashboard

Description

Infrastructure - Server Health

Shows trends in CPU usage and memory consumption over time so that you can assess whether system resources are nearing maximum capacity or are under utilized.

Infrastructure - Quality of Service

Shows query response times for the server.

Actions

Contains links to other pages in Central Administration.

Workbook Activity - Chart

Reports on frequency of data access. You can learn how often connections to PowerPivot data sources occur on a daily or weekly basis.

Workbook Activity - Lists

Reports on frequency of data access. You can learn how often connections to PowerPivot data sources occur on a daily or weekly basis.

Data Refresh - Recent Activity

Reports on the status of data refresh jobs, including jobs that failed to run. This report provides a composite view into data refresh operations at the application level. Administrators can see at a glance the number of data refresh jobs that are defined for the entire PowerPivot service application.

Data Refresh - Recent Failures

Lists the PowerPivot workbooks that did not complete data refresh.

Reports

Contains links to reports that you can open in Excel.

Service Application Settings

As a convenience, the dashboard provides a link to the configuration settings page for the current service application so that you can change timeout values, the load balancing scheme, or other settings.

Open PowerPivot Management Dashboard

How you open a PowerPivot dashboard determines the view you work with. If you open it from the service application, you will only see the operational data that applies to that PowerPivot service application, even if you are an administrator of other PowerPivot service applications in the farm.

To open a dashboard with the ability to switch service applications

  1. In Central Administration, in General Application Settings in the PowerPivot section, click Management Dashboard.

  2. On the main page, select the PowerPivot service application for which you want to view operations data. The dashboard only shows information for one PowerPivot service application at a time.

To open a dashboard view into a single service application

  1. In Central Administration, in Application Management, click Manage service applications.

  2. Click the PowerPivot service application link. The PowerPivot Management Dashboard displays operational data for the current service application only, with no ability to switch to another application.

Edit PowerPivot Dashboard

If you have expertise in dashboard development or customization, you can edit the dashboard to include new web parts. You can also edit the web part properties that are included in the dashboard.

Create Custom Reports for PowerPivot Dashboard

For reporting purposes, PowerPivot usage data and history is kept in an internal PowerPivot workbook that is created and configured along with the dashboard. If the default reports do not provide the information you require, you can create custom reports in Excel based on the workbook. Both the workbook and any custom reports that you create will be preserved if you upgrade or uninstall the PowerPivot server software later. The workbook and reports are stored in the PowerPivot Management library of the Central Administration web application. This library is not visible by default, but you can view the library using the View All Site Content action in Site Actions.

To help you get started, PowerPivot Management Dashboard provides an Office Data Connection (.odc) file to the workbook. After you create the .odc file, you can use it in Excel to create additional reports.

Note

In this release, you must edit the file to avoid the following error when attempting to use the .odc file in Excel: "Initialization of the data source failed". The auto-generated .odc file includes two parameters that are not supported by the MSOLAP OLE DB provider. The following instructions provide the workaround for removing the parameters.

You must be a farm or service administrator to build reports that are based on the PowerPivot workbook in Central Administration.

  1. Open the PowerPivot Management Dashboard.

  2. Scroll to the Reports section at the bottom of the page.

  3. Click PowerPivot Management Data.

  4. Save the .odc file to a local folder.

  5. Open the .odc file in a text editor.

  6. In the <odc:ConnectionString> element, scroll to the end of the line and remove Embedded Data=False, and then remove Edit Mode=0. If the last character in the string is a semicolon, remove it now.

  7. Save the File.

  8. Start Excel 2010.

  9. On the PowerPivot ribbon, click Launch PowerPivot Window.

  10. On the Design ribbon in the PowerPivot window, click Existing Connections.

  11. Click Browse for More.

  12. In the file path, specify the .odc file.

  13. Click Open. The Table Import Wizard starts, using the connection string to the PowerPivot workbook that contains usage data.

  14. Click Test Connection to verify that you have access.

  15. Enter a friendly name for the connection, and then click Next.

  16. In Specify MDX Query, click Design to open the MDX query designer to assemble the data you want to work with, and then create PivotTable or PivotChart reports to visualize the data in Excel.

Source Data in Dashboards

Dashboards, reports and web parts show data from an internal reporting database that pulls data from the system, PowerPivot application databases, usage data collection database in SharePoint, and other sources. The internal database is a PowerPivot data source. The structure of the data source is fixed. Although you can use the data source to create new reports, you must not modify the structure in anyway that breaks the predefined reports that use it.

For more information about how data is collected, see PowerPivot Usage Data Collection

To capture data about the PowerPivot server system, the reporting database assumes that event messaging, data refresh history, and other usage history is enabled for each PowerPivot service application. The server and usage data that is gathered during normal server operations is the origin of source data that ends up in the internal reporting database. If you turn off events or usage history, the composite reports will be incomplete or erroneous.