BI Scorecard: Evaluating the Suites One Functional Area at a TimeBI Scorecard: Evaluating the Suites One Functional Area at a Time

Editor's Note: Welcome to Intelligent Enterprise's special product review series by independent expert Cindi Howson. This review series takes a close look at features and functions critical in your business intelligence (BI) product evaluation. In the course of this series, Howson will offer a comparative evaluation of a selection of major BI products.

Of course, there are more BI products important to evaluate than are addressed in this product review series. In recent issues, Intelligent Enterprise has published reviews and analyses of a number of them, as well as articles and product guides at our Web Community, IntelligentBPM.com. For access to these, please consult this listing of relevant articles from 2003 and 2004. As you consider which products fit best with your organization's BI strategy, be sure to take advantage of the full breadth of our BI coverage — to which Howson's series now becomes a major addition.

In upcoming issues and on the Web, look for reviews of some of the products not addressed specifically in the product comparisons in this series. Intelligent Enterprise endeavors to bring readers information about as wide a spectrum of credible technology alternatives as we can. We look forward to hearing from you about other products, or perhaps other features and functions, which you feel we should cover to give the fullest possible dimension to BI product selection.

David Stodder
Editor-in-Chief

Business intelligence (BI) attracted a lot attention in 2003. Vendor acquisition and product innovation invigorated a maturing market. Although sexy scorecards have always whetted the appetite of data-rich but information-poor managers, it was tried-and-true reporting that fueled the most heated discussions in 2003.

Cognos, with its world product launch of ReportNet, broadcast the event live from New York. Business Objects released Enterprise 6 and later acquired Crystal; Hyperion acquired Brio. MicroStrategy released 7.5, with long-awaited production reporting. Add heavyweight Microsoft to the mix with its just-released Reporting Services, and it certainly leaves companies wondering if they've invested in the right BI tools.

Despite all this innovation and excitement, the road to BI product selection and standardization is still a treacherous one, with few people understanding what features are different among the products and how those differences affect usability, manageability, cost, and ultimately, success. When people buy a car, they understand features such as "miles per gallon" well. (If I buy that cool Hummer, I understand the effects on pollution, gas costs, and number of times standing at the pump in the snow.) But, when selecting and standardizing on a BI tool, features such as "banded reports" or "multipass SQL" mean different things to different people, depending on whether they're users, BI experts, or vendors.

This seven-part "BI Scorecard" series is designed to help you understand the features and product differences from seven major functional areas that will most affect the success of your deployment.

None of the scorecards rank the criteria, as the importance of each feature will vary depending on your company's own capabilities, goals, and infrastructure. (See Table 1.) Instead, the scorecards provide indicators for areas in which the products differ. If you are doing an initial prototype, be sure to dig deeper into the weaker areas (color coded red) to avoid hitting limitations. If you are standardizing and deploying, leverage the product's strengths (color coded green) for a more successful implementation.

Table 1 Scorecard comparing query capabilities of several BI suites.

Focus on Query Features

This first part of the series looks at query features: how you get the data out of the data warehouse or operational system. An organization must first answer a few strategic questions before determining which criteria are more important:

Business View of the Data

Many of the leading BI tools include a business view, or metadata layer, that shields users from the complexities of the underlying database schema. Unbeknownst to the user, the business view may connect to various data sources, perform table joins, aggregate data automatically, define complex filters and calculations, allowing users to select business measures and dimensions rather than cryptic field names. For IT query authors, the business view provides robust, reusable components, simplifying and reducing the cost of report maintenance. When one calculation or data element changes, the business view administrator updates it centrally, rather than in hundreds of individual reports.

The names of the business views differ for each product, but from an end-user perspective, the functional differences are minimal. In most cases, query authors must use the business view to generate SQL, but in some cases, IT developers can edit or write their own SQL. From an administrative perspective, however, the business views are quite varied: Their capabilities, the SQL they generate, and the processes used to build them are wide ranging.

Business Objects refers to its business view as a "universe." Cognos ReportNet calls it a "package." Informatica's Power Analyzer uses the concept of a Schema. MicroStrategy uses a Project. In the past, Crystal didn't provide a metadata layer, but recently introduced the capability in version 10 (released January 2004) — although developers can still code their own SQL. One of Business Object's first integration plans with Crystal is to allow Crystal developers access to the Business Objects universes (H1 2004). Microsoft's new Reporting Services does not use a business view, thus positioning itself as an authoring tool primarily for IT developers rather than business power users.

Web-based Querying

When BI tools first began deploying over the Internet in 1996, many vendors took the approach of using the Web to deliver standard reports, but few provided Web-based applications that let users develop queries from within a browser. Initial endeavors to do so yielded fairly basic list reports, none of the highly formatted documents that users have grown to expect on the desktop. The year 2003 brought significant improvements in Web-based querying, yet the approaches are very different.

Business Objects, for example, offers two interfaces for Web-based querying: a Java applet and Dynamic HTML. Some organizations have a no-applet policy for security reasons, and companies without a standard browser will struggle to deploy applets. However, the functionality in the applet version is superior to that of the DHTML version.

Cognos launched ReportNet in 2003, touting a zero footprint that uses JavaScript and DHTML. JavaScript overcomes most security concerns with applets, yet it introduces similar browser dependence and an initial launch lag over pure HTML. The query capabilities in ReportNet are superior to those of Cognos Query, although Cognos will continue to maintain this product for existing customers. Within ReportNet, there are two different editors: Query Studio for business users and Report Studio for IT developers. Even with the zero footprint approach, Report Studio offers more Web-based query functionality than any of the other tools I've reviewed.

Crystal Ad Hoc is a Web-based query tool that lets users query a previously built Crystal report. The functionality is quite basic, with few of the formatting capabilities offered in the desktop Crystal Reports. Therefore, Crystal Reports is still the primary authoring tool for Web-delivered reports. With richer Web-based abilities in Business Objects' WebIntelligence, the future of Crystal Ad Hoc is uncertain.

Informatica, a late-comer to BI, does not have any desktop legacy or parity issues that have challenged long-time BI companies. Its PowerAnalyzer product uses pure HTML/DHTML. MicroStrategy has also taken a combination HTML/DHTML approach.

In Microsoft's first release, Reporting Services uses an editor within Visual Studio for query and report design and thus does not provide Web-based querying. Users can still refresh and interact with reports via a browser but they cannot build new ones from within a browser. However, Reporting Services uses an open, published document format (Report Definition Language, or RDL): In this way, it enables third-party vendors to provide Web-based query.

Prompting

Prompting is one of those features known by many names, such as parameters, picklists, cascading prompts, or dynamic filters. Prompting is one of the most important features for a flexible reporting environment and providing users with guided analysis. You may use prompts to ask users what data they want to see in a report or how they want to filter it. I'll refer to the first as a "column" prompt and the second type as "filter" or "row" prompt.

Very few vendors support column prompts out of the box; most will require you to turn to custom development. Yet column prompts allow one report template to produce hundreds of variations. Users get the data they want and IT gets low deployment costs.

Informatica PowerAnalyzer makes this the easiest to implement by providing a checkbox in the wizard-like query designer. When query authors check "Prompt Values," anyone who subsequently executes the report is prompted to select the possible measures (metrics) and dimensions (attributes). (See Figure 1.)

Figure 1 Informatica's PowerAnalyzer prompts users to select either the metrics or attributes to appear in a report, in addition to using prompts to filter the data.

One of the challenges with prompts is that if you ask a user too many annoying questions (when they simply want the same data they always access!), the report will be perceived as unfriendly. In this respect, the BI tool must provide users with the option of saving previous prompt values or bypassing the prompts. Take the scenario of one sales report that both a regional manager and a product manager may run. The regional manager will want to answer the prompt for region but not for product; the product manager will want all regions (so ignore the region prompt) but will want to select specific products. Tools that do not support "optional" prompts will force the author to develop two queries, thus increasing deployment costs and potentially confusing users.

Another important criterion related to prompts is the ability to customize the displayed picklists. For example, when the product manager accesses a product filter prompt, the manager most often will want to see a meaningful list of product names or descriptions rather than nonsensical product I.D. numbers. For performance reasons, the database may want to process the query via the I.D. field.

Cascading prompts are a further customization that progressively prompt users based on their last selection. So when the regional manager selects to see "Eastern" sales, the BI tool then displays a picklist of states in the Eastern region. MicroStrategy offers a unique feature called hierarchical prompts (see Figure 2) that allows users to select filter values from any level within a hierarchy.

Figure 2 MicroStrategy's hierarchical prompts let a user choose to answer a prompt from any level, so a country manager can select "USA" or a regional manager can select "Northeast."

The last major consideration is the intuitiveness of the prompts. Radio buttons and checkboxes are much easier to use than Ctrl-click to select multiple values. A search button is essential for navigating long picklists.

Multiple Data Sources

There are many reasons users want to query multiple data sources simultaneously. Some may want to display two data sets as two objects (tables/charts) in one report: for example, a table of customer revenue next to a graph of customer satisfaction. In other cases, the data may exist in two different places but users want to merge the two data sets and analyze them in one table. In theory, all data has been cleansed and stored in a data warehouse. In reality, different versions may exist in multiple databases, including personal spreadsheets or departmental databases. So revenue may come from the data warehouse, but customer groupings and industry segmentation may reside in an Microsoft Access database.

While the Business Objects universe allows access to only one database, the individual document lets users access multiple databases, stored procedures, and personal spreadsheets simultaneously. This gives a query author significant flexibility in report development, yet is one of the features that does not exist in WebIntelligence.

Cognos ReportNet's package provides for multiple data sources through ODBC. However, one report can query only one package, thus giving the administrators control but limiting user flexibility. Query Studio, likewise, can present the results as only one table or chart, but Report Studio is more flexible.

Crystal can access multiple data sources in one query and as one result set only, essentially performing a cross-database join.

Informatica's PowerAnalyzer allows one report to access multiple data sources after the administrator has defined those data sources; results are displayed as one table.

With MicroStrategy 7.5's new Document Editor (desktop), you can include multiple queries from the same project, yet the project can access data in only one relational database. Because this access is in the formatted document only, OLAP analysis or drill-down is not available for this type of document.

Microsoft's Reporting Services allows one document to access multiple data sources either as two result sets or one. When users want to display one result set, Reporting Services generates a distributed query similar to Crystal Reports.

Each of these approaches offers varying degrees of flexibility and differences in whether a central administrator versus an individual query designer controls data access.

Business Question Complexity

Users can ask some seemingly simple business questions that require complex capabilities. For example, "What are this year's revenues for last year's top selling products?" seems like a straightforward business question, yet not all BI tools can readily answer it. A SQL subquery can handle this kind of question, or a vendor may use a proprietary approach to resolve it. Querying two star schemas such as inventory and sales to calculate days' sales inventory (DSI) requires multipass SQL, something straight SQL cannot handle.

While RDBMSs have increasingly added analytic functions such as RANK to enable more robust reporting, not all BI tools support these new functions. In the best case, the BI tool will use a proprietary approach to deliver comparable functionality. The pitfall with the proprietary approach is that it may require more data to pass over the network than if the RDBMS had performed the task. Alternatively, some tools let IT developers custom-code the SQL. In the worst-case scenario, users are forced to do a lot of manual data manipulation in Excel.

Business Objects allows users to ask complex questions by supporting the full range of SQL functions, including newer analytic functions. It uses simple drop-down boxes to make it easy for the query author to ask the question. For example, to generate a subquery, users select Advanced Query Filters to nest their conditions as shown in Figure 3.

Figure 3 Business Objects WebIntelligence Advanced Filter allows users to easily create subqueries.

Cognos ReportNet technically can answer complex business questions, but the implementation of it makes it difficult for all but the most expert of users.

Crystal does not support SQL HAVING, INTERSECT, subqueries, or multi-star queries.

Informatica does support HAVING and multi-star queries, but does not support INTERSECT and other set operators.

MicroStrategy supports a number of the SQL extensions and uses proprietary functions in its Intelligence Server to further process the data, providing users with robust analysis capabilities.

Microsoft's Reporting Services includes a basic Query Editor. But for more complex SQL capabilities, developers must modify the SQL directly.

Next Focus: Reporting

This issue, I covered some of the key differences in query capabilities between major BI tools. Next time, I'll look at reporting features that allow you to transform your query results into powerful documents that help you more quickly discover business opportunities.

Cindi Howson [[email protected]] is the president of ASK, a BI consultancy. She co-teaches The Data Warehouse Institute's "Evaluating BI Toolsets" and is the author of Business Objects: The Complete Reference.

Resources

A Word About Methodology

In addition to deploying and supporting a number of BI tools, I've listened to clients and checked out discussion groups to determine what criteria are important. I've compared that information to vendor marketing messages about what vendors claim is different. For each of the products reviewed, I've installed and evaluated the most-recent versions hands-on and validated findings with customer references. For most features, I've looked for out-of-the-box functionality that does not require coding.