PowerPlay 4.1

• Cognos Inc., 67 S. Bedford St., Burlington, MA 01803; 800-426-4667, 617-229-6600, or fax 617-229-9844.
• Pricing: Single copy, $695 (includes PowerPlay Transformer, PowerPlay Explorer and Reporter user components, and PowerPlay portfolio).
• Minimum Requirements: Windows 3.1, 386 or later (486 is recommended), and 4MB of RAM.

Impromptu 3.0

• Cognos Inc., 67 S. Bedford St., Burlington, MA 01803; 800-426-4667, 617-229-6600, or fax 617-229-9844.
• Pricing: User version, $495; administrator version, $595.
• Minimum Requirements: Windows 3.1, 386 or later (486 is recommended) and 6MB of RAM.

One of the most populated segments of the client/service landscape is the end-user query, analysis, and reporting tools category. Cognos offers two fine products targeted at non-technical end users. PowerPlay is an analysis tool that emphasizes crosstabs and graphics. Impromptu is a report writer based primarily on prebuilt templates. Both require administrators to perform some set-up duties prior to unleashing them on users.

PowerPlay

Transformer. The Transformer converts external data into a PowerPlay analytical model and one or more extract files for use in the PowerPlay Explorer. The extract file contains the data (which PowerPlay compresses), definitions for analytical dimensions such as date, region, and product line, and numeric measures such as revenue, expenses, and profit. The incoming data can be transactional records or pre-summarized totals already prepared for decision-support purposes, but the data must be a single result table. You can use Impromptu or any other query tool to produce a text file containing the input data. PowerPlay also reads Xbase files (.dbf), Paradox tables, and Lotus and Excel spreadsheet files.

After selecting an input file, the Transformer displays four multiple document interface (MDI) child windows: a dimension map across the top half of the main window, and smaller windows below that for data columns, measures, and extracts. You can rename columns, or change properties of any item in the lower windows. It's a good idea to drag and drop columns into the measures window so PowerPlay will know these are not potential dimensions. You can click on a toolbar button to make PowerPlay scan the data file and automatically generate dimensions that appear across the top of the dimension map window. Another toolbar button populates the unique values into each dimension and measure. At this point, you can double-click on a measure name to see a hierarchical display of its submeasures and the values in each level. The last essential step is to generate an extract file from the model. In addition to the default full model extract, you can create additional extracts based on subset models. For example, a subset model based on region yields an extract limited to data for that region. You can launch the Explorer to view the data in the extract.

The Transformer does a pretty good --but not perfect -- job of guessing which columns should be dimensions (or nested dimensions) and which should be measures. It's easy to create new dimensions and new measures (including calculated measures) and to delete or reorganize generated dimensions. PowerPlay has built-in knowledge of dates, so it generates dimensions for years, months, and quarters automatically. It also creates several relative time dimensions automatically, including current month, last month, quarter-to-date, prior quarter-to-date, year-to-date, and prior year-to-date. You can also create other special time categories, including custom fiscal years. Flexible time categories are critical to business analysts. Compared to relational databases and SQL, PowerPlay, like many other OLAP tools, offers far superior support for flexible time periods and automatic time consolidations.

In addition to using the Transformer to access data from relational databases, PowerPlay is one of the first third-party client tools that supports Arbor Software's Essbase, a multidimensional server. After establishing a connection from within the Explorer, users can double-click on dimension names and values to begin drilling down through data stored on an Essbase server.

Explorer. After you have packaged your raw data in the Transformer, the Explorer lets you view and manipulate it in several ways. (See Figure 1.) Explorer lets you open one or more views (from the same or different models), each in its own MDI child window. Each view window has a toolbar-like area across the top, but instead of buttons, you see folder icons for each dimension and measure. Below the dimension line is the data area. The initial display is a crosstab grid, complete with total lines for rows and columns. The data reflects the current dimensions you selected from the folder toolbar. Double-clicking on a dimension folder opens a filter window that lets you change the current value for any dimension. Each folder displays the name of the value you selected in that dimension.

You can also modify the crosstab by dragging and dropping new dimensions into the row or column axis, and you can swap the rows and columns using the mouse. However, I quickly realized that PowerPlay's crosstabs are pretty flat: They show only one level at a time on the rows and columns, and one measure at a time in each row and column cell. In contrast, Microsoft Excel Pivot Tables let you create complex crosstabs with multiple dimensions on each axis and multiple measures at the intersection of each row and column. Mark Morton, PowerPlay's product manager, told me that this design is intentional because it simplifies the interface. I suspect that future versions of PowerPlay will have to find a way to gain additional flexibility while remaining easy to use.

In the meantime, all is not lost. PowerPlay lets you create layers by dragging a dimension into the upper left corner of the crosstab grid. Layers act like a stack of sheets, and you navigate them using the up and down arrows above the grid. I discovered that you can open multiple copies of a view and tile the windows to see more than one layer (or display view) at a time. Speaking of display views, the crosstab grid is the initial view, but you can use toolbar buttons to see the data in one of several chart types: a simple bar graph, a clustered bar graph, a stacked bar graph, a pie chart, a single line, multiple line, or correlation graph (bars and lines).

Armed For Analysis

PowerPlay provides several other analytical features. In the crosstab grid view, you can switch from viewing numeric values in each cell to percent of row or column, or as a percent of the layer or a percent of the grand totals for the view. You can highlight exceptional data by defining ranges along with a color for each criterion. You can suppress data from view (and exclude it from calculations) by formatting it as hidden, by resizing its row or column to zero, or by using the "Suppress Zero Values" option. You can impose filters by selecting a category from a floating list box (you do not need to create or enter expressions).

Other Tools

PowerPlay's Portfolio creates presentations consisting of slides onto which users place views or reports. The slides can be displayed in sequence. Slides can also have pushbuttons that jump to related slides. Because the extracts created by the Transformer are static snapshots of transactional databases, the Scheduler is an essential component because it updates the extracts and recalculates derived values so that views and reports are refreshed with current data.

Overall, I am impressed with PowerPlay's combination of ease of use and analytical depth. However, I'd like to see even more flexible display options such as multilevel hierarchical formatting of summary and detail values on the same screen.

Impromptu

Unlike many other report writers that can connect to and access SQL databases directly, all Impromptu reports must be based on catalogs. Administrators create catalogs by connecting to a database and choosing the tables and columns that users will see. Data items are packaged into folders, and a folder can include columns from multiple tables because the administrator defines the joins, about which users never need to worry. Of course, administrators can rename columns using more business-oriented terms. Catalogs can also include filters and calculated data items. Impromptu also lets administrators print a report documenting the structure and contents of a catalog.

Impromptu accesses several client/server and desktop databases using ODBC, native drivers, and gateways. It also supports a special data format Cognos calls a "Hotfile." A Hotfile is a snapshot of a table that you can incorporate into a catalog. Hotfiles enable Impromptu to mix data from multiple databases, although the Hotfile is static, not live data. They also let users work on a report without connecting to a data source, similar to PowerPlay's extract files. You create Hotfiles by saving a report's data as a Hotfile.

A personal catalog intended for one user is the simplest type of catalog. An administrator can select a checkbox to upgrade a personal catalog into a distributed catalog. To fine-tune access by multiple users, administrators can also create classes of users, and then define filters limiting each user to a subset of the data (for example, by department or region). You can delegate administrative tasks to departmental users by changing selections in a security configuration dialog. Finally, Impromptu lets administrators activate several kinds of query governors in a catalog. You can limit the number of rows retrieved, the length of time a query can execute, allow, or prevent sorting on unindexed columns, and much more. Many end-user query tools require administrative setup, but few offer as much control as Impromptu.

Templates

HeadStart templates do not contain place holders for data, but they provide place holders for report titles and corporate logo graphics. They also provide structures for common report layouts such as record listings, screen forms, combined forms and listings, and so forth. The templates already have the frame elements that users would have to create if they were building a report from scratch, but they do not have any data fields or calculations. Impromptu does not use wizards to guide users though the process of creating new reports, but it does present a single dialog with multiple tabs, and users can enter any tabbed subdialog in any order. The tab pages obtain report specifications such as the data elements drawn from a catalog, sorting, grouping, and filter conditions.

While most report writers emphasize printed output, Impromptu reports are equally at home on screen. For example, a form and list report showing customer profile information in the form, and a list of orders by that customer, can have scrollbars on the list. Impromptu also places VCR buttons in the upper-right part of the screen so that users can navigate through a report's data.

One of Impromptu's default layouts for a new report is a mailing label, but I was surprised to see that Impromptu does not provide predefined Avery label layouts. Administrators can prepare and distribute them, but that should not be necessary. Instead, they should come with the product.

Impromptu supports OLE 2, including OLE automation. The product includes CognosScript, a Basic-like macro language, but you can also use Microsoft's Visual Basic for Applications (VBA). Automation lets you run Impromptu reports from other applications and integrate Impromptu with other applications. Although powerful, this aspect of Impromptu is probably most interesting to developers and administrators rather than end users.

End-User Analysis Tools

Figure 1.