Microsoft Office 2007 Enterprise Management Featur

Thanks to John Hancock from the PowerPivot team for putting together this post.
Previous articles have covered some of the features of PowerPivot for Excel, and shown how PowerPivot helps users build really powerful BI applications right in Excel. We have described some of the PowerPivot for SharePoint features on this blog as well as the PowerPivot blog, so for this article I will be focusing on the features that we provide to help IT to manage and understand the self-service BI activity.
Self-Service Business Intelligence and IT
The goal of PowerPivot for SharePoint is to allow people to share their PowerPivot workbooks in a secure, scalable and managed environment. The management experience for PowerPivot for SharePoint is integrated into SharePoint Central Administration, so it can be managed from the same place as other services like Excel Services. The starting point is the PowerPivot Management Dashboard shown below, which gives an overview of the current state and lets you drill into more details.

The dashboard includes a view of system health for the PowerPivot service, including how query response times may be affecting users’ perception of performance and how much memory the PowerPivot service is consuming. But the really interesting information starts when we move beyond the infrastructure details and into how the business is using it.
Finding the Mission Critical PowerPivot Workbooks
There may be hundreds of PowerPivot workbooks in a SharePoint farm, and most of them are only interesting to the small group of people that is using them for a relatively short period. Where things get interesting for IT is when a PowerPivot workbook starts to become “mission critical”. That is, the workbook is being used by lots of people on a regular basis as a key part of a business process.
PowerPivot tracks all of the information about who is using workbooks that are published to SharePoint, and uses this information to help administrators find the most critical ones. The dashboard includes a Workbook Activity chart (see below) that shows which workbooks have the most users and highest number of queries. The size of the circle indicates the workbook size.

The chart is also animated so you can hit play and watch how the workbook activity changes over time. This can help you to determine whether a popular workbook has been steadily increasing in importance to the business over time, or is a recent addition that is part of a crucial but possibly temporary process. The dashboard also includes a Workbook Activity workbook that allows you to drill into more details on workbooks by author, site and user.

Understanding How Data Sources are Used
PowerPivot users can mash up data from local sources like text files and Excel workbooks,Microsoft Office 2007 Enterprise, and corporate data sources like databases, Reporting Services reports, SharePoint lists and data feeds. Helping IT understand which data sources are being widely used, and which data sources are used by mission critical workbooks, is a key part of allowing IT to manage the self-service BI activity.
The Workbook Activity workbook includes a Data Sources PivotTable that allows you to slice by the type of data, source server and database. For example, I can find the list of the most popular workbooks that are using a SQL Server database called AdventureWorks on a server SRVR123. This would help me to determine the possible impact to self-service BI users before making a schema change to that source system.

Extending the Capabilities of the PowerPivot Management Dashboard
We built the dashboard using the same technology that we ship – SharePoint web part pages and Excel Services. The great news is that the underlying data for the dashboard is provided as a PowerPivot workbook, so you can create your own Excel workbooks or Reporting Services reports using it as a data source.
The Reports web part is linked to a regular SharePoint document library, and it includes a data connection file (ODC) that links to the PowerPivot management data. If you click the ODC file, it will launch Excel client and allow you to build PivotTable reports and charts against the data. You can upload your new workbook to the Reports document library and it will show up along with the built-in workbooks that we ship.

The management dashboard is a web part page, so you can customize it with additional web parts showing the reports that you created. Early adopters of PowerPivot have already started taking advantage of the rich information that we provide to extend their dashboards with all kinds of useful analysis, and we are looking forward to seeing how administrators take advantage of this capability.
<div