The database researcher E. F. Codd coined the term "on-line analytical processing" (OLAP) in a whitepaper[2] that set out twelve rules for analytic systems (an allusion to his earlier famous set of twelve rules defining the relational model). 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.[3]
In contrast to "on-line transaction processing" (OLTP), OLAP defines a database technology optimized for processing human queries rather than transactions. The results of this orientation were that multidimensional databases oriented their performance requirements around a different set of benchmarks (Analytic Performance Benchmark, APB-1) than that of RDBMS (Transaction Processing Performance Council [TPC]).
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 Foundation Suite (BIFS) product.[4]
In August 2005, Information Age magazine named Essbase as one of the 10 most influential technology innovations of the previous 10 years,[5] along with Netscape, the BlackBerry, Google, virtualization, Voice Over IP (VOIP), 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".[6]
History and motivation
Essbase was originally developed to address the scalability issues associated with spreadsheets such as Lotus 1-2-3 and Microsoft Excel. Indeed, the patent covering (now expired) Essbase uses spreadsheets as a motivating example to illustrate the need for such a system.[7]
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:
North
Jan
Feb
Mar
Total
Quantity
240
1890
50
2180
Sales
$24
$189
$5
$218
Expenses
$20
$150
$3
$173
Profit
$4
$39
$2
$45
South
Jan
Feb
Mar
Total
Quantity
760
110
2950
3820
Sales
$76
$11
$295
$382
Expenses
$60
$10
$237
$307
Profit
$16
$1
$58
$75
Total Region
Jan
Feb
Mar
Total
Quantity
1000
2000
3000
6000
Sales
$100
$200
$300
$600
Expenses
$80
$160
$240
$480
Profit
$20
$40
$60
$120
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:
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 × 10,000 × 5,000) cells. If the software maps each cell as a 64-bit floating point value, this equates to a memory requirement of about 17.9GiB (exactly 19.2GB). 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 (6 × 6) 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 (Essbase Analytics)
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[7] 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 (Essbase BSO), 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 (4 × 4 × 12000), requiring under 2GiB of memory (exact 1,536MB), plus the size of the index used to look up the appropriate blocks.
Because the database hides this implementation 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, 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 (dimension aggregation) 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 (stored calculations) 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 (dynamic calculation) 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 (calculation scripts) uses a proceduralprogramming 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 (called the "default calculation") can be used to execute this type of calculation.
Aggregate storage (Enterprise Analytics)
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 (Essbase ASO) 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 (for example, the database may calculate all combinations of the fifth generation of Product with the third generation of Customer), 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 (dimension aggregation) basically duplicates the algorithm used by Essbase BSO.
The second method (dynamic calculations) evaluates MDX expressions against dimension members.
User interface
The majority of Essbase users work with Essbase data via an add-in for Microsoft Excel (previously also Lotus 1-2-3) known as Smart View. The Essbase Add-In is a standard plugin to Microsoft Excel and creates an additional menu that can be used to connect to Essbase databases, retrieve or write data, and navigate the cube's dimensions ("Zoom in", "Pivot", etc.).[8]
In 2005, Hyperion began to offer a visualization tool called Hyperion Visual Explorer (HVE) which was an OEM from Tableau Software. Tableau Software originated at Stanford University as a government-sponsored research project to investigate new ways for users to interact with relational and OLAP databases. Hyperion and Tableau together built fundamentally the first versions of Tableau Software which was designed specifically for multidimensional (OLAP) databases. Oracle quickly terminated the OEM arrangement with Tableau Software soon after the acquisition of Hyperion in 2007.
Most other well known analytics vendors provide user-facing applications with support for Essbase and include;
The previous offerings from Hyperion acquired new names as given below:
Hyperion's previous offerings
Hyperion System 9 BI+ offerings
Hyperion Essbase ASO
Enterprise Analytics
Hyperion Essbase BSO
Essbase Analytics
Hyperion Analyzer
Web Analysis
Hyperion Reports
Financial Reporting
Hyperion Intelligence
Interactive Reporting
Hyperion SQR
Production Reporting
Hyperion Metrics Builder
Enterprise Metrics
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 Essbase applications:
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 WindowsGUI 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.
Cloud offerings
Since 2017, Essbase Cloud has been available as part of the Oracle Analytics Cloud (OAC), a suite of analytics that include reports and dashboards, data visualization, inline data preparation and mobile.[13]
Competitors
There are several significant competitors among the OLAP, analytics products to that of Essbase (HOLAP/MOLAP) on the market, among them SAP BPC, Microsoft SQL Server Microsoft Analysis Services, (MOLAP, HOLAP, ROLAP), IBM Cognos (ROLAP), IBM/Cognos/Applix TM1 (MOLAP), Oracle OLAP (ROLAP/MOLAP), MicroStrategy (ROLAP), and EXASolution (ROLAP).
Also note that of the above competitors, including Essbase, all use heterogenous relational (Microsoft SQL Server, Oracle, IBM DB/2, TeraData, Access, etc.) or non-relational data sourcing (Excel, text Files, CSV Files, etc.) to feed the cubes (facts and dimensional data), except for Oracle OLAP which may only use Oracle relational sourcing.
Export and/or product migration of Essbase
As of 2009[update] 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 (load rules), 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.
Online analytical processing, or OLAP, is an approach to answer multi-dimensional analytical (MDA) queries swiftly in computing. OLAP is part of the broader category of business intelligence, which also encompasses relational databases, report writing and data mining. Typical applications of OLAP include business reporting for sales, marketing, management reporting, business process management (BPM), budgeting and forecasting, financial reporting and similar areas, with new applications emerging, such as agriculture.
An OLAP cube is a multi-dimensional array of data. Online analytical processing (OLAP) is a computer-based technique of analyzing data to look for insights. The term cube here refers to a multi-dimensional dataset, which is also sometimes called a hypercube if the number of dimensions is greater than three.
In computing, the star schema or star model is the simplest style of data mart schema and is the approach most widely used to develop data warehouses and dimensional data marts. The star schema consists of one or more fact tables referencing any number of dimension tables. The star schema is an important special case of the snowflake schema, and is more effective for handling simpler queries.
In computing, a snowflake schema or snowflake model is a logical arrangement of tables in a multidimensional database such that the entity relationship diagram resembles a snowflake shape. The snowflake schema is represented by centralized fact tables which are connected to multiple dimensions. "Snowflaking" is a method of normalizing the dimension tables in a star schema. When it is completely normalized along all the dimension tables, the resultant structure resembles a snowflake with the fact table in the middle. The principle behind snowflaking is normalization of the dimension tables by removing low cardinality attributes and forming separate tables.
In computer programming contexts, a data cube is a multi-dimensional ("n-D") array of values. Typically, the term data cube is applied in contexts where these arrays are massively larger than the hosting computer's main memory; examples include multi-terabyte/petabyte data warehouses and time series of image data.
IBM Planning Analytics powered by TM1 is a business performance management software suite designed to implement collaborative planning, budgeting and forecasting solutions, interactive "what-if" analyses, as well as analytical and reporting applications.
A pivot table is a table of values which are aggregations of groups of individual values from a more extensive table within one or more discrete categories. The aggregations or summaries of the groups of the individual terms might include sums, averages, counts, or other statistics. A pivot table is the outcome of the statistical processing of tabularized raw data and can be used for decision-making.
Multidimensional Expressions (MDX) is a query language for online analytical processing (OLAP) using a database management system. Much like SQL, it is a query language for OLAP cubes. It is also a calculation language, with syntax similar to spreadsheet formulae.
Applix Inc. was a computer software company founded in 1983 based in Westborough, Massachusetts that published Applix TM1, a multi-dimensional online analytical processing (MOLAP) database server, and related presentation tools, including Applix Web and Applix Executive Viewer. Together, Applix TM1, Applix Web and Applix Executive Viewer were the three core components of the Applix Business Analytics Platform.
Microsoft SQL Server Analysis Services (SSAS) is an online analytical processing (OLAP) and data mining tool in Microsoft SQL Server. SSAS is used as a tool by organizations to analyze and make sense of information possibly spread out across multiple databases, or in disparate tables or files. Microsoft has included a number of services in SQL Server related to business intelligence and data warehousing. These services include Integration Services, Reporting Services and Analysis Services. Analysis Services includes a group of OLAP and data mining capabilities and comes in two flavors multidimensional and tabular, where the difference between the two is how the data is presented. In a tabular model, the information is arranged in two-dimensional tables which can thus be more readable for a human. A multidimensional model can contain information with many degrees of freedom, and must be unfolded to increase readability by a human.
Business intelligence software is a type of application software designed to retrieve, analyze, transform and report data for business intelligence. The applications generally read data that has been previously stored, often - though not necessarily - in a data warehouse or data mart.
The Oracle Database OLAP Option implements On-line Analytical Processing (OLAP) within an Oracle database environment. Oracle Corporation markets the Oracle Database OLAP Option as an extra-cost option to supplement the "Enterprise Edition" of its database.
Palo is a memory resident multidimensional database server and typically used as a business intelligence tool for controlling and budgeting purposes with spreadsheet software acting as the user interface. Beyond the multidimensional data concept, Palo enables multiple users to share one centralised data storage.
The following tables compare general and technical information for a number of online analytical processing (OLAP) servers. Please see the individual products articles for further information.
CubePort is a commercial software application that converts from Oracle Essbase to the analogous Microsoft product Microsoft Analysis Services, which is built into Microsoft SQL Server. This application achieves this through various analogy mapping techniques, and is a standard client-server application that runs on a Windows computer but may connect to non-Windows servers. CubePort converts the various OLAP structures and syntaxes in the source through an extraction process, interprets, and recreates in the target. The objective is to simulate exactly the behavior of the original source system to the target system.
XLCubed is a business intelligence software and consulting services company. Established in 2001, XLCubed develops business intelligence software and provides business intelligence and performance management consulting services. The company is privately held and based out of the United Kingdom in the Thames Valley IT corridor.
The term is used for two different things:
In computer science, in-memory processing (PIM) is a computer architecture in which data operations are available directly on the data memory, rather than having to be transferred to CPU registers first. This may improve the power usage and performance of moving data between the processor and the main memory.
In software engineering, in-memory processing is a software architecture where a database is kept entirely in random-access memory (RAM) or flash memory so that usual accesses, in particular read or query operations, do not require access to disk storage. This may allow faster data operations such as "joins", and faster reporting and decision-making in business.
An array database management system or array DBMS provides database services specifically for arrays, that is: homogeneous collections of data items, sitting on a regular grid of one, two, or more dimensions. Often arrays are used to represent sensor, simulation, image, or statistics data. Such arrays tend to be Big Data, with single objects frequently ranging into Terabyte and soon Petabyte sizes; for example, today's earth and space observation archives typically grow by Terabytes a day. Array databases aim at offering flexible, scalable storage and retrieval on this information category.
Cubes is a light-weight open source multidimensional modelling and OLAP toolkit for development reporting applications and browsing of aggregated data written in Python programming language released under the MIT License.
The functional database model is used to support analytics applications such as financial planning and performance management. The functional database model, or the functional model for short, is different from but complementary to the relational model. The functional model is also distinct from other similarly named concepts, including the DAPLEX functional database model and functional language databases.
This page is based on this Wikipedia article Text is available under the CC BY-SA 4.0 license; additional terms may apply. Images, videos and audio are available under their respective licenses.
