Arbor Essbase OLAP Server 5

A high-end solution with enticing options

With all the excitement that Microsoft SQL Server OLAP Services (formerlycode-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, thestandards process, and the OLAP marketplace. Its Arbor Essbase OLAP Server 5shares 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 recentlyannounced merger between Arbor and a high-end analytical applications vendor,Hyperion Software, Arbor (which will operate under a new name, HyperionSolutions) seems poised to dominate the high end of the OLAP and analyticalapplications 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 ofparallel processing and works on symmetric multiprocessing (SMP) systems. Youcan partition data across servers and operating systems (OSs). You can even nestpartitions. Each partition can use Essbase's multidimensional or relational datastore; 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 usewith Essbase. These tools include:

The Basics of OLAP Servers
Before you use Essbase or another OLAP server, you need a basicunderstanding of OLAP terms. Some common terms include dimensions, members,cubes, cells, density, sparsity, and navigational directions.

Dimensions. Dimensions represent the facts you want toanalyze. For example, the APB-1 sales and marketing benchmark databaseconsists of six dimensions: Time (86 time periods consisting of 2 years ofmonthly data), Measure (15 measures representing financial calculations, such asmargins), Scenario (16 scenarios), Channel (10 channels), Customer (1000customers categorized into 3 levels), and Product (10,000 products categorizedinto 7 levels). Dimensions consist of members.

Members. Members are groupings of data that you use inanalyses. You can think of members as by items. For example, you cananalyze products by size, by color, by quarter, or by sales campaign. Essbaseuses hierarchical terminology (e.g., descendants and ancestors, roots andleaves, and generations and levels) to describe roles and relationships betweenmembers.

Cubes. A cube is a block of data that contains three or moredimensions. 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 coloredsquare 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 databasewould 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.

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 adifferent 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 specifydimensions that Essbase calculates as needed (e.g., calculate at run time),greatly reducing the time to load and calculate cubes. You can even storecalc-on-the-fly data on disk after calculation.

Navigational directions. OLAP users apply navigationalterms 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 anEssbase 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 thesix dimensions of Time, Measure, Scenario, Channel, Customer, and Product. TheTime and Measure dimensions have the attribute tags of Time and Accounts,respectively. The Time tag tells Essbase to break down the data according to thespecified reporting periods. The Accounts tag tells Essbase to use its built-inaccounting functionality, which provides variance reporting and othercalculations. 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 Essbasethat the dimension name is just a heading and therefore no data exists at thathierarchical level. The children, or members, under that dimension headingcontain the data. When Essbase encounters a Label Only tag, it displays thevalue of the dimension's first member.

Step 2. Use the Application Manager's Data Prep Editor tocreate rules for data loading and dynamic dimension building. As Screen 1 shows,the members have various codes representing those rules. The + and ~ codes tellEssbase whether (+) or not (~) to add members and roll up results into theparent. The ­ code tells Essbase to multiply the member by ­1 and thenadd the sum to the result of previous calculations performed on other members.The % code tells Essbase to divide the member into the sum of theprevious calculations performed on other members and then multiply by 100. TheDynamic Calc code tells Essbase not to perform the calculation duringthe calculation phase but to delay any needed calculations until runtime. TheShared Member code tells Essbase to share the member's data with one ormore other members. (Shared members must be in the same dimension. Thus, Essbasehas to store the data in only one location.) The Shared Member code includes apointer that points to the location of the data. The Two Pass Calc codetells Essbase that it needs to calculate the member's formula twice to producethe desired result.

When a code involves a formula, you can type in the formula directly or usethe Application Manager's Outline Editor. The types of calculations that Essbaseperforms include aggregations (additions and summaries), matrixcalculations (variances and percentages), cross-dimensional calculations (marketshares and product shares), OLAP-aware calculations (algebraic, statistical, andfinancial formulas), and procedural calculations (profitability, forecasts, and allocations). If you need custom calculations, you can use the OutlineEditor's Calculation Script Editor to create a calculation script in textformat. During this iterative process, you need to frequently load test data tomake sure the formulas work as you expect.

Step 3. Load the source data. Loading the data is a simplepoint-and-click affair using the Data Prep Editor. You can load data from avariety of sources, including relational and ODBC-compliant databases and ASCII,Excel, and Lotus 1-2-3 files.

Step 4. Perform calculations. By default, Essbase performscalculations based on the rules in the database outline. Calculations occurdimension by dimension. Essbase first performs calculations in the dimensionswith the Accounts and Time tags, which are typically dense. Essbase thenperforms calculations in the remaining dense dimensions. Essbase performscalculations in sparse dimensions last.

Step 5. Define reports, queries, or other customapplications. After you load the data and perform the calculations, you can usethe Arbor Essbase Spreadsheet Add-in to perform interactive OLAP and createreports, as Screen 2 shows. You can also use WIRED for OLAP or the Crystal Infofor Essbase report writer to create custom applications and reports.

These five steps provide only a glimpse into what you must do to runEssbase. Arbor's standard training is an intense 3-day workshop in which youlearn how to create, tune, and administer Essbase databases. As with relationaldatabases, you can back up, audit, reconfigure, and rebuild Essbase databases.

The Bottom Line
Essbase is a high-end multiplatform product that generally won't competedirectly with SQL Server OLAP Services for several reasons. Unlike OLAPServices, Essbase can run on multiple platforms, including Windows 9x. And withEssbase, you can build bigger databases than with OLAP Services--and those bigdatabases will have a reasonable calculation window. Finally, with Essbase, youcan expand your OLAP system with a variety of Essbase add-on modules andEssbase-ready products.