Comparative Lab Report: Client Tools for Analysis Services

In 1996, Microsoft entered the online analysis world by acquiring technology from the Israeli company Panorama Software. Under the name OLAP Services, this technology became part of SQL Server 7.0 in 1999. In SQL Server 2000, the product was expanded and renamed Analysis Services. The latest analysis product is a powerful and flexible online analytical processing (OLAP) provider, but it doesn't include a client tool for querying, reporting, or analyzing data. Customers who want to use Analysis Services without writing a custom client browser must therefore find a suitable commercially available client application. To help readers select a client application that meets their specific needs, we compared five such products. After at least two consultants evaluated each tool, we reconciled and summarized the findings. We made certain that at least one of the reviewers of each tool had worked with that tool in a client setting.

To find a current list of client tools, we looked at Microsoft's Data Warehousing Alliance Web site (http://www.microsoft.com/sql/partners/DWA), which contains a cross-referenced list of approximately 20 vendors that provide front-end tools. Rather than perform a trivial analysis of all 20 products, we chose to focus on five products we've used and evaluated at the request of multiple clients: Cognos PowerPlay, Crystal Analysis, Microsoft Office XP PivotTables, Panorama NovaView, and ProClarity Analytics Platform. In this article, we share our methodology and vendor evaluation files so that you can make an apples-to-apples comparison with other vendors' tools.

After you've read this review, one or two of the five tools will likely be on your short list. However, keep in mind that your best choice might not be any of them. The purpose of the article is not to declare one winner, but rather to help you understand the strengths and weaknesses of these products specifically and of client tools generally. You should acquire a good sense of which features are common to most products and which features are rare.

As our team analyzed the tools, we followed a two-stage process. First, we created a standardized Analysis Services database and developed a standardized template of tasks, then asked each evaluator to use the targeted tool to try out the complete list of tasks. In this stage, our goal was to be objective and consistent. Second, we asked each evaluator to prepare an overall assessment of the tool's usefulness for users in various roles, along with impressions of that tool's strengths and weaknesses. Our goal in the second stage was to be subjective and pragmatic. The subjective evaluation includes not only the results of the objective analysis, but also experience from working with the tool in a real-world setting. In this article, we present the high-level, pragmatic, somewhat subjective analysis on a product-by-product basis. Then, in the Web-exclusive sidebar "Getting Down to the Nitty-Gritty," at http://www.sqlmag.com, InstantDoc ID 26486, we recap the results of the detailed analysis, highlighting significant product strengths and weaknesses on a feature-by-feature basis. In all, we evaluated more than 100 features, which we divided into 18 subcategories, then grouped into four categories. The sidebar's Table A shows our ratings for these categories and subcategories.

Inevitably, we missed unique features of each product. Some product features are subtle, and you stumble onto them only after months of intense work. Some features are so specialized that our apples-to-apples comparison missed them. And we didn't cover some larger topics, such as scalability, seamless support for multiple OLAP server architectures, and tight integration between relational and OLAP reporting because they're beyond the scope of this article.

Vendor Overviews

The high-level overviews of each product follow alphabetical order. For each product, we summarize the benefits that the product offers to users in different roles, then highlight what might be make-or-break attributes. User benefits are important because features aren't the same as benefits. A feature that might provide considerable benefit to one user might provide no benefit at all—or might even be a detriment—to a different user. To effectively evaluate a tool, you must think in terms of the ways people will use it. We identified three archetypal roles a user might play and asked evaluators to assess how well the product meets the needs of users in each role. (The same user might perform different roles in different contexts.) The three roles correspond to the three general functions of a client tool:

To perform free-form ad hoc analysis, power analysts require the full analytical power of Analysis Services. They're willing to learn the details of the source database design and of the query tool to obtain the necessary results, and they often create reports that others use.

Data gatherers have to be able to dynamically query a database without becoming experts in the source database or the query tool. They want a guided user experience that permits drill-down and pivoting, yet eliminates options that might create undesirable results (e.g., restricting the view to fiscal year 2001 while simultaneously displaying the months from calendar year 2002).

Report users require standard reports that might be brief or extended and which often include charts as well as tables. These users want to scan consistently structured reports without needing to drill or slice to find the desired values. Producing this kind of report typically involves creating static reports, either in printed form or as static HTML pages or other documents. Usually, headers and footers (which include such information as the date the report was generated) and titles that repeat on different pages are crucial.

As you identify which roles are most important in your organization or for your application, you can focus on the appropriate features of each product. During each vendor evaluation, we describe how well that vendor meets the needs of users in each role.

All the products reviewed support the basic OLAP operations of slice (filtering by a single member from a dimension), dice (displaying multiple members in a grid), and pivot (switch a dimension from one axis to another). The review focuses on how easy these operations are to perform and their flexibility. Most products come in both a full-client desktop version and a thin-client Web version. For products that come with a Web version, we highlight subtle distinctions between whether the client is truly zero footprint (the client doesn't download controls and leaves nothing behind) or whether it requires Java applets or even a full download of an ActiveX control.

Some products include make-or-break features. Identifying these make-or-break features is important because it can often help you simplify the decision-making process. Typically, a make-or-break feature either elevates the product above the others or removes the product from consideration, permitting you to bypass a more detailed analysis. But remember that features might change or another mechanism might meet the same need.

Cognos PowerPlay 7.0

PowerPlay comes in two basic versions. PowerPlay for Windows is a client/server application, and PowerPlay Web is a zero-footprint pure HTML solution. (An additional version, PowerPlay for Excel, duplicates the functionality of the client/server PowerPlay for Windows version, but within the context of an Excel spreadsheet.) None of the versions interact directly with Analysis Services; instead, they use an intermediate driver that can retrieve data from any of several different database engines, including Analysis Services. To deploy PowerPlay Web reports, you must install PowerPlay Enterprise Server.

PowerPlay for Windows has two modes: Explorer, which limits what the user can do, and Reporter, which is more free-form. Explorer mode always displays groups of members on a single level—all the members of a level or all the children of a member—whereas Reporter mode lets you place members anywhere. Certain features—notably, conditional formatting and nested charts—are available only in the structured environment of Explorer mode.

An important advantage of PowerPlay is that it is only one piece of a much larger picture. Cognos provides a wide range of BI tools, including Impromptu, a relational reporting tool; Upfront, a Web portal and reporting manager; and Visualizer, a graphing tool. These tools share a common security model and a common configuration manager. Figure 1 shows a sample sales report and graph that PowerPlay generates.

Suitability for Specific Roles

For the power analyst, PowerPlay is good. Reporter mode provides a tremendous amount of flexibility. PowerPlay allows more sophisticated analyses than any other client tool, but it also lets users create meaningless or confusing reports. If the analyst uses PowerPlay frequently enough to be comfortable with the UI, the tool can be effective.

For the data gatherer, PowerPlay is good. With PowerPlay, the report author can freeze selected slicers, but otherwise can't significantly guide the information consumer through a report. One interesting feature is PowerPlay Web's interaction with an Adobe Acrobat PDF file, in which a link from a PDF file opens a live view of a report in PowerPlay Web.

For the report user, PowerPlay is excellent. The tool is an extremely good report generator, complete with scheduling tools. As long as you set up the reports by using a special subset command to populate the axes, reports will automatically include new members as the source data changes.

Strengths and Weaknesses

PowerPlay's greatest strengths lie in two areas: enterprise connectivity and production reporting. PowerPlay is part of a suite of products that can serve data not only from multiple OLAP servers such as Analysis Services, Hyperion Essbase, and Cognos PowerCubes, but also directly from relational sources. Although most organizations select one OLAP server technology, you might need to integrate multiple OLAP products, and almost everyone wants the ability to seamlessly integrate OLAP and relational reporting. PowerPlay is the only product we reviewed that can directly produce publication-quality reports. You can output them as HTML files or PDF files, and you can download them directly to a printer. You can even schedule production of the reports.

PowerPlay's greatest weaknesses—limited Analysis Services support and complexity—come from its strengths. Because PowerPlay can communicate equally well with several OLAP server technologies, it doesn't expose the full range of Analysis Services functionality.

PowerPlay can be challenging to set up properly, but Cognos representatives assure us that numerous users have set up PowerPlay without assistance. This problem has increased with the advent of PowerPlay 7.0 because now you can't use any form of PowerPlay—even PowerPlay for Windows on a standalone machine—without first installing a directory service to handle security. You have to use either Netscape Directory, which is included as a free supplement to PowerPlay, or Windows Active Directory (AD). In addition, before you can access any Analysis Services cube from PowerPlay, you must run the Connection Manager to create a special file (an .mdc file) that contains connection information.

In addition, PowerPlay's dual-mode interface (Explorer and Reporter) can be confusing. In general, PowerPlay's UI is more confusing than most, although the Web interface for PowerPlay 7.0 is much simpler and clearer than in previous versions. The UI for PowerPlay for Windows fixed several irritating problems from PowerPlay 6.0 but isn't significantly changed. As a final minor point, although PowerPlay allows customization, it uses the Cognos Scripting Language, which is essentially Visual Basic (VB) plus Cognos's own development environment.

If the suite of Cognos products is appealing to you or if you want extensive report management and distribution flexibility, PowerPlay is a great tool. If you're looking primarily for an analytical client for Analysis Services, you can probably find a better—and simpler—tool.

Crystal Analysis 8.0

As is the case with most of the products we reviewed, Crystal Analysis is available in full-client and enterprise-server (thin-client) versions. The client/server configuration lets a power user build and deploy analytical applications and briefing books, and the thin-client solution, which is included in the Crystal Enterprise Edition, provides zero-footprint reporting capabilities.

Crystal Analysis 8.0 was the company's first-generation OLAP reporting tool. In January 2002, Crystal Decisions distributed a major maintenance release (MR1) for Crystal Analysis 8.0, and in July 2002, the company released Crystal Analysis Professional 8.5. Because we couldn't obtain Crystal Analysis 8.5 in time to review it, for this comparison, we used Crystal Analysis 8.0 MR1. According to the company, Crystal Analysis 8.5's new features include a rich Web-based client and an Excel add-in. Figure 2, page 36, shows a sample sales report in Crystal Analysis 8.0.

Suitability for Specific Roles

For the power analyst, Crystal Analysis is fair. The product has good dimension navigation but feels more like a report-development tool than an ad hoc analytical tool. The application contains many wizards for creating analytical reports, but the analytical tools themselves can be frustrating. For example, you can't rank members within a hierarchy.

For the data gatherer, Crystal Analysis is good. The report designer can customize the report in many ways. In particular, the designer can create transition elements that effectively control what happens when a user performs an action. However, you can't navigate from one cube to another within one briefing book. Slicing is easy, and the report designer can prevent report users from changing the slice for specific dimensions. The Web deployment—which effectively matches the client/server functionality—is easy to manage.

For the report user, Crystal Analysis is fair to good. On the one hand, the tool lacks an ability to create structured or static reports. Crystal Analysis's lack of standardized reporting support for an OLAP database seems particularly annoying when you compare that deficiency with the rich reporting capabilities that sister application Crystal Reports brings to the relational world. On the other hand, Crystal Analysis's strong Web-deployment capability, coupled with the ability to restrict interaction, could make this a good tool for supporting data gatherers.

Strengths and Weaknesses

On the positive side, Crystal Analysis is the only tool for which a nonprogrammer can create an application that lets a user proceed smoothly from one report to another or modify the current view while retaining the current slicer.

On the negative side, an application can't navigate smoothly to a report from a different cube because in Crystal Analysis, an entire briefing book is linked to one cube. You can create multiple views (pages) based on that cube, and you can create transitions from one view to another, but you can't navigate to a different cube or to a different book. This restriction prevents you from conducting analyses that compare values from two or more cubes. (You ultimately have to launch separate instances of Crystal Analysis.)

In general, Crystal Analysis 8.0 felt like a first release. The product worked very well for basic analysis, but the limitations were frustrating. Crystal Analysis 8.5, however, might already have added some of the significant functionality that Crystal Analysis needs to remain competitive in this maturing market.

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.

Panorama NovaView 3.0

NovaView, part of the NovaView e-BI Suite, uses a briefing-book product to store different views or reports. You can use the desktop version of NovaView to create views and briefing books, then publish those books to the Panorama XML repository, which you can access from either the desktop NovaView client or the NovaView Web interface. The repository information is stored in an open XML file format.

NovaView comes in both full desktop client and thin-client Web versions. In the Web version, you can choose whether you want to use small Java applets—which improve the user experience—or pure Dynamic HTML (DHTML). When you deploy the Web version, you choose one of two modes: intranet mode, in which each user is named and managed, and Internet mode, in which all users are treated as anonymous. Figure 4, page 38, displays NovaView's charting capability.

Suitability for Specific Roles

For the power analyst, NovaView is excellent. NovaView fully supports almost all Analysis Services features. You can create a wide variety of calculated members by using either NovaView proprietary functions or by building multidimensional expressions (MDX) directly.

For the data gatherer, NovaView is good. Although the application doesn't include tools for directly guiding and limiting the user's experience, both parameters (slider controls that adjust query values) and bubble-up exceptions (cell colors that vary based on values more detailed than those displayed) are valuable tools for guiding the data gatherer.

For the report user, NovaView is good. The reporting is focused on interactive use instead of production reports, but you can at least create headers and footers when printing.

Strengths and Weaknesses

NovaView fully supports nearly all Analysis Services features, both in client/server and in zero-footprint Web options. Some users found NovaView's UI—specifically, the way that you select members—to be unusual, but most users were able to figure it out without assistance.

NovaView is the only product we reviewed that directly supports writing data back to the server through the thin-client UI, facilitating the creation of a budgeting or planning application. (Note that ProClarity supports data write-back in the full client/server tool, and Cognos PowerPlay and the Office PivotTable tools have documented techniques available for adding write-back capability through custom coding.)

NovaView directly supports bubble-up exception highlighting. Most products let you flag exceptions based on the value in the current cell, but an exception that bubbles up from a hidden detail level can reveal underlying problems even in a high-level report. Although in Analysis Manager you can write sophisticated MDX expressions that make the bubble-up exceptions available to any tool that displays server-side formatting, only NovaView has wizards in the client tool that build this type of MDX expression for you.

Another innovative feature in the NovaView interface is the use of parameters—slider controls that can adjust almost any numeric value, such as the number of members you want in a top-count or bottom-count query. NovaView's examples and documentation are less fully developed than most, but the tool includes excellent supporting files and samples.

The biggest concern with NovaView is that the interface is still unpolished. For example, you often see an alert box containing an internal error message such as

 (Method '~' of object '~' failed) in pnFunction Module 0 in DoWebFunction

These messages are more appropriate to the beta version of a product than to a finished version intended for widespread deployment. On a similar note, in the version we tested, when we changed the way members display on an axis, the product immediately switched to manual mode, with the result that no numbers appeared in the grid. This is clearly a bug, and Panorama will surely fix it quickly because the company is very responsive to bug reports. However, our testers and consultants encountered an uncomfortable number of bugs, ranging from inconvenient to serious.

The published licensing scheme for NovaView is rather complicated, with different packages based on intranet named users and Internet concurrent users. Also, because NovaView is headquartered abroad (in Israel), some North and South American companies might have been concerned in the past about working with a supplier that operates in a time zone far removed from that of their development teams. However, now that Panorama is establishing a North American organization, this concern should diminish.

ProClarity Analytics Platform Release 5.0

ProClarity client products are available in three versions. ProClarity Professional—the version we used for most of our tests—is a client/server application that includes full ad hoc capability, including an optional authoring tool. ProClarity Analytics Server (formerly ProClarity Enterprise Server) is available in both Enterprise and Standard editions, each of which supports either a rich client (the same functionality as ProClarity Professional, but in the context of a Web browser) or a zero-footprint browser-based client. ProClarity products are designed to be open and extensible, letting developers use VBA to develop applications based on the ProClarity platform. ProClarity Enterprise Server has additional options available, including Business Reporter, which lets you create free-form reports from within an Excel worksheet. Figure 5, page 40, shows a graph in ProClarity Professional.

Suitability for Specific Roles

For the power analyst, ProClarity is excellent. In addition to advanced visualization tools, a broad range of options for displaying values on the axes, access to advanced Analysis Services features, and the open availability of MDX make this a useful tool for a power analyst.

For the data gatherer, ProClarity is excellent. Controlling the user experience does require custom VBA code, but by using the briefing-book storage mechanism, you can easily provide for the user a set of reports that are easy to use and modify. The new thin-client interface in version 5.0 is professional looking; even with a pure HTML, zero-footprint interface, the controls are easy to use, and responsiveness is excellent.

For the report user, ProClarity is fair. Layout and formatting options for printed reports are limited (the lack of an option for changing the title on the grid is particularly frustrating). However, the product can create static versions of the reports, and you can print a set of reports that cycle through all the values of a slicer field

Strengths and Weaknesses

ProClarity's biggest strength is that it supports nearly all Analysis Services features in both its client/server and zero-footprint options. In addition, ProClarity includes proprietary visualization tools such as the decomposition view, and users can program or embed ProClarity into custom applications. New in version 5.0 are customizable headers and footers for printing reports. ProClarity has an option to automatically cycle through the set of members in a slicer as you print; however, when you print a report, levels within a report that were indented on the screen switch to full columns, taking up extra space on the page.

As a weakness, most of our testers found ProClarity's UI frustrating and cumbersome. ProClarity 5.0 has improved the UI somewhat, particularly in the Web version, but you often need to switch between multiple panes to create a report, and showing members from multiple levels of a dimension together on a report is difficult.

ProClarity Analytics Server Enterprise Edition includes the new Excel-based Business Reporter tool. By using the Business Reporter, you can create a staging query, then use special functions within Excel to create free-form, flexible reports. This tool appears to be a powerful mechanism for creating infinitely customizable reports, but the functions use explicit row and column numbers to retrieve values from the hidden staging query. As long as you use the functions in blocks—for example, to display all the children of a member—modifying the staging query automatically adjusts the functions in the spreadsheet. But if you use the functions to create a free-form report, changing the data source can easily cause the formulas to retrieve incorrect values—with no warning or errors. This failure is unacceptable, particularly because the Excel PivotTable in Office XP offers the same flexibility in a function that doesn't inadvertently return an incorrect value.

Different Strokes for Different Folks

None of these five products provides a solution that clearly meets the needs of all users. Each product has its own strengths and weaknesses. For example, Crystal Analysis Professional 8.0 is a first-generation tool that lacks analytical depth but includes an innovative approach for guiding the experience for data gatherers. The Office PivotTable tools are easy to work with, include great charts, and are customizable, but they don't include a thin-client Web-deployment option. NovaView is focused on Analysis Services and provides perhaps the richest set of raw functionality. However, this product still has a lot of minor bugs and doesn't yet contain the same depth of customer-support options in North America as the other tools do. PowerPlay has the best option for creating publishable reports from cubes and can access virtually any type of data server, but it has limited support for features specific to Analysis Services. ProClarity, like NovaView, focuses on ad hoc reporting, not production reports, but it supports most Analysis Services features such as data write-back, and it is highly customizable. All the products have significant limitations. For example, none of the products lets a user create a simple graphically driven executive information system (EIS) report without programming.

We hope this high-level overview helps you choose a client tool for Analysis Services. If you need more data to make your decision, see the highlights of our detailed analysis in the Web sidebar, "Getting Down to the Nitty-Gritty." To set up your own test, you can download the .xls test matrix file and the .cab and .mdb sample database files available online at InstantDoc ID 26399.