Essbase is a multidimensional database management system that provides a multidimensional database
platform upon which to build analytic applications. Essbase, whose name derives from "Extended
Spread Sheet dataBASE", began as a product of Arbor Software, which merged with Hyperion
Software in 1998. Oracle Corporation acquired Hyperion Solutions Corporation in 2007, as
of 2009 it markets Essbase as "Oracle Essbase". Until late 2005 IBM also marketed the product
— as DB2 OLAP Server. The database researcher E. F. Codd coined
the term "on-line analytical processing" in a whitepaper that set out twelve rules for
analytic systems. This whitepaper, published by Computerworld, was somewhat explicit in
its reference to Essbase features, and when it was later discovered that Codd had been
sponsored by Arbor Software, Computerworld withdrew the paper.
In contrast to "on-line transaction processing", OLAP defines a database technology optimized
for processing human queries rather than transactions. The results of this orientation were that
MDBMS oriented their performance requirements around a different set of benchmarks than
that of RDBMS). Hyperion renamed many of its products 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 carried the
"Analytic Services" title until it was incorporated into Oracle's Business Intelligence product
suite. [1] In August 2005, Information Age magazine named
Essbase as one of the 10 most influential technology innovations of the previous 10
years, along with Netscape, the BlackBerry, Google, virtualization, Voice Over IP, Linux,
XML, the Pentium processor and ADSL. 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".
History and motivation Although Essbase has been categorized 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. 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:
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 require 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". Sparsity
As the number and size of dimensions increases, developers of multidimensional databases increasingly
face technical problems in the physical representation of data. Say the above example was extended
to add a "Customer" and "Product" dimension: If the multidimensional database reserved
storage space for every possible value, it would need to store 2,400,000,000 cells. If
the software maps each cell 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.
Aggregation OLAP systems generally provide for multiple
levels of detail within each dimension by arranging the members of each dimension into
one or more hierarchies. 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 "Revenue" and "Expenses",
and so on. In the example above, if "Product" represents
individual product SKUs, analysts may also want to report using aggregations such as
"Product Group", "Product Family", "Product Line", etc. Similarly, for "Customer", natural
aggregations may arrange customers according to geographic location or industry.
The number of aggregate values implied by a set of input data can become surprisingly
large. If the Customer and Product dimensions are each in fact six "generations" deep, then
36 aggregate values are affected by a single data point. It follows that if all these aggregate
values are to be stored, the amount of space required is proportional to the product of
the depth of all aggregating dimensions. For large databases, this can cause the effective
storage requirements to be many hundred times the size of the data being aggregated.
Block storage Since version 7, Essbase has supported two
"storage options" which take advantage of sparsity to minimize the amount of physical
memory and disk space required to represent large multidimensional spaces. The Essbase
patent describes the original method, which aimed to reduce the amount of physical memory
required without increasing the time required to look up closely related values. With the
introduction of alternative storage options, marketing materials called this the Block
Storage Option, later referred to as Essbase Analytics.
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
comprises a 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, requiring under 2 gigabytes of memory,
plus the size of the index used to look up the appropriate blocks.
Because the database hides this implementation from front-end tools, the full hypercube can
be navigated naturally, and it is possible to load values into any cell interactively.
Calculation engine Users can specify calculations in Essbase
BSO 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 takes place implicitly 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 uses a formula against each calculated dimension member — when Essbase
calculates that member, the result is stored against that member just like a data value.
The third method is specified in exactly the same format as stored calculations, but calculates
a result when a user accesses a value addressed by that member; the system does not store
such calculated values. The fourth method uses a procedural programming
language specific to the Essbase calculation engine. This type of calculation may act upon
any data value in the hypercube, and can therefore perform calculations that cannot be expressed
as a simple formula. 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 can be used to execute this type of calculation.
Aggregate storage Although block storage effectively minimizes
storage requirements without impacting retrieval time, it has limitations in its treatment
of aggregate data in large applications, motivating the introduction of a second storage engine,
named Aggregate Storage Option or more recently, Enterprise Analytics. This storage option
makes the database behave much more like an OLAP database, such as SQL Server Analysis
Services. Following a data load, Essbase ASO does not
store any aggregate values, but instead calculates them on demand. For large databases, where
the time required to generate these values may become inconvenient, the database can
materialize one or more aggregate "views", made up of one aggregate level from each dimension,
and these views are then used to generate other aggregate values where possible. This
process can be partially automated, where the administrator specifies the amount of
disk space that may be used, and the database generates views according to actual usage.
This approach has a major drawback in that the cube cannot be treated for calculation
purposes as a single large hypercube, because aggregate values cannot be directly controlled,
so write-back from front-end tools is limited, and complex calculations that cannot be expressed
as MDX expressions are not possible. Calculation engine
Essbase ASO can specify calculations as: the aggregation of values through dimensional
hierarchies; or dynamically calculated dimension members.
The first method basically duplicates the algorithm used by Essbase BSO.
The second method evaluates MDX expressions against dimension members.
User interface Many users work with Essbase data using as
their interface an add-in for Microsoft Excel. 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. With the release of System 9, Hyperion provided
a new user interface add-in for Essbase called "Smart View for Microsoft Office". Smart View
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" [2]. Tableau originated 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 include: Hyperion Analyzer
Hyperion Reports Hyperion Enterprise Reporting
Hyperion Intelligence Hyperion SQR
Alphablox Arcplan dynaSight
Oracle Business Intelligence Suite Enterprise Edition
Applied OLAP Dodeca CXO-Cockpit Reporting Suite
The previous offerings from Hyperion acquired new names as given below:
APIs are available for C, Visual Basic and Java, and embedded scripting support is available
for Perl. The standardised XML for Analysis protocol can query Essbase data sources using
the MDX language. In 2007, Oracle Corporation began bundling
Hyperion BI tools into Oracle Business Intelligence Enterprise Edition Plus.
Administrative interface A number of standard interfaces can administer
of Essbase applications: ESSCMD, the original command line interface
for administration commands; MaxL, a "multi-dimensional database access
language" which provides both a superset of ESSCMD commands, but with a syntax more akin
to SQL, as well as support for MDX queries; Essbase Application Manager, the original
Microsoft Windows GUI administration client, compatible with versions of Essbase before
7.0; Essbase Administration Services, later renamed
Analytic Administration Services, and then back to 'Essbase Administration Services'
in v. 9.3.1, the currently supported GUI administration client; and
Essbase Integration Server for maintaining the structure and content of Essbase databases
based on data models derived from relational or file-based data sources.
Competitors There are several significant competitors
among the OLAP, analytics products to that of Essbase on the market, among them SAP BPC,
Microsoft SQL Server Microsoft Analysis Services,, IBM Cognos, IBMApplix TM1, Oracle OLAP, MicroStrategy,
and EXASolution. Also note that of the above competitors, including
Essbase, all use heterogenous relational or non-relational data sourcing to feed the cubes,
except for Oracle OLAP which may only use Oracle relational sourcing.
Export and/or product migration of Essbase As of 2009 two options can export Essbase
cubes into other formats: CubePort, a commercial conversion application,
converts Essbase cubes to the Microsoft SQL Server Analysis Services product. This product
performs an object-to-object translation that make up an Essbase cube, including: outline,
member formulas, calc scripts, data loading, report scripts to MDX queries, substitution
variables, and security model. It can extract from any platform version of Essbase, including
Oracle/Hyperion Essbase on Windows, Unix, AIX, HP UX, Solaris, IBM DB/2 OLAP, or AS/400
Showcase Essbase. OlapUnderground Outline Extractor performs
a pure, rudimentary, export of the outline, though it does not directly create any new
objects. The output is a simple text file that can be pulled indirectly into other OLAP
products, among other uses, such as synchronizing outlines. The Outline Extractor is now maintained,
supported and distributed free of charge by Applied OLAP, Inc.
See also OLAP
Oracle OLAP Business Intelligence
Data Warehousing Hyperion Planning
Comparison of OLAP Servers References
v11.1.2.3 documentation: * http:docs.oracle.comE40248_01portal_3.htm v11.1.1.3 documentation: * http:download.oracle.comcdnav/portal_3.htm
v9.3.1 documentation: * http:download.oracle.comcddoc/index.htm ^ http:www-306.ibm.comdatadb2olap/
^ Codd, E. F.; S B Codd; C T Salley. "Providing OLAP to User-Analysts:
An IT Mandate". Computerworld. ^ http:www.regdeveloper.com01olap_speed/
^ http:web.archive.org20070927190115companynews_releases/press_release_2005_000512.cfm ^ a b Earle, Robert J. "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. Essbase
Database Administrator's Guide. External links
Oracle EPM, BI & Data Warehousing Oracle Essbase
Hyperion at Oracle