Arbor Software's Essbase Analysis Server 3.2 soups up spreadsheets and other front ends with multidimensional data display and analysis
Edmund X. Dejesus
With Arbor Software's Essbase Analysis Server 3.2, you can quickly construct data-query applications to satisfy the most detail-hungry data analysts as well as the most computerphobic members of upper management. You can deploy a variety of front ends, including the spreadsheet that you're probably already using for data-analysis chores, and you can load data automatically from many relational databases and data warehouses.
Or, should you feel the urge, you can drill through the consolidated multidimensional dat
a with automatically generated SQL calls to access the underlying raw data. At $43,250 for five concurrent user licenses, Essbase is pricey, but you get a complete solution for your money that offers a combination of speed and power.
Essbase supports a variety of
client front ends
, including such popular spreadsheets as Excel for Windows 4.0 or higher, Excel for Macintosh System 7, 1-2-3 for Windows release 4.0, and 1-2-3 for DOS release 2.4. By using existing spreadsheets, you can minimize your training and development costs by retaining your existing spreadsheet applications.
Other applications that front for Essbase include Cognos's PowerPlay, Trinzic's Forest & Trees, Andyne's Pablo, and several Comshare programs. You can develop your own front end for Essbase using Arbor's published API (and API Reference Manual) with Visual Basic or other development tools.
Up and Analyzing
To access Essbase from your spreadsheet, you first run the Spread
sheet Client program on the client system. Launching Excel 5.0 starts Essbase automatically as a nonintrusive add-in, producing an Essbase menu in the Excel menu bar and a handful of Essbase icons on the toolbar.
First, you must connect to the Essbase server by giving a user name and a password and then selecting from a list of the applications and databases to which you have access. Member selection is probably your next step. Here you can specify which of the many dimensions in your database you want to examine. You can also view the "outlines" that define dimensions, their members, and any underlying formulas (using the many internal Essbase functions).
Selecting Retrieve returns the data for the dimensions you've specified (all data resides on the server) and populates the spreadsheet with labeled values. You decide how to handle missing values, determine which language aliases for database field names to use, apply formatting, and control many other options. The data values retrieved are real
numbers, not pointers to locations, so you can distribute the resulting spreadsheet to anyone who needs the data -- without their needing access to Essbase (handy for use on laptops).
Double-clicking on data or labels zooms you in to the next-lowest level of detail. Pivoting between row and column displays of any member is as easy as dragging the member label to the row or column where you want it to appear. The whole table automatically rearranges instantaneously to accommodate your changes.
To break out child levels of a member (e.g., individual sales territories) as separate spreadsheets, you select Cascade. A combination of Essbase data calls and spreadsheet macros performs the task automatically, producing individual spreadsheet files that you can distribute.
The macro capabilities of your spreadsheet can combine with Essbase functions and Visual Basic for Applications to create attractive and powerful applications within the spreadsheet. All the power of advanced spreadsheet graphing
is at your disposal. You can specify that Essbase saves any changes made to the spreadsheet back in the Essbase database, or you can make the data read-only.
The SQL Drill
One new feature in version 3.2 is the optional SQL Drill Through. This lets you "drill through" the displayed numbers to examine the underlying database data (e.g., point-of-sale transaction records). The server creates SQL statements and sends them to the source database. Essbase can import data from many sources, including spreadsheets, flat ASCII files, and relational databases, such as dBase, DB/2, Oracle, Sybase, and most any other SQL- or Open Database Connectivity-compatible (ODBC) database.
Essbase can load data automatically, according to schedules that you determine, using data-load rules that you specify to select and filter the data. It can automatically construct multidimensional outlines, determining the dimensions, members of dimensions, and many other features by itself.
Alternative
ly, you can hand-craft outlines, deciding on levels of consolidation along dimensions, calculation formulas (including mathematics and logic, as well as conditional and multiple-pass operations), zoom levels, field-name aliases, and myriad other options. Essbase suggests denseness/sparsity labels for data (see the sidebar "Handling Sparse Data"), but you can override its suggestions.
An administrator can assign access to users down to the individual cell level, ensuring that folks don't see what they shouldn't. You can also assign read and write privileges at several levels.
Essbase Application Manager
lets administrators assemble multidimensional databases, handle user access, and set up data-loading from other databases. You can run Application Manager from a client machine.
Professional data analysts may find the 2-D world of a spreadsheet confining. That's why Arbor Software has formed partnerships, ensuring that Essbase can use a variety of other third-party, multidimensio
nal, on-line analytical processing (OLAP) client tools, such as the aforementioned PowerPlay, Forest & Trees, Pablo, and Comshare programs. These tools enable the company to offer an assortment of slice-and-dice and display features. You can also write custom applications to Essbase's published API.
No Free Lunch
Though expensive, Essbase is a solid investment. You get a complete solution for your money, with a combination of speed and power, open client/server front-end and database access, automated data loading and handling, and administrative and security features.
These strengths are all fueling Arbor's explosive growth. They're also making it the standard against which similar products must be measured.
PRODUCT INFORMATION
Essbase Analysis Server 3.2.......$43,250
Arbor Software Corp.
Sunnyvale, CA
(408) 727-5800
www.arborsoft.com
screen_link (33 Kbytes)
Essbase can use many popular spreadsheets as client front ends. These include Excel for Windows 4.0 or higher (shown here), Excel for Macintosh System 7, 1-2-3 for DOS release 2.4, and 1-2-3 for Windows release 4.0.
screen_link (29 Kbytes)
You can e
xplicitly define dimensions, members of dimensions, calculation formulas, and field-name aliases in Essbase Application Manager's Outline Editor.
Edmund X. DeJesus is a BYTE senior editor. He has been a professional programmer for over 15 years. You can contact him on the Internet or BIX at
edejesus@bix.com
.
Data to the Nth Dimension
