PowerPivot Management Dashboard and Usage Data
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. The Management Dashboard provides information related to server health, workbook activity, and data refresh.
Overview of the sections of the Dashboard
Open PowerPivot Management Dashboard
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, memory consumption, and query response times over time so that you can assess whether system resources are nearing maximum capacity or are under utilized. |
|
Actions |
Contains links to other pages in Central Administration including the current service application, a list of service applications, and usage logging. |
|
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. |
The dashboard only shows information for one PowerPivot service application at a time. You can open the management dashboard from two different locations.
Open the dashboard from General Application Settings
-
In Central Administration, in the General Application Settings group, click PowerPivot Management Dashboard.
-
On the main page, select the PowerPivot service application for which you want to view operations data.
Open the dashboard from PowerPivot service application n
-
In Central Administration, in Application Management, click Manage service applications.
-
Click the PowerPivot service application link. The PowerPivot Management Dashboard displays operational data for the current service application only. You cannot switch to another service application.
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.
-
Open the PowerPivot Management Dashboard.
-
Scroll to the Reports section at the bottom of the page.
-
Click PowerPivot Management Data.
-
Save the .odc file to a local folder.
-
Open the .odc file in a text editor.
-
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.
-
Save the File.
-
Start Excel 2010.
-
On the PowerPivot ribbon, click Launch PowerPivot Window.
-
On the Design ribbon in the PowerPivot window, click Existing Connections.
-
Click Browse for More.
-
In the file path, specify the .odc file.
-
Click Open. The Table Import Wizard starts, using the connection string to the PowerPivot workbook that contains usage data.
-
Click Test Connection to verify that you have access.
-
Enter a friendly name for the connection, and then click Next.
-
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.
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.
