Take a custom slice of your data with dynamic data viewing
Bobby Joe Reff
The electronic spreadsheet revolutionized the computer world with a row-and-column format that enabled quick analysis of data by a variety of formulas. Today's spreadsheets, however, need more dimensions as applications track more complex sets of data. While the consolidation features of multiple 3-D worksheet programs such as Lotus 1-2-3 and Microsoft Excel offer increased capability, it is not always easy to view certain slices or customize views of the data. The task of creating reports using the massive amount of data accumulated by multinational corporations becomes incredibly unwieldy. Today's powerful spreadsheets can handle the data, but grasping the significance of that data may require the power of dynamic
-viewing spreadsheets.
NSTL reviews three products that provide flexible viewing of data by simply rearranging a preset category or group. Although the three products are similar in providing dynamic-viewing capability, they contain subtle differences based on their primary purpose.
Lotus Improv, originally covered in this review, was discontinued by Lotus Development. The reviewers found that it was the program of choice when performing data modeling or when dynamic viewing is of primary importance. IThink is a modeling program, but it does not offer a dynamic-viewing spreadsheet. Lotus 1-2-3 release 5 for Windows now has some dynamic-viewing capabilities but only when combined with Lotus Approach.
Program Concepts
Dynamic-viewing capability is useful in both data modeling and data analysis applications. Modeling spreadsheets, such as Lotus Improv, specialize in creating a model to determine how different variables (e.g., sales forecasts or inventory levels) will affect outcomes (e
.g., profits). Data analysis makes sense of existing data by managing different views while retaining data relationships (e.g., looking at sales over time to explore trends and predictors).
Microsoft focuses on analysis rather than modeling as the key to Excel. Summarizing and analyzing large data sets require the comprehensive versatility of an advanced spreadsheet, and Excel offers this in its outlining, filtering, analysis tools (including functions), and, in version 5.0, tabbed notebooks.
Data analysis, however, is incomplete if you cannot interpret the meaning of complex data. This is where dynamic viewing becomes important. You can access pre-existing data from your spreadsheet to create PivotTables and maintain links to the data, regardless of how the view changes. Full-featured viewing lets you summarize data, collapse or expand categories, track data, analyze trends, and rearrange data to determine different outcomes without manually re-creating the model.
TM/1 Perspectives from
Sinpor takes a different approach. TM/1 attains true multidimensionality by adding database-type access to the capabilities of the spreadsheet you're already using. TM/1 stores the data in previously established dimensions; then you browse through the data tables residing in the spreadsheet.
This approach eliminates the need to rekey data or rearrange it for different views. It also offers an efficient storage system for data, while maintaining a spreadsheet's ability to analyze, manipulate, modify, and enhance data. The vendor calls this approach OLAP (On-Line Analytical Processing), since the consolidations are also dynamically calculated. This approach also lends itself well to multiuser systems. The Sparse Matrix technology adds to the inherent ability of storing the data once to incorporate large amounts of data by not allocating space to cells that do not contain data.
Like Microsoft Excel, Quattro Pro for Windows adds a dynamic-viewing module to its spreadsheet. Unlike Excel's module, Qua
ttro Pro's module is a separate program. However, it maintains hot links to and from the spreadsheet. The program offers a full range of viewing capabilities that aid you in spotting trends, making decisions, tracking data, summarizing data, and creating reports.
Building Models
You typically create models by either entering data manually (i.e., building from scratch) or importing data (usually from external databases). Creating models in any of the programs requires planning; poorly constructed structures only produce poor models that show nothing, regardless of how you try to manipulate the view.
Both Microsoft Excel and Quattro Pro for Windows require you to first input data into a spreadsheet and then create the dynamic-viewing model afterward. When building models, usability testers preferred Quattro Pro's modeling module over Microsoft Excel's PivotTables.
Excel incorporates the PivotTables (dynamic-viewing model) in a tabbed notebook, while Quattro Pro transfers the data to t
he Data Modeling Desktop. In Quattro Pro, you see a source window, with the category names in the top row and the data displayed beneath it. You drag the fields to the modeling window, and the program places the data, giving you a step-by-step creation procedure that aids in constructing a model.
Excel provides a PivotTable Wizard that forces you to set the framework of the model and then attempt to create it. Problems occur if the model is not perfect or if it contains an incorrect construction. A preview feature, similar to the one found in Text Wizard and Chart Wizard, would be very helpful when creating PivotTables.
TM/1 Perspectives' database approach to storing data can be confusing when you are first attempting to build models. With TM/1, you must create dimensions first and then create the tables using the dimensions. Next, you input the data into the table, and it is stored within the dimensions. The program limits text data to one dimension only and then requires that dimension to be p
laced last in the table.
Microsoft Excel uses Microsoft Query to build models from external data and accepts the data directly into PivotTable Wizard. TM/1 uses the underlying spreadsheet to import data and then updates the dimensions. Sinpor also offers the optional Spreadsheet Connector ($4995 and up), which connects multiple stations using TM/1 Perspectives in a client/server architecture. Quattro Pro queries data using the Database Desktop module.
Data Analysis
Each of the reviewed programs has a different way of analyzing data. Excel offers extensive analytical capabilities through the Excel spreadsheet. You can achieve better results using the analytical features found elsewhere and then using PivotTables to dynamically view the different aspects. The program does offer useful consolidation features, such as automatic summing of data, running totals, and automatic conversion of an entry to a percentage (e.g., percentage of total sales). The subtotaling feature also permits automatic
averaging, minimums, maximums, and variations. You cannot enter formulas into the PivotTables. You can view the detail in a new sheet that lets you work with the total flexibility the spreadsheet has to offer.
In Quattro Pro, you cannot modify data without creating the model with hot links to the spreadsheet. Creating the model using multiple tabbed sheets disables the hot links. The program does let you assign various mathematical qualifiers to data, such as summing, averaging, counting, increase, and automatic conversion to percentage. The limit dialog box replaces the page field concept used by the other programs but also adds the capability to easily consolidate user-specified subsets of the category.
All the programs collapse and expand groups, which aids in clarifying large amounts of data. The beauty of the expansion rests in the ``at your fingertip'' details of what the summary shows.
Reporting
Excel offers many options, and its charting is quite powerful. If you use custom
formatting to create borders and shading, you will lose the formatting when the view changes. However, you can use auto-formatting to reapply formatting each time a new view is generated. And by building a special PivotChart, you can also maintain charts built from your PivotTables.
TM/1 Perspectives makes use of the formatting capabilities of 1-2-3 for Windows or Excel's formatting capabilities. The formats for the table are attached to the cells, however, not to the data.
With Quattro Pro, you have to transfer your data from the Data Modeling Desktop back into the main spreadsheet program and then format it for the report. The program does not even keep the numeric format of the modeling module; it transfers everything back as general numbers. Formatting in Quattro Pro is easy, but if the view changes, you'll have to transfer the data again.
Thanks to 1-2-3's excellent spreadsheet enhancements, such as frames, borders, and colors, TM/1 outscores the other products for the sheer number o
f its high-quality reporting options. Excel also scores high in this regard, offering many different types of enhancements.
A Model Spreadsheet
Microsoft Excel represents the most advanced of all spreadsheets. Although not primarily a dynamic-viewing spreadsheet, the addition of this capability rounds out a powerful tool that can be used in a multitude of solutions. It scored high, considering that we tested none of its other powerful features.
TM/1 Perspectives provides analytical processing to normal spreadsheets. It lacks the drag-and-drop viewing found in the other programs and concentrates mainly on processing numerical data that needs to be manipulated in a variety of ways and settings. TM/1 accomplishes this with excellent integration within either Excel or Lotus 1-2-3. Its biggest weaknesses are its documentation and help system, which do not adequately prepare you for the program's complexity.
Quattro Pro adds the Data Modeling Desktop to its already-powerful spreadsheet. T
he module has more dedicated modeling functionality than Excel, but it does not offer the flexibility found in the other products. Having to transfer data back to the spreadsheet to format, print, or chart makes it a less attractive option. Its performance is poor; the program takes quite a bit of time to perform certain view changes that other programs accomplish in relatively short order.
The Facts
Microsoft Excel 5.0 $495
Microsoft Corp.
1 Microsoft Way
Redmond, WA 98052
(800) 426-9400
(206) 882-8080
Quattro Pro for Windows 5.0 $495
Standard Edition
$49.95
Novell, Inc.
WordPerfect Applications Group
1555 Technology Way
Orem, UT 84058
(800) 321-5906
TM/1 Perspectives 1.0 $795
Sinpor Corp.
31 Mountain Blvd., Building N
Warren, NJ 07059
(908) 755-9880
Highlights
STRENGTHS
Microsoft Excel
-- Most feature-rich spreadsheet; has excellent analysis tools
-- Microsoft Query can be invoked from within PivotTables
-- VBA for Microsoft Office can access PivotTables
Quattro Pro Workgroup Edition
-- Models easy to create
-- Excellent analysis tools
-- Database Desktop queries and imports data from databases
TM/1 Perspectives
-- Sparse Matrix technology handles large amounts of data well
-- Can make use of Excel's or 1-2-3's programming language
-- Spreadsheet Connector queries act as a server for TM/1 data
LIMITATIONS
Microsoft Excel
-- Cannot dynamically view data from multiple sheets without consolidating
-- Initially, models difficult to set up
Quattro Pro Workgroup Edition
-- Two separate modules
-- Hot links do not work with multiple sheets
-- Transferring data loses formatting
TM/1 Perspectives
-- Difficult to learn
-- Initially, models difficult to set up
-- No drag-and-drop capability
Illustration: Microsoft Excel combines dynamic viewing with a powerful, feature-rich electronic spreadsheet. You transfer data to a PivotTable to view the model in different perspectives. You create the model with PivotTable Wizard.
Illustration: Quattro Pro's dynamic-viewing module is a separate program. The Data Modeling Desktop provides good usability features, but the procedure for transferring the model back to the main spreadsheet for any type of output is cumbersome.
Table: DYNAMIC-VIEWING SPREADSHEETS (This table is not available electronically. Please see November, 1994, issue.)
Illustration: TM/1 Perspectives was one of the first products to make use of dyna
mic-viewing technology. It uses database-type storage procedures to handle large amounts of data. The TM/1 module accesses just the data needed from within Microsoft Excel or Lotus 1-2-3 (shown here).
Dynamic-Viewing Spreadsheets
