|
According to the OLAP Report (www.olapreport.com), Arbor Softwareıs Essbase OLAP Server is one of the top five products in a market that is growing by about 40 percent per year. OLAP database servers store data in multidimensional database structures called hypercubes and deliver intuitive access and rapid query response times.
Until now, most Essbase implementations have been for financial analysis applications such as financial reporting and budgeting, where previous versions have proven especially well suited. These are high-value applications used by knowledge workers who demand intuitive end-user access, uniform "speed of thought" (less than five seconds) query response times, rapid deployment, and read/write capabilities so that proposed budgets and forecasts can be written back to the database for interactive review and revision. Because the spreadsheet is the medium of choice for the financial analysis community, the ability to draw data directly into Excel and Lotus 1-2-3 spreadsheets for review and manipulation is essential. On all of these points, Essbase shines.
Previous versions of Essbase achieved uniform, "speed of thought" query response times by precalculating all possible dimensional data summaries in advance. (See the DBMS review of Essbase 4.0, July 1996.) This approach left Essbase particularly vulnerable to the OLAP phenomenon of "combinatorial explosion," the tendency of OLAP databases to grow exponentially as dimensions and member categories are added. (See Nigel Pendseıs discussion of "compound growth factors" in the OLAP Report.)
Because the typical financial hypercube is comparatively limited in dimensionality and has a logical structure that meshes well with Essbaseıs physical architecture, combinatorial explosion has not inhibited Arbor from gaining a major share of the OLAP database server market for financial applications.
However, Arbor has long held ambitions of breaking into the market for other types of applications, including various kinds of operational analysis and, especially, the marketing analysis applications where the relational OLAP (ROLAP) vendors currently hold sway. The ROLAP vendors have been able to hold the market for large-scale marketing applications by containing the problem of combinatorial explosion through selective (rather than total) precalculation of dimensional summaries and by dynamically sorting, filtering, and pivoting on "attributes" of primary member categories without making them full-fledged, stored dimensions. Attributes are qualifying characteristics of primary members (for example, the color or size of a particular product).
These compromises result in the loss of uniformly fast response times (only the precalculated query results are retrieved quickly) and a slower deployment (while an aggregation strategy is formulated), but they have been necessary to achieve the extreme scalability demanded by retail sales applications with 500 stores that sell 200,000 products, each with numerous attributes.
The most important improvements in Essbase 5 address these issues head on, resulting in a much more versatile and scalable product. The first challenge in taming combinatorial explosion was to find a way to reduce the percentage of precalculated cells in a hypercube while minimizing the negative impact on query response time. Essbase 5 addresses this issue by allowing the value of a calculated measure or aggregated parent to be "dynamically calculated" when it is requested in a query.
Consider the case of a budgeting application with a "Scenario" dimension with the following members: Actual, Budget, Variance, and Percent Variance. In version 4, Essbase would precalculate and store all four values. In Essbase 5, the Variance and Percent Variance values can be dynamically calculated on request, cutting the size of the hypercube in half and reducing hypercube calculation times proportionally while adding imperceptibly to query response times. Member values can also be dynamically calculated and stored so only the first requester suffers any response time penalty.
With Essbase 5, you can partition a logical hypercube into several physical "multicubes," each of which can have somewhat different dimensionality. The partitions are linked along their common, conformed dimensions to let end users drill across from one related dataset to another and create views that span multiple physical databases.
The physical partitioning of Essbase cubes also lets you bring parallel processing technology to bear against the loading and aggregation of Essbase databases. For example, if a sales database is broken up by region, each regionıs data can use a separate processor (on the same SMP computer or on separate computers) for loading and aggregating its own Essbase partition. End users see the finished database as a single hypercube.
Essbaseıs scalability limits have never been defined by physical size per se ı disk space is cheap! The real constraint has always been the amount of time required to aggregate the hypercube and write it out to disk versus the amount of time available to complete the job. In a typical single-processor Essbase 4 setup, the Essbase calculation engine writes out roughly 1GB per hour. Thus, a 50GB Essbase 4 hypercube took about two days to build. However, if you use dynamic calculations to cut the size of the hypercube in half and divide the remaining task among eight processors, Essbase 5 can comfortably build the same database within an overnight processing window.
When the OLAP Councilıs APB-1 benchmark for OLAP server performance was first run with Essbase 4.1, the incremental data load and aggregate calculation portions of the benchmark took more than five hours. When the same benchmark was run on the same server with Essbase 5, the incremental load and calculation took less than 11 minutes. At the same time, the after-calculation average query response time improved from less than a tenth of a second with Essbase 4.1 to less than a hundredth of a second for Essbase 5.
Extending the scalability of a product in one direction often exposes new weaknesses in other areas. While you can build much larger hypercubes with Essbase 5, youıll lose the ability to manage hypercube metadata graphically using the Outline Editor utility when the total number of member categories exceeds about 300,000. You must pay more attention than usual to stage more than a gigabyte of source data properly for efficient loading into Essbase, which may slow your deployment.
An attribute is a qualifying characteristic of a member category, such as the color or size of a product. Attaching attributes to member categories lets you filter members dynamically (for example, "show me only ıredı products"). OLAP servers that cannot filter dynamically on attributes must treat them as full-fledged dimensions, which contributes greatly to combinatorial explosion in applications such as product sales analysis with numerous product attributes. Essbase 5 allows an unlimited number of attributes to be attached to a member category for filtered reporting, although you still canıt pivot an attribute (put "month" down the side and "day-of-week" ı as an attribute of the date ı across the top of a view).
Essbase 5 can automatically calculate relative time dimension members such as period to date, current period, and prior period. Still missing is the ability to create automatically standard parent categories (week, month, quarter, and year) or attributes (day of week) in the date dimension from source data containing only one date field.
An Essbase hypercube can contain only numeric data. Essbase 5 adds the ability to attach nonnumeric data to hypercube data cells via linked objects. For example, you could append a note to a cell explaining why sales were depressed by a nonrecurring event in this period. A future analyst using this data to produce a forecast could drill to the explanation. All types of data objects can be linked to a data cell in this manner, including multimedia. However, the Application Manager is not yet able to attach large numbers of objects to numerous cells through a data load rules file.
Another data storage option is provided by DB2 OLAP, the result of a collaborative effort between Arbor and IBM, which permits those who like their data relational to store an Essbase hypercube physically in DB2 or other major RDBMSs. All Essbase tools (both Arbor and third party) work with DB2 OLAP just as they do with standard Essbase.
Because a majority of Essbase users are financial analysts, the most popular front-end query tool is the Essbase spreadsheet add-in (Excel or Lotus 1-2-3), which pulls data directly into the analystıs spreadsheet. The spreadsheet add-in includes a query wizard that leads you through a step-by-step process for defining new views and invoking sorting and filtering options.
Once the numbers are in the spreadsheet, you have full spreadsheet analysis capabilities. You can develop budgets, forecasts, and other projections and scenarios and then write the results back to Essbase for subsequent review and revision, a capability that few other OLAP database servers possess. Database security extends down to the cell level.
Essbase comes with a complimentary copy of Crystal Info for Essbase, which you can use to produce reports. You can also explore an Essbase hypercube using graphical user interfaces such as Arborıs own Wired for OLAP, Cognosıs PowerPlay, Andyneıs PaBLO, Comshareıs Commander Decision, and IQ Softwareıs IQ Vision. These tools allow for a more intuitive, graphical approach to data exploration and permit the creation of executive information system (EIS)-style briefing books. (Note that not all Essbase 5 functionality is available through all the GUIs.)
Arbor offers a CGI-based Web gateway as another interface option for browser users. There is also a Java-based Web server version of Wired for OLAP, and most of the other third-party client GUIs mentioned also have Web server versions. Most of these products offer functionality comparable to that offered in their desktop client versions. InfoSpace and Track Objects also offer excellent Java-based Web application servers for Essbase databases.
For custom development, Arbor offers native APIs for most major object-oriented development environments, including Visual Basic, C++, as well as Borlandıs Delphi and Sybaseıs PowerBuilder. However, Arbor has not announced that Essbase will comply with either the OLAP Councilıs MD API or Microsoftıs OLE DB for OLAP API, the emerging industry standards. (Arborıs Wired for OLAP client already supports the OLE DB for OLAP API.)
Another recent addition to the Essbase product family is Essbase Objects, a library of ActiveX objects that can be used to build custom applications on top of an Essbase hypercube. Statistical analysis software vendor SPSS Inc. and GIS vendor Environmental Systems Research Institute Inc. have already employed Essbase Objects to connect their products to Essbase hypercubes.
Essbase is surprisingly easy to deploy. The key to Essbaseıs rapid deployability is its graphical administration toolset, the Essbase Application Manager. (See Figure 1.) The Essbase 5 Application Manager is little changed from its predecessor, adding only the functionality that is needed to accommodate new server functionality.
The Outline Editor is a graphical tool for viewing and editing the multidimensional structure of an Essbase hypercube. Simply by pointing and clicking and dragging and dropping, you can use the Outline Editor to control virtually all aspects of hypercube design, including the definition of dimensions, member name aliases, roll-up and drill-down paths (including unbalanced and multiple hierarchies), methods of aggregation (or allocation), complex calculated measures, and data storage options.
The Data Prep Editor controls the mapping of source data (relational or flat files) to the hypercube structure. Through the Data Prep Editor, rules files are built to automate the process of fleshing out the Essbase outline with a complete list of member category names from the source data, which are assigned to their proper places in the Essbase metadata outline. Once the outline is complete, other rules files are built to automate the loading of data from the source files into the hypercube.
For especially complex calculations and fine tuning of operating parameters, the Formula Editor is used to create "Calculation Scripts." Even with the Formula Editor, point and click is the rule. Together, these capabilities allow you to take a data source full of cryptic codes and empty category fields and quickly turn it into a useful decision-support database.
Essbaseıs scalability and versatility are much enhanced by the new architectural options. The combination of dynamic calculations, partitioned multicubes, and improved attribute filtering makes Arbor Essbase OLAP Server 5 suitable for a wide range of analytical applications across the enterprise, including financial analysis, human resources, operations, and customer service analysis. Except for specialized "database marketing" applications, Essbase 5 is even appropriate for most marketing and sales analysis.
Figure 1. Arbor Essbase Application Manager.