Oracle choices for Multidimensional Analysis

Here’s a quick assessment highlighting the main differences between Hyperion Essbase vs Oracle OLAP. Knowing that both products are now under the same ownership, I thought this should be archived before it gets completely outdated!

	Oracle Essbase	Oracle OLAP
What-is?	Standalone OLAP Server: separate to the Oracle database End-user focused, popular among business users as data access done via Excel Fully multidimensional OLAP engine with support of MDX and XML/A (rather than SQL)	Oracle OLAP option: in-database solution available in Oracle Enterprise (latest version 11g) Aggregation management solution for SQL-based BI applications Alternative to table-based materialized views solution, offering better query perf and fast incremental load A “real” multidimensional OLAP server paradigm and not a simple SQL relational hack
Underlying Technology	Storage Used: Server MOLAP proprietary storage Concept Used : Full OLAP capabilities leveraging business rules and names, with Dimensions, facts, hierarchies outlines, consolidation rules, write-back, annotate, calculated metric, etc Data Access : MDX access exposing full OLAP capabilities model, access API choices between Java and XML/A, Essbase query tools.	Storage Used: multidimensional arrays in DB Concept Used : Structured around business rules and naming, with Dimensions, facts, hierarchies, aggregation rules etc Data Access : Fast data access through “cells” (direct address) storing either details and summary data but leveraging SQL access mode OLAP Engine : advanced analytics like time series analysis, non-additive calculations, financial and statistical models
Key Benefits	Ø Renown and proven technology with important customer base and long history background Ø Use to power many of the Hyperion performance management applications	Ø Oracle Backend-Only Solution, no need for: a. external metadata b. exporting data c. separate server d. other backend application Ø Easier than Materialized View Solution, a. Avoid the complexity of choosing which MV to create and how many possibly creatable b. All summaries stored in a single OLAP multidimensional Cube c. CBO treats OLAP cubes as MVs: rewrites queries to access cubes transparently; refresh using MVs standard Ø Easy access to detail transaction as stored directly in Cube (transparently)
Drawbacks	Standalone application requiring: 1. Dedicated server hardware 2. External metadata, hence business rules, def.. duplications 3. Extraction and import of DWH data Currently, not fully integrated within Oracle infrastructure	Does not offer a full compliant multi-dimensional access to OLAP data : 1. No MDX, XML/A 2. No real OLAP-aware query, only support SQL language (olap extension) 3. No best suited for planning & forecasting tools

Martin