Introduction to Oracle OLAP

Where Oracle OLAP stands today

• Deprecated, with an end date. Analytic workspaces, the OLAP DML language, financial reporting and the OLAP Java API are all listed as deprecated in the OLAP User's Guide, and support ends with Premier Support for the current long-term release.
• Licensing. The licensing manual still lists OLAP as an extra-cost option for Enterprise Edition. It is not available on Standard Edition 2. Oracle has stated that the option was removed from its technology price list on June 1, 2024, so treat it as something existing customers keep supported rather than something you buy new. Confirm your own position with your Oracle contract.
• No new investment. The surrounding tooling (AWM, the Excel/MDX connectors from third parties, the OBIEE integration the option was once marketed with) has not kept pace with current releases.

How to check whether a database uses OLAP

Run these as a DBA. The first shows whether the option is installed, the second whether anyone has built anything with it.

SELECT comp_id, comp_name, version, status
FROM   dba_registry
WHERE  comp_id IN ('APS', 'XOQ');

APS is the OLAP Analytic Workspace component and XOQ is the OLAP API. No rows means the option is not installed.

SELECT owner, aw_name
FROM   dba_aws
WHERE  owner NOT IN ('SYS');

SELECT owner, cube_name
FROM   dba_cubes;

User-owned rows here mean real cubes exist and something may depend on them. Installed-but-unused is common, because older database templates installed OLAP by default. Usage of an extra-cost option matters for licence audits, so also check DBA_FEATURE_USAGE_STATISTICS for OLAP entries.

The replacement: analytic views

Analytic views arrived in Oracle Database 12c Release 2. They give you the same dimensional model (hierarchies, levels, aggregation rules, calculated measures) as a metadata layer over ordinary tables. Nothing is copied and there is no build step, so results always reflect the current table data.

You create three kinds of object:

1. An attribute dimension maps a dimension table's columns to attributes and levels.
2. A hierarchy orders those levels.
3. An analytic view joins a fact table to the dimensions and declares measures.

The example below uses a two-table star schema.

CREATE TABLE time_dim (
  month_id   VARCHAR2(7)  PRIMARY KEY,   -- '2026-01'
  month_name VARCHAR2(20) NOT NULL,
  year_id    VARCHAR2(4)  NOT NULL
);

CREATE TABLE sales_fact (
  month_id VARCHAR2(7) NOT NULL REFERENCES time_dim (month_id),
  amount   NUMBER(12,2) NOT NULL
);

INSERT INTO time_dim VALUES ('2025-12', 'December 2025', '2025');
INSERT INTO time_dim VALUES ('2026-01', 'January 2026',  '2026');
INSERT INTO time_dim VALUES ('2026-02', 'February 2026', '2026');
INSERT INTO sales_fact VALUES ('2025-12', 900);
INSERT INTO sales_fact VALUES ('2026-01', 1000);
INSERT INTO sales_fact VALUES ('2026-01', 250);
INSERT INTO sales_fact VALUES ('2026-02', 1500);
COMMIT;

CREATE OR REPLACE ATTRIBUTE DIMENSION time_attr_dim
USING time_dim
ATTRIBUTES (year_id, month_id, month_name)
LEVEL month
  KEY month_id
  MEMBER NAME month_name
  ORDER BY month_id
  DETERMINES (year_id)
LEVEL year
  KEY year_id
  MEMBER NAME year_id
  ORDER BY year_id;

CREATE OR REPLACE HIERARCHY time_hier
USING time_attr_dim
  (month CHILD OF year);

CREATE OR REPLACE ANALYTIC VIEW sales_av
USING sales_fact
DIMENSION BY
  (time_attr_dim
     KEY month_id REFERENCES month_id
     HIERARCHIES (time_hier DEFAULT))
MEASURES
  (sales FACT amount,
   sales_prior_period AS (LAG(sales) OVER (HIERARCHY time_hier OFFSET 1)))
DEFAULT MEASURE sales;

Query it without GROUP BY or joins. You pick the hierarchy and filter on the level you want:

SELECT time_hier.member_name AS period,
       sales,
       sales_prior_period
FROM   sales_av HIERARCHIES (time_hier)
WHERE  time_hier.level_name = 'MONTH'
ORDER  BY time_hier.hier_order;

PERIOD            SALES  SALES_PRIOR_PERIOD
--------------- ------- -------------------
December 2025       900
January 2026       1250                 900
February 2026      1500                1250

Change the filter to 'YEAR' and the same view returns yearly totals. 'ALL' returns the grand total.

To create these objects in your own schema you need the CREATE ATTRIBUTE DIMENSION, CREATE HIERARCHY and CREATE ANALYTIC VIEW system privileges, plus read access to the underlying tables.

OLAP cubes compared with analytic views

Performance comes from different places. A cube answers from stored aggregates. An analytic view generates SQL against the base tables, so it is as fast as those tables allow; Oracle's guidance is that with columnar storage (Database In-Memory, or Exadata/Autonomous columnar formats) query performance is similar to the OLAP option. On large row-store fact tables, add materialized views or aggregate tables for the common levels.

Migration notes

• Dimensional reporting cubes map well: each cube dimension becomes an attribute dimension plus hierarchies, each stored measure becomes a FACT measure, and most calculated measures (share, lead/lag, period-to-date, rank) have direct analytic view expressions.
• Custom OLAP DML programs, forecasting and allocation models have no analytic view equivalent. Oracle points these workloads to Oracle Essbase.
• The source tables are your starting point. If the star schema that fed the cube still exists, build the analytic views on it and compare results with the cube views before switching reports over.
• Removing the option after migration is a separate DBA task with its own procedure; uninstalling APS and XOQ from a database that still has analytic workspaces will break them. Drop or export the workspaces first.

Common points of confusion

• Analytic views are not analytic functions. SUM(...) OVER (...) window functions are a core SQL feature unrelated to either OLAP or analytic views.
• Materialized views do not need OLAP. Ordinary materialized views with query rewrite are part of Enterprise Edition and are not affected by this deprecation. Only cube-organised materialized views depend on the OLAP option.
• "OLAP" in general is not going away. Only the Oracle OLAP option is deprecated. Dimensional analysis in Oracle now happens through analytic views, in-memory column store and SQL.

Further reading

• Oracle OLAP User's Guide: Deprecated Features
• Data Warehousing Guide: Overview of Analytic Views
• Database Licensing Information User Manual

What is New in Oracle’s Eighth Generation SPARC Systems

In this article, we will see what the newly launched Eighth Generation of Oracle SPARC platform has to offer.