Microsoft Office XP PivotTables
Microsoft added PivotTable reports for multidimensional analysis to Microsoft Excel in 1994 and enhanced the reports to retrieve data directly from OLAP cubes in Office 2000. At the same time, Office 2000 added a new set of ActiveX controls—the Office Web Components, which included an ActiveX version of a PivotTable report. In Office XP, both the Excel PivotTable report and the Web Component PivotTable control have been enhanced, but the development focus seems to be directed at the Web component.
In our review, we analyzed both the PivotTable control from the Office XP Web Components (which we call the OWC PivotTable) and the Excel PivotTable report (which we call the Excel PivotTable). Even though the two tools are similar in many ways, the OWC PivotTable has more power and flexibility for analyzing data, and the Excel PivotTable is stronger for formatting and reporting. Figure 3 shows a sales report in the OWC PivotTable control window.
Suitability for Specific Roles
For the power analyst, both Office PivotTable tools are good. The tools are easy to navigate, although the screen layout can become cumbersome when a cube contains a dimension that has many levels. The OWC PivotTable is particularly strong for sorting and finding the top products. Excel has the added advantage of familiarity to most power analysts, and the tight integration of the two tools makes the OWC PivotTable convenient for distributing reports that the analysts can bring into Excel for further manipulation.
For the data gatherer, the Excel PivotTable is fair, and the OWC PivotTable is good. The Excel PivotTable doesn't let you control the user experience: If you permit any manipulation of a PivotTable in Excel, you allow all forms of manipulation. In contrast, the OWC PivotTable lets a report designer add some restrictions, such as preventing the user from moving a dimension to the slicer area. The PivotTables let you guide the user experience to different degrees. The OWC PivotTable requires the sophistication of a container such as FrontPage, but Excel is limited to the standard technique of creating a workbook that contains multiple initial reports on different sheets.
For the report user, the OWC PivotTable is poor, largely because it lacks a zero-footprint client. The Excel PivotTable, however, is good, mostly because the powerful GetPivotData function, combined with Excel's full formatting and printing capabilities, makes this tool an excellent option for creating standardized reports. Even if you use one of the zero-footprint tools for widespread deployment, you might want to use Excel 2002 PivotTables to build production reports.
Strengths and Weaknesses
On the up side, Office has an obvious advantage: If you already own an Office license, the PivotTable tools are free. Actually, the licensing scheme for the XP version of the OWC PivotTable is cost-effective but somewhat confusing. Even if you haven't deployed the new Office release, you can use the OWC PivotTable to generate reports if you hold a license for Office XP. You only need an appropriate version of Internet Explorer (IE).
The OWC PivotTable is the only tool we reviewed that makes it easy to hide levels from a dimension while simultaneously filtering for the top or bottom values. In other words, in a product dimension that includes category, subcategory, and product levels, you can easily show the top five best-selling products for each category while ignoring the subcategory level.
On the down side, the PivotTable tools are the only products we reviewed that don't provide a zero-footprint thin-client option. Because no thin-client version of the OWC PivotTable exists, to use a PivotTable report, Web clients need to permit the automatic download of the ActiveX control along with the current version of Microsoft Data Access Components (MDAC). Although the OWC PivotTable is enabled for Internet access (when Microsoft IIS provides the authentication), the control isn't optimized for Internet use and performs much better in a client/server environment. The OWC PivotTable consists of only the ActiveX control and doesn't include a container application. Therefore, you have to use another application—such as FrontPage, Access, or Excel—to create and publish a page that contains an OWC PivotTable.
All the products except OWC PivotTable allow conditional formatting (sometimes known as stoplighting or exception highlighting), in which higher values are formatted differently than lower values. However, the Excel PivotTable allows standard Excel conditional formatting, and both PivotTable reports display server-side cell formatting—but only if you use Visual Basic for Applications (VBA) to change the value of a property that doesn't appear in the UI.
Neither Office PivotTable handles ragged dimensions very well. (A ragged dimension has holes between a member and its parent.) At best, the reports simply display a placeholder member where each hidden member would be. If ragged dimensions are crucial to your reporting needs, you might want to avoid the Office PivotTable tools.
Note that creating an Excel PivotTable report requires you to take the extra step of creating an Office Query or Office Data Connection file before generating the report. However, Excel does create the file automatically as part of the PivotTable wizard.
Prev. page
1
2
3
[4]
5
6
next page 
