Essbase
From Wikipedia, the free encyclopedia
| Developer: | Hyperion Solutions Corporation |
|---|---|
| Latest release: | 9.2 / 2006 |
| OS: | Microsoft Windows, Linux, AIX, HP-UX, Solaris |
| Use: | Multidimensional database |
| License: | Proprietary EULA |
| Website: | www.hyperion.com |
Essbase is a multidimensional database management system (MDBMS) that provides a multidimensional database platform upon which to build analytic applications, ie. those that are optimized for multiple serial queries, processed in realtime by users. Essbase, whose name derives from "Extended Spread Sheet database", was originally developed by "Arbor Software", which merged with "Hyperion Software" in 1998. It is currently available from Hyperion Solutions Corporation.
The term on-line analytical processing (OLAP), was initially crafted with the aim of establishing a way to communicate the benefits of this technology in contrast to that of relational database management systems (RDBMS). The fundamental difference was that this database technology was optimized for processing human queries rather than transactions. The results of this orientation was that MDBMS oriented their performance requirements around a different set of benchmarks (Analytic Performance Benchmark (APB-1)) than that of RDBMS (Transaction Processing Performance Council (TPC)).
Many Hyperion products were renamed in 2005, giving Essbase an official name of Hyperion® System™ 9 BI+™ Analytic Services™, but the new name was largely ignored by practitioners. The Essbase brand was later returned to the official product name for marketing purposes, but the server software still carries the "Analytic Services" title.
Hyperion Essbase was named as one of the 10 most influential technology innovations of the last 10 years by Information Age magazine in its August 2005 issue. Hyperion Essbase was ranked in the top 10 along with Netscape, Blackberry, Google, virtualisation, Voice Over IP (VOIP), Linux, XML, the Pentium processor and ADSL. Writing in Information Age's 10th anniversary issue, editor Kenny MacIver said: "Hyperion Essbase was the multi-dimensional database technology that put online analytical processing on the business intelligence map. It has spurred the creation of scores of rival OLAP products – and billions of OLAP cubes"
Contents |
[edit] History and Motivation
Although Essbase has been categorised as a general-purpose multidimensional database, it was originally developed to address the scalability issues associated with spreadsheets such as Lotus 1-2-3 and Microsoft Excel. Indeed, the patent covering Essbase uses spreadsheets as a motivating example to illustrate the need for such a system. [1]
In this context, "multi-dimensional" refers to the representation of financial data in spreadsheet format. A typical spreadsheet may display time intervals along column headings, and account names on row headings. For example:
| Jan | Feb | Mar | Total | |
|---|---|---|---|---|
| Quantity | 1000 | 2000 | 3000 | 6000 |
| Sales | $100 | $200 | $300 | $600 |
| Expenses | $80 | $160 | $240 | $480 |
| Profit | $20 | $40 | $60 | $120 |
If a user wants to break down these values by Region, for example, this typically involves the duplication of this table on multiple spreadsheets:
|
|
|
An alternative representation of this structure would be a three-dimensional spreadsheet grid, giving rise to the idea that "Time", "Account", and "Region" are dimensions. As further dimensions are added to the system, it becomes very difficult to maintain spreadsheets that correctly represent the multi-dimensional values. Multidimensional databases such as Essbase provide a data store for values that exist, at least conceptually, in a multi-dimensional hypercube.
A technical problem faced by multidimensional databases is the physical representation of data as the number and size of dimensions increases. Say the above example was extended to add a "Customer" and "Product" dimension:
| Dimension | Number of dimension values |
|---|---|
| Accounts | 4 |
| Time | 4 |
| Region | 3 |
| Customer | 10,000 |
| Product | 5,000 |
If the multidimensional database reserved storage space for every possible value, it would need to store 2,400,000,000 (4 × 4 × 3 × 10000 × 5000) cells. If each cell is represented as a 64-bit floating point value, this equates to a memory requirement of at least 17 gigabytes. In practice, of course, the number of combinations of "Customer" and "Product" that contain meaningful values will be a tiny subset of the total space. This property of multi-dimensional spaces is referred to as sparsity.
The patent[1] describes the method used by Essbase to reduce the amount of physical memory required, without increasing the time required to look up closely-related values. Put briefly, Essbase requires the developer to tag dimensions as "dense" or "sparse". The system then arranges data to represent the hypercube into "blocks", where each block is multi-dimensional array made up of "dense" dimensions, and space is allocated for every potential cell in that block. Sparsity is exploited because the system only creates blocks when required. In the example above, say the developer has tagged "Accounts" and "Time" as "dense", and "Region", "Customer, and "Product" as "sparse". If there are, say, 12,000 combinations of Region, Customer and Product that contain data, then only 12,000 blocks will be created, each block large enough to store every possible combination of Accounts and Time. The number of cells stored is therefore 192000 (4 × 4 × 12000), requiring under 2 megabytes of memory.
Because this implementation is hidden from front-end tools (i.e., a report that attempts to retrieve data from non-existent cells merely sees "null" values), the full hypercube can be navigated naturally.
[edit] Calculation Engine
OLAP systems generally provide for multiple levels of detail within each dimension. A Time dimension, for example, may be represented as a hierarchy starting with "Total Time", and breaking down into multiple years, then quarters, then months. An Accounts dimension may start with "Profit", which breaks down into "Sales" and "Expenses", and so on. Depending on the implementation, the calculated values at summary levels of detail may be generated on the fly, or pre-calculated ahead of time.
In addition, many OLAP systems provide some means to produce arbitrary calculated results on the fly (such as via MDX queries). Essbase takes a somewhat different approach to calculations. Calculations can be specified as:
- the aggregation of values through dimensional "hierarchies";
- stored calculations on dimension members;
- "dynamically calculated" dimension members; or
- procedural "calculation scripts" that act on values stored in the database.
The first method (dimension aggregation) is implicitly performed through addition, or by selectively tagging branches of the hierarchy to be subtracted, multiplied, divided or ignored. Also, the result of this aggregation can be stored in the database, or calculated dynamically on demand -- members must be tagged as "Stored" or "Dynamic Calc." to specify which method is to be used.
The second method (stored calculations) are specified by entering a formula against the dimension member -- when Essbase calculates that member, the result is stored against that member just like a data value.
The third method (dynamic calculation) are specified in exactly the same format as stored calculations, but are calculated when a value addressed by that member is accessed by a user, and are not stored.
The fourth method (calculation scripts) are written in a procedural programming language specific to the Essbase calculation engine. The results of running the calculation are stored as data values in the database. A calculation script must also be executed to trigger the calculation of aggregated values or stored calculations as described above -- a built-in calculation script (called the "default calculation") can be used to execute this type of calculation.
[edit] User Interface
The most widely known user interface to Essbase is an add-in for Microsoft Excel (previously also Lotus 1-2-3). The add-in adds a menu to the spreadsheet application that can be used to connect to Essbase databases, retrieve data, and navigate the cube's dimensions ("Zoom in", "Pivot", etc).[2]
With the release of System 9, Hyperion provided a new user interface add-in for Essbase called "SmartView for Microsoft Office". SmartView provides access to Essbase and other System 9 content for Microsoft Powerpoint, Microsoft Word, Microsoft Outlook as well as supplanting the previous add-in for Microsoft Excel.
In 2005, Hyperion began to offer a visualization tool called Tableau under the name "Hyperion Visual Explorer".[3] Tableau was originally developed at Stanford University as a government-sponsored research project to investigate new ways for users to interact with relational and OLAP databases.
Other user-facing applications with support for Essbase databases are:
- Hyperion Analyzer (aka Hyperion System 9 BI+ Web Analysis)
- Hyperion Reports (aka Hyperion System 9 BI+ Financial Reporting)
- Hyperion Intelligence (aka Hyperion System 9 BI+ Interactive Reporting)
- Hyperion SQR (aka Hyperion System 9 BI+ Production Reporting)
- Alphablox
An API is available for C, Visual Basic and Java, and embedded scripting support is available for Perl. The standardised XML for Analysis protocol can be used to query Essbase data sources using the MDX language.
[edit] References
- ^ a b Earle, Robert J. (1992) "Method and apparatus for storing and retrieving multi-dimensional data in computer memory". United States Patent 5,359,724 assigned to Arbor Software Corporation.
- ^ Hyperion Solutions Corporation (2006). Essbase Database Administrator's Guide.
- ^ Tableau Software (2005). Tableau Software Lands Global OEM Deal with Hyperion. Press release.
