Introduction

At the time of the Oracle 21c release, Oracle announced the deprecation of Oracle OLAP. No new features would be added to Oracle OLAP in future releases of the Oracle Database.  As Oracle 23ai is released, Oracle is announcing that support for the OLAP Option will end with Oracle 23ai Premier Support.

Oracle OLAP was removed from the Technology Price List on June 1, 2024. However, existing customers can continue to receive support by renewing support contracts.

Oracle recommends that customers do not begin developing or deploying new applications using Oracle OLAP and that they begin planning to move existing applications to alternative technologies now. This blog post outlines OLAP Option use cases and recommends migration paths for each.

If you have not read my post The Evolution of Online Analytic Processing in the Oracle Database, now would be a good time to do so. It provides a historical perspective on OLAP in the Oracle Database, industry trends, and our current and future approach.

OLAP Use Cases

Applications using the OLAP Option to the Oracle Database can be categorized as either dimensional query and reporting (slice and dice) or budgeting, planning, financial consolidation, and other applications with complex dimensional and hierarchical calculation models and “what-if” scenarios.

The primary requirements for dimensional query and reporting typically include a dimensional semantic model, calculation expressions that leverage the dimensional model (for example, time series and hierarchical expressions), high-performance query, and data set scalability.

Applications in budgeting, forecasting, and financial reporting require a dimensional model, support for unique aggregation and calculation methods (for example, performing allocations or creating a financial chart of accounts), and a transaction model that supports scenario planning and sensitivity analysis.

Migration Paths

Oracle believes that Oracle analytic views best serve the dimensional query and reporting use case and that Oracle Essbase best serves budgeting, planning, and financial applications.

Oracle analytic views are a free feature of every edition of the Oracle Database, providing a virtual dimensional semantic layer over tables. Oracle Essbase is the best-of-breed multidimensional database, providing a multidimensional semantic model using the multidimensional cube, calculation model language, and APIs.

Dimensional Reporting with Oracle Analytic Views

Analytic views provide a dimensional semantic model similar to the Oracle OLAP model.  They also support unique time series and hierarchical calculation expressions. Like OLAP analytic workspaces, analytic views can be queried with SQL. Analytic views support hierarchical query semantics and standard GROUP BY relational queries (OLAP does not support GROUP BY style queries).

There are some critical differences between OLAP analytic workspaces and analytic views. In an OLAP analytic workspace, data are loaded into a multidimensional data structure. This multidimensional data structure is optimized for fast query performance, but loading new data or modifying dimensions and hierarchies may take time, and scalability is limited.

In contrast, analytic views are (as the name suggests) a virtual semantic model layered over database tables and views. The data is not stored in the analytic view but in the underlying relational tables. Query performance for analytic views is optimized by caching hierarchical data and relying upon columnar storage such as In-Memory Column Store and Exadata Hybrid Columnar Compression. Because analytic views do not store data, it is simple and fast to maintain the dimensional model by, for example, adding new levels to a hierarchy. 

While analytic views can be layered over any table or view, Oracle recommends using column store tables with analytic views for the best query performance.  Analytic views using column store tables can provide high-performance queries like OLAP but without the scalability limitations of the analytic workspace cubes, mainly when combined with materialized caches.

Column store tables are available on Oracle Exadata using Hybrid Columnar Compression (the default storage format on the Oracle Autonomous Data Warehouse) and with the Oracle Database In-Memory Column Store.  Oracle is aggressively advancing the performance and scalability of column store tables, making them clear the path now and in the future.  (See New Database In-Memory Features in Oracle Database 23ai and Hybrid Columnar Compression on Exadata.)

Oracle Analytic Cloud and Oracle Business Intelligence (OAC) are tightly integrated with Oracle analytic views. OAC automatically imports the analytic view semantic model from the Oracle Database, creating a ready-to-use data set. Analytic views can also be imported into OAC repositories. In both cases, OAC queries analytic views with hierarchical-style SQL, taking advantage of the advanced features of the analytic view.

Application developers will find analytic views an ideal data platform. Analytic views provide a content-rich model, presentation metadata, and support for multilingual applications and are very easy to query. Application developers can embed calculations into the analytic view and express them dynamically within queries (OLAP does not support query-defined calculations).

Application developers should consider using the Oracle APEX low-code development environment with analytic views for an all in-database solution. To learn more, see my post, Easily Build an Analytic Application using APEX and Analytic Views.

Budgeting, Planning, and What-if Analysis with Essbase

Oracle recommends migrating to Oracle Essbase for applications that require support for advanced OLAP calculations, such as non-additive aggregation methods, calculation models, and hierarchical expressions. It also has built-in financial and time intelligence for management reporting applications.

Oracle Essbase supports a multidimensional semantic model. The Essbase application defines an outline that includes business hierarchies, complex calculations, and formulas replicating many of the calculations supported by OLAP hierarchies and MODELs. Essbase supports parent-child hierarchies, non-additive aggregation methods, and inter-row/inter-member calculations that are necessary to solve, such as a chart of accounts.

The OLAP and Essbase models are similar in many ways. For example, each includes hierarchies, levels, members, attributes, and stored and calculated measures. However, there are also many differences, so you will want to begin your migration early to understand where to make changes and what new features Essbase offers.

Essbase includes SmartView, a tool that seamlessly integrates Essbase data into Microsoft Excel. SmartView supports real-time data connectivity, facilitating dynamic reporting, ad-hoc analysis, and drill-down capabilities. Its intuitive interface and robust data management capabilities empower users to make informed decisions by combining Excel’s flexibility with Essbase’s powerful analytical features.

Users of the Oracle Autonomous Database can also consider using Essbase federated partitions. Federated partitions support a hybrid approach where data is stored in relational tables in an Autonomous Database and accessed using Essbase. They allow the application to use the Essbase dimensional model and calculation capabilities while storing data in the relational database.  Federated partitions are particularly useful for Oracle OLAP customers who want to continue to keep their within an Oracle Database. 

Summary

Now is the time for Oracle OLAP users to begin planning migrations to new data platforms.

Oracle recommends Oracle analytic views for dimensional query and reporting applications. Analytic views offer a dimensional semantic model akin to Oracle OLAP, supporting time series, hierarchical calculations, and SQL queries. They function as a virtual layer over tabular data sources, ensuring high performance and scalability.

Oracle recommends migrating to Oracle Essbase for more advanced OLAP calculations required by budgeting, planning and management reporting applications. Essbase supports a robust multidimensional model and advanced calculations. It provides block and advanced storage options, catering to various application needs, and supports federated partitions for hybrid data storage approaches in the Oracle Autonomous Database.

Each migration path will present challenges and many new opportunities.  By getting started now, you can make a slow, considered change.