With all the excitement that Microsoft SQL Server OLAP Services (formerly code-named Plato) is generating, looking at an established player in the online analytical processing (OLAP) market can give you an idea of how a mature OLAP server works and what it can provide. Arbor Software is one of the most notable established players. Arbor was a founding member of the OLAP Council and was instrumental in developing a benchmark for OLAP servers, the APB-1 sales and marketing benchmark application database. The APB-1 benchmark is similar to the Transaction Processing Council (TPC) benchmarks that database management system (DBMS) vendors use.
Arbor continues to assert a leadership role in OLAP development, the standards process, and the OLAP marketplace. Its Arbor Essbase OLAP Server 5 shares market leadership with Oracle's Oracle Express Server. Many vendors sell products that work with Essbase. (IBM has even licensed Essbase for its DB2 OLAP Server. For more information, see the sidebar "DB2 OLAP Server 1.0," page 94.) And many users rely on Essbase to support complex calculations, provide good performance, and offer lights-out functionality. With the recently announced merger between Arbor and a high-end analytical applications vendor, Hyperion Software, Arbor (which will operate under a new name, Hyperion Solutions) seems poised to dominate the high end of the OLAP and analytical applications markets.
Essbase and Its Extended Family
Essbase is a multiplatform OLAP server that runs on Windows NT, Windows 9x, OS/2, AS/400, HP-UX, IBM AIX, and Sun Solaris. Essbase 5 takes advantage of parallel processing and works on symmetric multiprocessing (SMP) systems. You can partition data across servers and operating systems (OSs). You can even nest partitions. Each partition can use Essbase's multidimensional or relational data store; both stores operate with full functionality, including replication, calculation, querying, and navigation.
Arbor offers a full product line of developer and end-user tools to use with Essbase. These tools include:
- Arbor WIRED for OLAP, which provides Windows and Java browser versions of the OLAP client tool. You use WIRED for OLAP much like Visual Basic (VB); you construct a form using components such as the WIRED Designer. The WIRED Analyzer component lets you perform interactive queries and create reports. This tool supports email, export (including slide show screens and annotations), and other capabilities. It also works with MDSS.
- Arbor Essbase API, which has C++ and VB bindings. This API library contains more than 300 functions to create custom OLAP applications.
- Arbor Essbase Objects, which consist of eight OLAP-aware ActiveX controls.
- Arbor Essbase Web Gateway, which provides all essential OLAP features, including drill down, roll up, pivot, and full read and write capabilities from standard Web browsers.
- Arbor Essbase Adjustment Module, which integrates secure, auditable
controls for corporate adjustments
into a comprehensive reporting, analysis, and planning environment. - Arbor Essbase SQL Interface, which directly accesses more than 20 PC and SQL relational databases for data loading.
- Arbor Essbase SQL Drill-Through, which lets you drill down from Essbase to raw data. SQL Drill-Through supports Open Database Connectivity (ODBC) and includes native drivers for Oracle, Informix, Sybase, Microsoft (SQL Server), and IBM (DB2).
- Arbor Integration Server, which will include OLAP Builder and OLAP Architect tools that let developers create reusable OLAP dimensions, hierarchies, and sophisticated calculation logic that Essbase maintains in a shared OLAP metadata catalog. More than 20 partners will support this forthcoming tool.
The Basics of OLAP Servers
Before you use Essbase or another OLAP server, you need a basic
understanding of OLAP terms. Some common terms include dimensions, members,
cubes, cells, density, sparsity, and navigational directions.
Dimensions. Dimensions represent the facts you want to analyze. For example, the APB-1 sales and marketing benchmark database consists of six dimensions: Time (86 time periods consisting of 2 years of monthly data), Measure (15 measures representing financial calculations, such as margins), Scenario (16 scenarios), Channel (10 channels), Customer (1000 customers categorized into 3 levels), and Product (10,000 products categorized into 7 levels). Dimensions consist of members.
Members. Members are groupings of data that you use in analyses. You can think of members as by items. For example, you can analyze products by size, by color, by quarter, or by sales campaign. Essbase uses hierarchical terminology (e.g., descendants and ancestors, roots and leaves, and generations and levels) to describe roles and relationships between members.
Cubes. A cube is a block of data that contains three or more dimensions. Multi-dimensional cubes are better suited for complex data analyses than for relational databases because relational databases are limited to two dimensions. (For more information on the differences between two- and multi-dimensional databases, see the sidebar "OLAP, ROLAP, MOLAP, and HOLAP.")
An Essbase database consists of miniature cubes that make up a larger cube, or hypercube. The database also consists of indexes (which keep track of the cubes' locations) and a variety of additional files (such as a database outline that defines the structure of the database, load rules, a security file, log files, and calculation and report scripts).
Cells. If an OLAP cube were a Rubik's Cube, each colored square in the cube would be a cell. Cells can contain data or calculations, orthey can be empty.
Density and sparsity. A cube containing the APB-1 database would have more than 2 trillion possible cells. However, most of the cells would be empty, a concept called data sparsity. When most of the cells contain data, the concept is called data density.
| Arbor Essbase OLAP Server 5 |
|
Contact: Arbor Software * 408-744-9500 or 800-858-1666 Web: http://www.arborsoft.com Price: $25,000 for Arbor Essbase Enterprise Server, $10,000 for Arbor Essbase Development Server, $85,000 for Arbor Essbase Financial Data Mart, $3500 per concurrent Essbase user, $995 per PC for Personal Essbase. A dozen tools and specialized modules cost extra. System Requirements: Windows NT, Windows 9x, OS/2, AS/400, HP-UX, IBM AIX, or Sun Solaris servers, Windows, Macintosh, or UNIX clients, Web browsers, or network computers |
A major challenge that OLAP vendors face is how to deal with data sparsity. One approach is to store only those cells that contain data. However, this approach relies on the use of indexes, which quickly overwhelm the server because indexes take up more space than the data. When OLAP servers get bogged down under the weight of their indexes, end users must wait for the OLAP server to load and recalculate cubes with updated information.
Because the typical Essbase customer builds big cubes, Arbor uses a different approach to deal with data sparsity. (Arbor even holds a patent on its technique.) This approach begins with the identification of all dimensions as dense or sparse. When you are designing a database, Essbase makes educated guesses about whether a dimension is dense or sparse. (If a dimension is dense, a high probability exists that data will be present in the cells when you combine that dimension with others.) However, you can override Essbase's determinations. Essbase's patented indexing relies on assumptions it makes about cube contents based on the dimensions' sparsity or density.
Arbor also addresses data sparsity with another patented process: calc on the fly, a dynamic calculation option. With this option, you can specify dimensions that Essbase calculates as needed (e.g., calculate at run time), greatly reducing the time to load and calculate cubes. You can even store calc-on-the-fly data on disk after calculation.
Navigational directions. OLAP users apply navigational terms to describe typical actions. For example, users drill down (i.e., go from a summary to the underlying detailed data), roll up (i.e., go from specific to consolidated data), and pivot (i.e., switch to another member or dimension in an analysis).
A Taste of Essbase
Because of the amount of information that a hypercube holds, most Essbase
applications take as long as 3 months to develop and deploy. Implementing an
Essbase application typically involves five steps.
Step 1. Create an Essbase database outline using Essbase's GUI interface, the Arbor Essbase Application Manager. Screen 1 contains an example of a database outline. This excerpt from the APB-1 database shows the six dimensions of Time, Measure, Scenario, Channel, Customer, and Product. The Time and Measure dimensions have the attribute tags of Time and Accounts, respectively. The Time tag tells Essbase to break down the data according to the specified reporting periods. The Accounts tag tells Essbase to use its built-in accounting functionality, which provides variance reporting and other calculations. You can use only one Time tag and one Accounts tag in a database.
Several of the dimensions have a Label Only tag. This tag tells Essbase that the dimension name is just a heading and therefore no data exists at that hierarchical level. The children, or members, under that dimension heading contain the data. When Essbase encounters a Label Only tag, it displays the value of the dimension's first member.
Step 2. Use the Application Manager's Data Prep Editor to create rules for data loading and dynamic dimension building. As Screen 1 shows, the members have various codes representing those rules. The + and ~ codes tell Essbase whether (+) or not (~) to add members and roll up results into the parent. The code tells Essbase to multiply the member by 1 and then add the sum to the result of previous calculations performed on other members. The % code tells Essbase to divide the member into the sum of the previous calculations performed on other members and then multiply by 100. The Dynamic Calc code tells Essbase not to perform the calculation during the calculation phase but to delay any needed calculations until runtime. The Shared Member code tells Essbase to share the member's data with one or more other members. (Shared members must be in the same dimension. Thus, Essbase has to store the data in only one location.) The Shared Member code includes a pointer that points to the location of the data. The Two Pass Calc code tells Essbase that it needs to calculate the member's formula twice to produce the desired result.
When a code involves a formula, you can type in the formula directly or use the Application Manager's Outline Editor. The types of calculations that Essbase performs include aggregations (additions and summaries), matrix calculations (variances and percentages), cross-dimensional calculations (market shares and product shares), OLAP-aware calculations (algebraic, statistical, and financial formulas), and procedural calculations (profitability, forecasts, and allocations). If you need custom calculations, you can use the Outline Editor's Calculation Script Editor to create a calculation script in text format. During this iterative process, you need to frequently load test data to make sure the formulas work as you expect.
Step 3. Load the source data. Loading the data is a simple point-and-click affair using the Data Prep Editor. You can load data from a variety of sources, including relational and ODBC-compliant databases and ASCII, Excel, and Lotus 1-2-3 files.
Step 4. Perform calculations. By default, Essbase performs calculations based on the rules in the database outline. Calculations occur dimension by dimension. Essbase first performs calculations in the dimensions with the Accounts and Time tags, which are typically dense. Essbase then performs calculations in the remaining dense dimensions. Essbase performs calculations in sparse dimensions last.
Step 5. Define reports, queries, or other custom applications. After you load the data and perform the calculations, you can use the Arbor Essbase Spreadsheet Add-in to perform interactive OLAP and create reports, as Screen 2 shows. You can also use WIRED for OLAP or the Crystal Info for Essbase report writer to create custom applications and reports.
These five steps provide only a glimpse into what you must do to run Essbase. Arbor's standard training is an intense 3-day workshop in which you learn how to create, tune, and administer Essbase databases. As with relational databases, you can back up, audit, reconfigure, and rebuild Essbase databases.
The Bottom Line
Essbase is a high-end multiplatform product that generally won't compete
directly with SQL Server OLAP Services for several reasons. Unlike OLAP
Services, Essbase can run on multiple platforms, including Windows 9x. And with
Essbase, you can build bigger databases than with OLAP Services--and those big
databases will have a reasonable calculation window. Finally, with Essbase, you
can expand your OLAP system with a variety of Essbase add-on modules and
Essbase-ready products.
