A fundamental
need in modern data analysis is to be able to view selected attributes in a multidimensional manner rather than as 2-D arrays of rows and columns. The OLAP model treats each attribute as a dimension in a multidimensional structure, and each data element is tied to the intersection of these dimensions. This model allows analysts to slice and dice data in different 2-D views depending on the information needed, such as sales of different products over time or sales to various customer types in different regions.
The two products evaluated here -- Arbor Software's Essbase 4.1 and Oracle's Oracle Express Server 6.0 -- are the acknowledged market leaders in OLAP servers. Instead of having software perform a multidimensional
analysis
of data that's physically stored in 2-D relational form, these programs actually store data in a multidimensional structure.
Such databases are sometimes referred to as
physical cubes
(see the sidebar "Gleaning the Cube"). Both products allow hier
archies within a dimension, such as years broken down into quarters and months, or products grouped together into divisions. Both allow the use of popular spreadsheet programs for viewing and analyzing the data, and both provide Web gateways. Each product also provides access to data stored in relational databases, both by importing data directly from a relational database and by allowing SQL queries at run time.
Interestingly, it was E. F. Codd, the father of relational databases, who coined the term
OLAP
, contrasting it with on-line transaction processing (OLTP). In a white paper, Codd set forth 12 rules (the same number as his relational rules) for creating sound OLAP systems. Thus, Codd communicated a not-so-subtle message that he considered the two types of databases equally valid and that they should exist side by side. Codd went on to argue that multidimensional databases are better suited than relational databases for certain aspects of data processing.
Codd's white paper has been cri
ticized on the grounds that it was commissioned by Arbor Software and is thus prone to bias. But such criticisms miss the point: Although Codd's paper did indeed include a section extolling Essbase, its overall impact was not to establish Essbase as superior to its competitors, but to legitimize OLAP as a viable database product category. The endorsement of so formidable a proponent of relational technology as Codd was arguably the most significant factor in opening the door to a plethora of new products, as well as revitalizing some older product lines that had come to be viewed as outdated legacy systems.
Data in a multidimensional database typically comes from relational databases used for transaction-oriented production systems and for data warehouses. Both Essbase and Oracle Express Server can import data from relational databases using Open Database Connectivity (ODBC) drivers.
Both can
import dimension members (e.g., the names of the products in a products dimension, or the na
mes of the regions in a regions dimension) and can import relational data into a multidimensional structure.
Both products also support
pivoting
, which is moving a dimension to another location in a spreadsheet; for example, you could move months to display down the side and move products to display across the top, or display sales offices within products rather than products within sales offices.
In general, there is a performance trade-off between precalculated data, for which the system stores the results, and calculations that the system executes at analysis time. Precalculation takes some time to execute, but it greatly speeds up queries. Virtual calculations, which Express Server uses, don't require explicit recalculation when the underlying data changes, but queries take longer because calculations must be performed while the queries execute.
Essbase data is hierarchically organized and can be consolidated in many ways, but without
virtual measures
(formulas calculated at
run time without the result being stored in the database), some values must be precalculated. Express Server allows this, but its lack of hierarchies and its inflexible consolidation process sometimes require execute-time calculation.
Arbor Software's Essbase
Arbor Software was the first vendor to seriously challenge the dominance of the relational model for large-scale databases when it introduced Essbase in 1993. And that early start shows in Essbase's overall power, ease of use and learning, and general maturity.
When choosing between two powerful products, where either one can fully meet your functionality needs, the choice comes down to usability or "deployability," the ability to surmount learning-curve issues quickly and get a working OLAP system on-line and functioning in minimal time. Once deployed, the system can be enhanced and customized through a relatively quick selection of options without the need for any programming.
Essbase offers a convenient drag-and-drop i
nterface for defining dimension hierarchies, and it provides a single-step facility for recalculation. The program also includes built-in features for performing many tasks for which Express Server requires programming.
All this usability and convenience does not compromise Essbase's power and capability, however. While lacking a few of Oracle Express Server's capabilities, Essbase scored higher in NSTL's overall versatility evaluation. A large and growing number of third-party vendors provides a variety of analytical tools for Essbase databases, and there's an API for custom-developed applications.
Essbase's Application Manager, its administration interface, includes an outliner interface for defining dimension hierarchies. Users can quickly drag and drop dimension members to their appropriate positions in relation to other members. Most administrative tasks are intuitive in the on-line interface, the only serious exception being the process of matching input fields with database dimensions when impo
rting hierarchical data.
Essbase offers architectural and structural advantages, multiuser read/write access, and cell-level locking. Dimensions can include label-only members and shared members (i.e., members occurring at multiple points within a dimension hierarchy), and measures and dimensions can include hierarchical structures. The intelligent calculator saves processing time by recalculating only those values that have changed rather than the entire database, and it does this in a single step.
Sparse dimensions
are those that have a large number of empty data values. For example, a company might sell some products in domestic regions and other products in international regions. This results in a number of empty data values at various intersection points of the product and region dimensions. Essbase allows administrators to define certain dimensions as sparse, and the program optimizes storage to facilitate efficient data retrieval and minimize wasted space.
Essbase provides interfac
es for both Microsoft Excel and Lotus 1-2-3, and users can update data in the database as well as read it. Essbase has convenient menu options for updating spreadsheets and for drilling down to detail-level data in relational databases. The Web gateway in Essbase is based on a small number of proprietary extensions to Hypertext Markup Language (HTML). A relatively straightforward set of embedded HTML tags adds database-access capability to standard Web forms.
Essbase doesn't support virtual measures, as Express Server does, but it supports hierarchies within measures, similar to the hierarchies within dimensions. Thus, an expenses dimension might be broken down into salaries, purchases, and so forth. Moreover, when consolidating elements at one level into the next-higher level, Essbase offers alternative consolidation methods rather than just adding the elements together. For example, you might define a profit measure, broken down into sales and cost, with profit defined as sales minus cost. This produces
the same effect as an Express Server formula, except that the result of the calculation is stored in the database as a separate value.
Oracle Express Server
Oracle's OLAP entry is both new and old, a powerful analytical tool that is in most respects considerably harder to use than Essbase. Though relatively new to the desktop world, multidimensional databases have been part of mainframe computing for decades. In fact, I.R.I., the company that developed Express Server, was in business from the 1970s until Oracle purchased it in 1995.
Like Essbase, Express Server isn't so easy to learn. The beta version we tested came with little documentation for database administrators, and there is no conceptual guide describing how the server works. The manuals are lacking in how-to instructions for accomplishing tasks; most of the time you have to hunt through the alphabetical command reference.
Defining hierarchies in Oracle Express Server is somewhat cumbersome; you must specify the pare
nt member for each individual dimension member, except for those at the top level. Express Server also allows the specification of relationships between dimensions, such as between year and month. However, the documentation doesn't clearly explain why someone would use related dimensions in some cases and hierarchies within a dimension in others.
Recalculating the database in Express Server requires rolling up each measure separately across every dimension -- in other words, three measures across six dimensions means 18 roll-up operations. You can automate this type of task with a batch process, but you still have to do it yourself. For importing data from relational databases, Express Server imposes a two-step process: First, you import the spreadsheet into a temporary worksheet area, and then you copy the data from the sheet to the database.
Express Server incorporates a powerful procedural language that offers even greater flexibility than Essbase, providing you're willing to do some programming. A
ll Express Server client tools require manual editing of configuration files to access the server. Express Server's Web gateway employs a complex programming language and requires storing procedures in the database to control database lookups and HTML formatting.
Express Server provides convenient facilities for defining virtual measures based on calculation formulas. Rather than importing relational data into the multidimensional database and storing it permanently, users define a formula that executes SQL commands at run time.
This is essentially the approach of relational OLAP (ROLAP) databases, and it allows Express Server to take a hybrid approach between multidimensional storage and relational lookup. Generally, Oracle advises users to store summary data in the multidimensional database and use lookup formulas to the relational database when drilling down to the detail level. Express Server also offers a more extensive set of calculation functions than Essbase, including forecasting functions an
d moving-aggregate functions, and it makes available a wider range of client-side analysis tools.
In past versions of Oracle Express Server, administrators managed sparsity by defining
conjoint
dimensions, which are essentially joins of two dimensions. The conjoint is treated as a single dimension for storage-and-retrieval purposes. One might define a conjoint of product and region to deal with different products sold in different regions and thus eliminate the empty storage areas. With version 6.0, Express Server has introduced sparsity management that's similar to Essbase's.
Oracle Express Server has two main interfaces for presenting data to the user. A spreadsheet interface allows Microsoft Excel users to retrieve and display data from their databases. However, Oracle Express Server's Windows client is only 16-bit and, while it has the ability to run on Windows 95, it will not work with Excel 7.0.
In addition to the spreadsheet interface, Oracle Express Server includes Express Analyze
r, which enables users to create
briefings
-- tabular and graphical displays of data, annotated with formatted text. Oracle offers several specialized analysis packages at extra cost, as well as Express Objects, a development environment for creating applications visually.
A Yes for Essbase
Both Essbase and Oracle Express Server are powerful products, offering extensive feature sets likely to meet the functional requirements of any business needing to perform multidimensional analysis on large volumes of data. The difference comes down to how easy it is to deploy each product, to get the data from its original source into a sensible multidimensional structure, and to begin performing meaningful analysis of that data without a steep learning curve or complex implementation.
From this standpoint,
Essbase is
clearly superior. Without sacrificing capability at all, it provides intuitive interfaces and straightforward procedures to accomplish tasks that o
ften require convoluted programming or multiple-step procedures in Oracle Express Server.
Evaluations in this report represent the judgment of BYTE editors, based in part on extensive tests conducted by NSTL, Inc., as documented in a recent issue of its monthly
Software Digest
Product Information
Essbase 4.1.....................$37,500 for five concurrent users
................................$2500 per additional user
(requires 32 MB of RAM; 30 MB
of disk space; and Windows NT,
OS/2, Solaris, HP/
UX, or OS/400)
Arbor Software Corp.
Sunnyvale, CA
Phone: (408) 727-5800
Fax: (408) 727-7140
Internet:
http://www.arborsoft.com
Circle 976 on Inquiry Card.
OLAP Serves Up Your Data
