Essbase 4.0 is the latest version of Arbor Software's multidimensional database engine and online analytical processing (OLAP) application, first released in June 1992. Arbor Software is a publicly traded company with a hot stock, and it claims more than 650 users worldwide. The company invented the term OLAP in a marketing white paper authored by E. F. Codd & Associates and has done more than any other vendor to help build the rapidly growing OLAP application market. But does Essbase live up to the market and mind share it has captured so far?
There are a number of useful enhancements and additions in version 4.0. For example, on the client side there are new 32-bit versions for use on Microsoft Windows 95 and NT. A new data retrieval wizard guides users through the process of defining multidimensional worksheet views that allow data filtering and sorting to take place on the server. New worksheet styles are supported that let users define templates into which data is retrieved and automatically formatted. Finally, Essbase indexes may now be paged into local memory to allow for the use of bigger databases because indexes do not need to be fully loaded into RAM. On the server, new database optimization, user-defined dimension-building rules, and intelligent data-manipulation features help to speed up data loading; Essbase databases no longer have a 2GB volume limit; and a new database rollback function assists with recovery from server interruptions. Other enhancements include error checking and conditional branching in the Essbase scripting language and automatic logout of Essbase users based on a user-defined timer interval.
I installed Essbase 4.0 on an Intel 486/75 with 32MB of RAM running Microsoft NT Workstation 3.51. The install took approximately one hour and proceeded painlessly through use of the wizard-driven InstallShield setup process. Essbase can be started automatically as a standard NT service and can optionally register itself in the NT Registry as part of the installation process. I used Microsoft Excel 5.0 for running the Essbase client software, which is added as a drop-down menu option on the Excel main menu bar. A typical Essbase environment consists of:
* a server running a SQL RDBMS that provides the source transaction data for the aggregated, dimensionally organized database that is managed by the Essbase database
* a server running the multidimensional Essbase database that, in this version, can theoretically handle terabyte-size databases with unlimited data dimensions and members
* desktop Windows or Macintosh clients running spreadsheet software that is used to retrieve and manipulate data directly from the Essbase server database
The Data Analysis Server, a multidimensional database engine, is the foundation component of the Essbase product suite. The Application Manager, Spreadsheet Client, and EssReporter are the desktop client database management, worksheet add-in, and worksheet reporting tools, respectively. The SQL Interface is used to extract data from ODBC-compliant data sources for loading into the Data Analysis Server. The Currency Conversion module is used to convert or translate monetary values from one currency to another. The SQL Drill Through is used to "drill through" to source transaction data in ODBC data sources from aggregated data held in the Data Analysis Server. The Extended Spreadsheet Macros and the Essbase API are used to assist in building custom client applications that use the Spreadsheet Client or the Data Analysis Server.
To use Essbase, you run the client-based Application Manager tool to create an application and one or more databases within the application. Then, each database is structurally defined through the building of a hierarchical outline. The outline is a logical representation of the database structure used for navigating and loading data and is easy to define using the graphical outline builder. The outline consists of multiple dimensions, each with one or more members. For example, a dimension might be a product and its members are individual product items. Essbase members based on numeric codes or IDs may have associated name aliases and may also be linked to formulas for deriving their value mathematically.
You can also use the Application Manager to maintain user groups and user logins to secure access to applications and databases, based on a login ID and password. You can set database access rights down to the database cell level to allow users no access, read only, or read/write rights, if required. This variation in access is achieved by creating data "filters" that can then be attached to specific users. Concurrent read/write access is supported, and version 4.0 now supports a rollback function for recovering from power failures.
Data is loaded into Essbase from a variety of file formats or directly from SQL database tables using ODBC data sources. Through user-defined rules, source data can be automatically reformatted, remapped, scaled, or converted in various ways before it reaches the Essbase database. Also, for frequently changing source databases, or in the case of an Essbase outline dimension with thousands of members, the data load can be configured to create new dimensions dynamically or populate existing dimensions in the outline. During the data load, Essbase recalculates values in the multidimensional "cube" and rebuilds indexes -- a step that is analogous to indexing relational database tables during a bulk copy.
There are two key results from this "prebuilding" of the database. First, Essbase databases can grow large quite quickly, and second, the data load phase or "consolidation" of the database can take some time. Competitive products such as TM/1 from TM/1 Software Corp. update only the database structures during data load and then recalculate values on the fly in response to client requests. This means that a TM/1 database will typically be more compact and faster to reload with data than its Essbase equivalent. However, precalculation does mean that Essbase should offer faster overall performance than TM/1, especially when handling larger databases with higher numbers of dimensions and members. In fact, it is possible to force Essbase to recalculate some or all of a database -- interactively or in batch mode -- through the use of calculation scripts defined using the functions and commands in the fully documented Essbase API.
After you connect to an Essbase server from the Spreadsheet Client, a worksheet is opened showing a single total value for all dimensions and labels for each dimension in specific row and column cells. Using the mouse or the menu bar, you can expand or collapse the member values displayed for a specific dimension, and these values can be displayed across (in columns) or down (in rows) the worksheet. Using the mouse to highlight cells or blocks of cells, you can focus on specific dimensions by excluding the nonhighlighted dimensions. There is a one-level "flashback" undo function if you get this wrong.
You can customize all types of aspects of the Essbase worksheet add-in, including how it behaves and how it displays dimension data visually through the use of varied fonts and colors. You can pivot dimensions displayed in the worksheet by dragging row dimensions beneath column dimensions or vice versa. You can use dialogs to limit the range of dimensions and members with which you work (the security system may already be limiting your view, of course), and then save these views in a library for reuse. You may also create specialized view templates for applying more sophisticated formatting to a view. You can input data into the worksheet, save it to the database, and then force a recalculation of either the whole database or a part of it (using a calculation script). To let you retrieve data from Essbase that has already been prefiltered and sorted, version 4.0 provides a Retrieval Wizard (see Figure 7, page 60) that builds a custom query against the database engine. You can specify the dimensions and members to include, their row and column orientation in the worksheet, multiple sorting criteria, and filters to let you see rankings of best- or worst-case values. These queries may be saved and made available to users through the security system.
Arbor's technology, marketing prowess, and the documented API to the Essbase engine have gained the company many partners in the software world. Decision-support tool vendors such as Cognos, Andyne, Platinum Technology, and Comshare have connected their client-based applications to the Essbase engine. Also, over a dozen client/server accounting software vendors have relationships with Arbor for feeding accounting transaction data into Essbase decision-support servers.
Version 4.0 is a major update. Like any sophisticated product, Essbase requires considerable IS or consulting help to set up, tune, and maintain the links to its transaction data sources. One obvious omission is the lack of any user-definable software agents to manage the database more intelligently or generate alerts. Perhaps Arbor's work with Comshare will eventually deliver agents for Essbase. Arbor also recently announced an Essbase Web Gateway that lets Web browsers access Essbase databases. Users can pivot, and drill down, up, and across dimensions by clicking on hyperlinks on Web pages generated by the Essbase Web Gateway.
Essbase ought to be on the short list of products evaluated for any enterprise-level sales or expense analysis, budgeting, or financial consolidation application. Anyone considering Essbase or other OLAP technology should also read Nigel Pendse and Richard Creeth's research report, "The OLAP Report" (Business Intelligence Inc., 1995) for a more detailed review.