Delivering Business Intelligence in a SharePoint Portal - Darryl Robinson

Introduction

Microsoft’s Office Web Parts and Components is a collection of Web Parts and data retrieval services that can be used to include interactive Excel pivot tables, pivot charts, and spreadsheets in a SharePoint 2.0 portal. The web parts may be connected to a variety of data sources, including SQL Server databases, OLAP cubes, SharePoint lists, other Office Web Parts, or any other data source with an available OLE DB provider. This versatility, the collection’s robust feature set, and its low price tag (free) make it an attractive choice for organizations seeking to leverage their investment in Microsoft Office and SharePoint 2.0 to deliver robust business intelligence solutions.

The collection consists of five Web Parts based on familiar Excel components, the Office Datasheet, Office PivotChart, Office PivotTable, Office PivotView, and Office Spreadsheet. Each Web Part has features and capabilities that suit it to particular uses. This article discusses the three components that are most useful in creating business intelligence applications, the PivotTable, PivotChart, and PivotView.

Functionality

PivotTable
PivotTable lists are similar to Excel pivot table reports. They display data in a row and column format that can be reorganized to analyze data in different ways. PivotTable lists are published with an initial presentation or view of the data. End users can perform additional analysis on the published list by displaying different fields and changing the initial view to suit their needs. PivotTable lists are suitable for providing information in a form that users can interact with and analyze.

PivotChart
The PivotChart Web Part is similar to the Excel pivot chart. It makes it easy for users to distinguish trends and patterns in data and to make visual comparisons. PivotCharts may be based on data in external databases, SharePoint lists, or another Office Web Part on the same page (e.g. a PivotTable). When underlying data changes, the chart is automatically updated. Like PivotTable lists, the fields displayed in PivotCharts can be filtered, sorted, and rearranged to analyze data in different ways.

PivotView
The PivotView Web Part combines the functionality of the PivotTable, PivotChart, and Datasheet Web Parts. Using a convenient dropdown menu, users may view and analyze data in one of three different but related views: PivotTable view, PivotChart view, or Datasheet view. The PivotTable view enables all the features of the PivotTable Web Part. The PivotChart view enables all the features of the PivotChart Web Part. The Datasheet view displays data in a tabular format so you can easily sort and filter columns and view details. Modifications made in one view are automatically reflected in the other views.

Key Benefits

The key benefits of the Office Web Parts include:

Support for a wide variety of data sources
The Web Parts can display data from a wide variety of data sources, including SQL Server databases, OLAP cubes, SharePoint lists, and other OLE DB compatible data sources. Their ability to display and analyze SharePoint list data is particularly useful, since it enables analysis without learning the SharePoint database model or transferring list data to an external database or OLAP cube.

A robust feature set
The Web Parts support a large subset of Excel functionality and chart types. This means that users can leverage their experience with Excel to perform complex analysis. Furthermore, individual components can be synchronized to produce rich interactive pages, where a change to one component automatically updates several others.

Reduced operational costs through self-service analysis
End users with Microsoft Office 2003 installed can fully interact with the Web Parts to perform analysis and to create personalized reports and views of the data. This can significantly reduce operational costs by shifting responsibility for data analysis and reporting from the financial analyst and/or IT department to the end user.

Low cost of ownership
The Web Parts can be downloaded from Microsoft at no cost. And while the Microsoft Office 2003 Web Components (bundled with Office 2003) are required to interact with the Web Parts and to access their full functionality, users without Office 2003 can still view, print, select, expand, collapse, and refresh the data in a view-only mode.

Shortcomings

The main shortcoming of the Office Web Parts is their lack of extensibility. The PivotChart component, for example, does not support attaching click-thru events to individual chart elements. Client-side scripting can be used to overcome this shortcoming, as the Web Parts expose a robust object model through VBA (Visual Basic for Applications). However, the ability to easily assign behavior through the Web Part UI would be ideal.

The other significant shortcoming of the Web Parts is their dependency upon Internet Explorer 5.01 Service Pack 2 and the Office 2003 Web Components. Users who do not have the Office 2003 Web Components installed may still view data in the view-only mode previously described. However, when viewed in earlier versions of Internet Explorer and in other browsers, the Web Parts appear as outlined boxes on the page or do not appear at all.

Summary

Organizations that are looking for a way to add business intelligence and interactive data analysis to their corporate portal need look no further than Microsoft’s Office Web Parts. The Web Parts are based on familiar Excel components and support a large subset of Excel’s features, thus reducing support costs and empowering end users to perform analysis. They can be connected to a variety of data sources and integrate seamlessly into SharePoint sites created using Windows SharePoint Services or SharePoint Portal Server. Best of all, the Web Parts are available at no cost. These qualities make the Office Web Parts a smart choice for business intelligence, particularly for organizations that have already invested in Microsoft Office 2003.