Getting Down to the Nitty-Gritty

If you need more than a high-level review to make a decision about your OLAP tool, here are some details that might help. We developed a test database and standardized series of tests for the products. For space considerations, this sidebar recaps only the analysis highlights.

We designed the test database—adapted from the SQL Server 2000 FoodMart sample database—to facilitate standardized testing of each feature. The test matrix evaluates over 100 features, which we grouped into 18 subcategories, then grouped into four categories. The four general categories are Content, Presentation, Environment, and Customization. The Content category includes all actions a user takes to move values into the body of the report. The Presentation category includes all actions a user takes to control the report’s appearance. The Environment category includes an assessment of how successfully the tool integrates with other processes in the organization. The Customization category determines how easily a developer can use custom MDX or programming to enhance the application’s behavior.

To each task in the test matrix we assigned a subjective weighting, then used the weightings to summarize the scores for each subcategory and category. Table A shows the category and subcategory ratings for each product. To help you interpret the numbers, the table also shows the weightings and the average across all tools. For each entry, products that earned a score more than one standard deviation above the average are highlighted with a green background, and products that earned a score more than one standard deviation below the average are highlighted with a magenta background. In the detailed analysis section, we provide a brief explanation for the high and low scores in each category. You can set up your own test by downloading the.xls test matrix file and the .cab and .mdb sample database files from the main article, "Client Tools for Analysis Services," at http://www.sqlmag.com, InstantDoc 26399.

Content

The Content category includes every action a user takes to move values—numbers as well as text—into the body of a report. All applications let a user perform the standard slice, dice, and pivot operations, but what else do they let a user do? This category examines the additional operations that the products enable.

Manipulate Axis Sets. All products let you manipulate the sets on each axis—drilling down and up, showing descendants, and filtering for top members. However, the OWC PivotTable was the only product that could show nested top members; when you use this PivotTable, you can easily show the top three products for each of the top three subcategories for each of the top three categories. If you remove category and subcategory levels, the report smoothly switches to showing top three products globally.

Dimensions. Ragged dimensions are hard to display in a browser that presents levels in separate columns. In Analysis Manager, you can create ragged dimensions by using the Skipped Levels on a parent-child dimension or Hide Member If properties on a balanced dimension. For example, a ragged balanced dimension lets you hide a Region if a country has only one region. It also lets you display employees at the appropriate level within the organizational structure by skipping levels between the supervisor and the subordinate. Office ignores the ragged setting for balanced dimensions, and its ragged unbalanced dimensions are completely unusable. Cognos PowerPlay 7.0 handles ragged dimensions better than Cognos PowerPlay 6.0 does, but it shifts up lower-level members of a ragged balanced dimension, which might not be appropriate.

Several products display member properties, but the OWC PivotTable is remarkably powerful in this area. When you design a report, you can choose whether to include the member in printable form on the report or to have the member property appear temporarily when the mouse hovers over a member. NovaView automatically displays all member properties in a screen tip, but you can’t easily make them printable.

Some products let you filter for the top-ranked members, but the OWC PivotTable control is the only tool that can easily show the top three products for each of the top three categories while skipping an intermediate subcategory level.

PowerPlay is the only product that permits complete free-form arrangement of members on axes. This capability lets a report developer create sophisticated asymmetrical reports (e.g., nesting members of a second dimension for only one member of a first dimension). Although this flexibility allows for highly sophisticated reports, it also lets you create absurd reports. The Excel PivotTable report lets you reference cells from the PivotTable by using the GetPivotData function. This capability lets you create reports even more flexible than those you can create by using PowerPlay—if you’re willing to do a little extra work. Free-form arrangement is particularly useful for financial reporting applications for which you need to be able to control member placement on a report (i.e., you need to break the dimension hierarchy).

Manipulate Slicers. If you want to slice by multiple members without having to create an explicit calculated member, NovaView and the Office tools provide simple, natural UIs. Both ProClarity and NovaView let you search for a slicer by name (a feature that might be useful when you’re slicing by members of a large, flat dimension).

Calculations (for a user who has no knowledge of MDX). All the products let you create new calculated measures, but with ProClarity and NovaView, you can easily create a new member of a nonmeasures dimension. NovaView has the edge because it lets you create a new member as a child of any other member in the dimension. The mechanisms for creating calculated measures vary greatly among the products.

ProClarity doesn’t let you turn Visual Totals on or off without using custom programming. Visual Totals recalculates parent values when you hide some of the children. ProClarity does have a subtotals option, which provides some of the benefit of Visual Totals, but the subtotals simply total all visible values, without regard to level in the hierarchy, calculated members, or native aggregation functions. NovaView handles Visual Totals in a particularly flexible way because you can choose how you want to deal with items hidden deep within the hierarchy.

Actions and Drillthrough. Both ProClarity and NovaView support server-defined actions, but NovaView supports even the less-common actions that return a data set or a cell set.

Crystal Analysis supports drillthrough in an elegant manner. Rather than return an arbitrary number of rows or force the user to preconfigure the number of rows to be returned, Crystal Analysis displays the first hundred rows, then retrieves additional data in one-hundred-row increments as the user scrolls down. PowerPlay doesn’t directly support Analysis Services drillthrough because it has its own drillthrough solution that uses Impromptu reports. PowerPlay requires additional setup costs, but provides more flexible drillthrough capabilities than using Analysis Services drillthrough.

Write-back. Both ProClarity and NovaView write data values back to the cube, but NovaView’s write-back capabilities are strongest because it supports what if write-back—even if the cube isn’t write-enabled. Also, NovaView supports write-back even in its thin-client server edition. Although PowerPlay and the Office PivotTables don’t support write-back capabilities in the standard UI, you can customize these applications to allow write-back capabilities.

Presentation

The Presentation category includes all the actions the user takes to control the appearance of a report. All the client tools display a simple grid, but they ought to be able to perfom additional operations such as adding headers and footers at the report level. Here’s an overview of the different ways these products let the user display reports.

Cell Formatting. Each product is significantly limited in its ability to format cells. Neither the Excel PivotTable nor the OWC PivotTable recognizes server-side number formatting. Excel PivotTable lets you use its own formatting for cells, including conditional formatting, but the formatting is often lost as you pivot the report. ProClarity lets you choose between custom formatting and server-side formatting, but custom formatting applies to all the measures, not just one. Neither Crystal Analysis nor PowerPlay lets you display the result of a calculation that returns text. PowerPlay and NovaView, the top products for this measure, let you use server-side formatting, but then override this formatting on a cell-by-cell basis.

Report Format and Layout. OLAP reporting tools use two basic approaches for arranging members on an axis from different levels. Tabular layout puts members from each level in a separate column and places level names at the top of each column; outline layout puts all members in the same column and displays no level names. Outline layout requires much less screen (or page) space when a report displays multiple levels or dimensions, but tabular layout can make cube navigation easier than outline layout. In general, the Office PivotTables support only tabular layout, and the other products support only outline layout. (In the PowerPlay Explorer mode, the Standard Cross-tab layout produces a tabular layout.) NovaView is the only product that lets you switch conveniently between column mode and indented mode.

For several reasons, the Excel PivotTable is a powerful tool for creating specialized reports. You can easily add headers and footers, you can use AutoFormats to create effective reports directly in the PivotTable, and you can use the GetPivotData function to create any layout you want. Conversely, the OWC PivotTable, like the Web version of most other products, isn’t effective for report layout because it doesn’t support page headers and footers. PowerPlay has an excellent ability to format a report for printing as well as for other types of distribution.

Charting. All products include effective charting tools. In addition to providing a wide range of chart types and formatting options, the Web Components Chart tool for Office XP lets you create groups of charts that share one set of slicers and one legend. PowerPlay line charts are better than most because the lines form a gap where empty cells occur in the data. In this scenario, most other charting tools let the line drop to zero. NovaView charts make it easy for the user to chose which items appear on the chart and suggest appropriate defaults

Microsoft and Cognos offer auxiliary products for graphical visualization. For Office, the product is Data Analyzer, and for PowerPlay, the product is Visualizer. ProClarity and NovaView, however, include visualization tools in the product. ProClarity includes proprietary decomposition and perspective views, and NovaView includes a cross-view feature. Both the OWC PivotTable and NovaView have add-ins available for displaying geographic data by using MapPoint.

Environment

The Environment category looks at how the tool integrates with the user’s environment. For example, can the user copy reports into Excel or graphics into PowerPoint? We extrapolated seven kinds of integration that various tools accomplished effectively.

Framework. Each product has different strengths and weaknesses in this area. The OWC PivotTable requires you to build a framework by using FrontPage or another designer tool. Crystal Analysis lets each briefing book reference only one cube and lets you open only one briefing book at a time. However, its excellent Analysis Buttons feature guides user navigation. PowerPlay easily connects to multiple cubes but doesn’t provide special navigation tools. Both ProClarity and NovaView share similar strong briefing-book approaches that let each page in a book connect to a different cube.

Offline Reports. None of the products is very good at letting a user review a report (let alone interact with one) while the report is disconnected from the server. ProClarity and the Excel PivotTable let you extract a local cube from the server cube. ProClarity lets you publish a book as static HTML, and PowerPlay can create and publish static reports in a variety of forms, including PDF files. NovaView and Excel let you save a report in such a way that a disconnected user can see the report in its saved form.

Guided User Experience. Many users want to be able to progress from one report to another in a guided manner, following a particular product or region through a series of reports. Crystal’s Analysis Buttons provide a convenient tool for guiding the user’s experience.

Distribution. PowerPlay has the most options for managing and distributing reports. NovaView gives you the option of letting Web users connect as named users (when you know the specific user and can deal with security issues) or as unnamed users (when you treat all users as anonymous).

Controlled User Experience. To prevent users from creating potentially meaningless combinations of choices in a report, all products except Excel can in some way limit the actions users take. PowerPlay, ProClarity, and Crystal let the report author determine which slicers a report user can change while locking others. ProClarity’s approach to slicers is frustrating because you must select the members for the slicer list in the dimension tree. After you flag selected members as slicers, the members appear in a flat list with no hierarchy.

Integration with Office Tools. The OWC PivotTable can transfer directly to a live Excel PivotTable, but PowerPlay can transfer to a PowerPlay for Excel grid. PowerPlay has the best option for pasting a static image of a report into a PowerPoint presentation. ProClarity can paste a live object into PowerPoint, but only if you’re willing to change the macro security level to low, which might not be acceptable in your organization.

Support and Training. The Office products’ documentation and sample files are the least extensive, but NovaView is also limited. ProClarity sample files aren’t as extensive as Crystal Analysis files. PowerPlay has an extensive support infrastructure (probably because PowerPlay has been around the longest of all these products).

Microsoft has the largest product-support services organization of all the vendors, but wait times are long, and the support staff doesn’t specialize in the PivotTable offerings. Cognos has a strong support organization, but getting representatives to return phone calls can be challenging. NovaView, based in Israel, has an enthusiastic support organization, but reaching it has sometimes been difficult because of the time-zone differences. However, NovaView has recently added 9-to-5 support (Eastern time), so the situation might improve as the company develops its North American support and sales organization.

Customization

The ability to programmatically customize a front-end application can be crucial if you require tight integration into an overall framework. Often, you can work around product limitations if you can customize the solution. Customizability adds to an application’s flexibility, but it also incurs additional costs to develop and maintain the customization. For our purposes, customization includes anything requiring specialized skills that are more appropriate for an IT professional than for a business analyst (e.g., the ability to use custom MDX and to program in Visual Basic (VB) or another programming language). The following two customization features require distinctive skills.

MDX Exposed. NovaView includes the powerful Direct MDX mode, which lets you enter custom MDX statements—either for the entire report or for a single axis—and permits you to include parameters that let the statement respond to selected slicer changes. Both Crystal Analysis and ProClarity have options for viewing and modifying the MDX statement, but they inconveniently convert set definitions in the custom MDX statement to constants. The OWC PivotTable requires programming to change the MDX statement, but then makes full use of the custom statement in further queries. The OWC PivotTable lets you modify the MDX statement but doesn’t let you see the statement that the UI generates. PowerPlay has no support for custom MDX.

Programmable. Although a full COM object model suitable for use from VB applications wasn’t available in the Crystal Analysis 8.0 release we reviewed, Crystal says that an upcoming release will include this feature. PowerPlay has a good development environment. ProClarity actively markets ProClarity as a development platform for building custom applications and includes a well-documented object model as part of the basic product. NovaView has a rich object library, complete with a feature for creating add-ins to supplement the UI.