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.
End of Article
Prev. page
1
[2]
next page -->
