Hubbry Logo
Online analytical processingOnline analytical processingMain
Open search
Online analytical processing
Community hub
Online analytical processing
logo
7 pages, 0 posts
0 subscribers
Be the first to start a discussion here.
Be the first to start a discussion here.
Online analytical processing
Online analytical processing
Online analytical processing
View on Wikipedia
from Wikipedia

In computing, online analytical processing (OLAP) (/ˈoʊlæp/), is an approach to quickly answer multi-dimensional analytical (MDA) queries.[1] The term OLAP was created as a slight modification of the traditional database term online transaction processing (OLTP).[2] OLAP is part of the broader category of business intelligence, which also encompasses relational databases, report writing and data mining.[3] Typical applications of OLAP include business reporting for sales, marketing, management reporting, business process management (BPM),[4] budgeting and forecasting, financial reporting and similar areas, with new applications emerging, such as agriculture.[5]

OLAP tools enable users to analyse multidimensional data interactively from multiple perspectives. OLAP consists of three basic analytical operations: consolidation (roll-up), drill-down, and slicing and dicing.[6]: 402–403  Consolidation involves the aggregation of data that can be accumulated and computed in one or more dimensions. For example, all sales offices are rolled up to the sales department or sales division to anticipate sales trends. By contrast, the drill-down is a technique that allows users to navigate through the details. For instance, users can view the sales by individual products that make up a region's sales. Slicing and dicing is a feature whereby users can take out (slicing) a specific set of data of the OLAP cube and view (dicing) the slices from different viewpoints. These viewpoints are sometimes called dimensions (such as looking at the same sales by salesperson, or by date, or by customer, or by product, or by region, etc.).

Databases configured for OLAP use a multidimensional data model, allowing for complex analytical and ad hoc queries with a rapid execution time.[7] They borrow aspects of navigational databases, hierarchical databases and relational databases.

OLAP is typically contrasted to OLTP (online transaction processing), which is generally characterized by much less complex queries, in a larger volume, to process transactions rather than for the purpose of business intelligence or reporting. Whereas OLAP systems are mostly optimized for read, OLTP has to process all kinds of queries (read, insert, update and delete).

Overview of OLAP systems

[edit]

At the core of any OLAP system is an OLAP cube (also called a 'multidimensional cube' or a hypercube). It consists of numeric facts called measures that are categorized by dimensions. The measures are placed at the intersections of the hypercube, which is spanned by the dimensions as a vector space. The usual interface to manipulate an OLAP cube is a matrix interface, like Pivot tables in a spreadsheet program, which performs projection operations along the dimensions, such as aggregation or averaging.

The cube metadata is typically created from a star schema or snowflake schema or fact constellation of tables in a relational database. Measures are derived from the records in the fact table and dimensions are derived from the dimension tables.

Each measure can be thought of as having a set of labels, or meta-data associated with it. A dimension is what describes these labels; it provides information about the measure.

A simple example would be a cube that contains a store's sales as a measure, and Date/Time as a dimension. Each Sale has a Date/Time label that describes more about that sale.

For example: 

 Sales Fact Table
+-------------+----------+
| sale_amount | time_id  |
+-------------+----------+            Time Dimension
|       930.10|     1234 |----+     +---------+-------------------+
+-------------+----------+    |     | time_id | timestamp         |
                              |     +---------+-------------------+
                              +---->|   1234  | 20080902 12:35:43 |
                                    +---------+-------------------+

Multidimensional databases

[edit]

Multidimensional structure is defined as "a variation of the relational model that uses multidimensional structures to organize data and express the relationships between data".[6]: 177  The structure is broken into cubes and the cubes are able to store and access data within the confines of each cube. "Each cell within a multidimensional structure contains aggregated data related to elements along each of its dimensions".[6]: 178  Even when data is manipulated it remains easy to access and continues to constitute a compact database format. The data still remains interrelated. Multidimensional structure is quite popular for analytical databases that use online analytical processing (OLAP) applications.[6] Analytical databases use these databases because of their ability to deliver answers to complex business queries swiftly. Data can be viewed from different angles, which gives a broader perspective of a problem unlike other models.[8]

Aggregations

[edit]

It has been claimed that for complex queries OLAP cubes can produce an answer in around 0.1% of the time required for the same query on OLTP relational data.[9][10] The most important mechanism in OLAP which allows it to achieve such performance is the use of aggregations. Aggregations are built from the fact table by changing the granularity on specific dimensions and aggregating up data along these dimensions, using an aggregate function (or aggregation function). The number of possible aggregations is determined by every possible combination of dimension granularities.

The combination of all possible aggregations and the base data contains the answers to every query which can be answered from the data.[11]

Because usually there are many aggregations that can be calculated, often only a predetermined number are fully calculated; the remainder are solved on demand. The problem of deciding which aggregations (views) to calculate is known as the view selection problem. View selection can be constrained by the total size of the selected set of aggregations, the time to update them from changes in the base data, or both. The objective of view selection is typically to minimize the average time to answer OLAP queries, although some studies also minimize the update time. View selection is NP-complete. Many approaches to the problem have been explored, including greedy algorithms, randomized search, genetic algorithms and A* search algorithm.

Some aggregation functions can be computed for the entire OLAP cube by precomputing values for each cell, and then computing the aggregation for a roll-up of cells by aggregating these aggregates, applying a divide and conquer algorithm to the multidimensional problem to compute them efficiently.[12] For example, the overall sum of a roll-up is just the sum of the sub-sums in each cell. Functions that can be decomposed in this way are called decomposable aggregation functions, and include COUNT, MAX, MIN, and SUM, which can be computed for each cell and then directly aggregated; these are known as self-decomposable aggregation functions.[13]

In other cases, the aggregate function can be computed by computing auxiliary numbers for cells, aggregating these auxiliary numbers, and finally computing the overall number at the end; examples include AVERAGE (tracking sum and count, dividing at the end) and RANGE (tracking max and min, subtracting at the end). In other cases, the aggregate function cannot be computed without analyzing the entire set at once, though in some cases approximations can be computed; examples include DISTINCT COUNT, MEDIAN, and MODE; for example, the median of a set is not the median of medians of subsets. These latter are difficult to implement efficiently in OLAP, as they require computing the aggregate function on the base data, either computing them online (slow) or precomputing them for possible rollouts (large space).

Types

[edit]

OLAP systems have been traditionally categorized using the following taxonomy.[14]

Multidimensional OLAP (MOLAP)

[edit]

MOLAP (multi-dimensional online analytical processing) is the classic form of OLAP and is sometimes referred to as just OLAP. MOLAP stores this data in an optimized multi-dimensional array storage, rather than in a relational database.

Some MOLAP tools require the pre-computation and storage of derived data, such as consolidations – the operation known as processing. Such MOLAP tools generally utilize a pre-calculated data set referred to as a data cube. The data cube contains all the possible answers to a given range of questions. As a result, they have a very fast response to queries. On the other hand, updating can take a long time depending on the degree of pre-computation. Pre-computation can also lead to what is known as data explosion.

Other MOLAP tools, particularly those that implement the functional database model do not pre-compute derived data but make all calculations on demand other than those that were previously requested and stored in a cache.

Advantages of MOLAP

  • Fast query performance due to optimized storage, multidimensional indexing and caching.
  • Smaller on-disk size of data compared to data stored in relational database due to compression techniques.
  • Automated computation of higher-level aggregates of the data.
  • It is very compact for low dimension data sets.
  • Array models provide natural indexing.
  • Effective data extraction achieved through the pre-structuring of aggregated data.

Disadvantages of MOLAP

  • Within some MOLAP systems the processing step (data load) can be quite lengthy, especially on large data volumes. This is usually remedied by doing only incremental processing, i.e., processing only the data which have changed (usually new data) instead of reprocessing the entire data set.
  • Some MOLAP methodologies introduce data redundancy.

Products

[edit]

Examples of commercial products that use MOLAP are Cognos Powerplay, Oracle Database OLAP Option, MicroStrategy, Microsoft Analysis Services, Essbase, TM1, Jedox, and icCube.

Relational OLAP (ROLAP)

[edit]

ROLAP works directly with relational databases and does not require pre-computation. The base data and the dimension tables are stored as relational tables and new tables are created to hold the aggregated information. It depends on a specialized schema design. This methodology relies on manipulating the data stored in the relational database to give the appearance of traditional OLAP's slicing and dicing functionality. In essence, each action of slicing and dicing is equivalent to adding a "WHERE" clause in the SQL statement. ROLAP tools do not use pre-calculated data cubes but instead pose the query to the standard relational database and its tables in order to bring back the data required to answer the question. ROLAP tools feature the ability to ask any question because the methodology is not limited to the contents of a cube. ROLAP also has the ability to drill down to the lowest level of detail in the database.

While ROLAP uses a relational database source, generally the database must be carefully designed for ROLAP use. A database which was designed for OLTP will not function well as a ROLAP database. Therefore, ROLAP still involves creating an additional copy of the data. However, since it is a database, a variety of technologies can be used to populate the database.

Advantages of ROLAP

[edit]
  • ROLAP is considered to be more scalable in handling large data volumes, especially models with dimensions with very high cardinality (i.e., millions of members).
  • With a variety of data loading tools available, and the ability to fine-tune the extract, transform, load (ETL) code to the particular data model, load times are generally much shorter than with the automated MOLAP loads.
  • The data are stored in a standard relational database and can be accessed by any SQL reporting tool (the tool does not have to be an OLAP tool).
  • ROLAP tools are better at handling non-aggregable facts (e.g., textual descriptions). MOLAP tools tend to suffer from slow performance when querying these elements.
  • By decoupling the data storage from the multi-dimensional model, it is possible to successfully model data that would not otherwise fit into a strict dimensional model.
  • The ROLAP approach can leverage database authorization controls such as row-level security, whereby the query results are filtered depending on preset criteria applied, for example, to a given user or group of users (SQL WHERE clause).

Disadvantages of ROLAP

[edit]
  • There is a consensus in the industry that ROLAP tools have slower performance than MOLAP tools. However, see the discussion below about ROLAP performance.
  • The loading of aggregate tables must be managed by custom ETL code. The ROLAP tools do not help with this task. This means additional development time and more code to support.
  • When the step of creating aggregate tables is skipped, the query performance then suffers because the larger detailed tables must be queried. This can be partially remedied by adding additional aggregate tables; however it is still not practical to create aggregate tables for all combinations of dimensions/attributes.
  • ROLAP relies on the general-purpose database for querying and caching, and therefore several special techniques employed by MOLAP tools are not available (such as special hierarchical indexing). However, modern ROLAP tools take advantage of latest improvements in SQL language such as CUBE and ROLLUP operators, DB2 Cube Views, as well as other SQL OLAP extensions. These SQL improvements can mitigate the benefits of the MOLAP tools.
  • Since ROLAP tools rely on SQL for all of the computations, they are not suitable when the model is heavy on calculations which don't translate well into SQL. Examples of such models include budgeting, allocations, financial reporting and other scenarios.

Performance of ROLAP

[edit]

In the OLAP industry ROLAP is usually perceived as being able to scale for large data volumes but suffering from slower query performance as opposed to MOLAP. The OLAP Survey, the largest independent survey across all major OLAP products, being conducted for 6 years (2001 to 2006) have consistently found that companies using ROLAP report slower performance than those using MOLAP even when data volumes were taken into consideration.

However, as with any survey there are a number of subtle issues that must be taken into account when interpreting the results.

  • The survey shows that ROLAP tools have 7 times more users than MOLAP tools within each company. Systems with more users will tend to suffer more performance problems at peak usage times.
  • There is also a question about complexity of the model, measured both in number of dimensions and richness of calculations. The survey does not offer a good way to control for these variations in the data being analyzed.

Downside of flexibility

[edit]

Some companies select ROLAP because they intend to re-use existing relational database tables—these tables will frequently not be optimally designed for OLAP use. The superior flexibility of ROLAP tools allows this less-than-optimal design to work, but performance suffers. MOLAP tools in contrast would force the data to be re-loaded into an optimal OLAP design.

Hybrid OLAP (HOLAP)

[edit]

The undesirable trade-off between additional ETL cost and slow query performance has ensured that most commercial OLAP tools now use a "Hybrid OLAP" (HOLAP) approach, which allows the model designer to decide which portion of the data will be stored in MOLAP and which portion in ROLAP.

There is no clear agreement across the industry as to what constitutes "Hybrid OLAP", except that a database will divide data between relational and specialized storage.[15] For example, for some vendors, a HOLAP database will use relational tables to hold the larger quantities of detailed data and use specialized storage for at least some aspects of the smaller quantities of more-aggregate or less-detailed data. HOLAP addresses the shortcomings of MOLAP and ROLAP by combining the capabilities of both approaches. HOLAP tools can utilize both pre-calculated cubes and relational data sources.

Vertical partitioning

[edit]

In this mode HOLAP stores aggregations in MOLAP for fast query performance, and detailed data in ROLAP to optimize time of cube processing.

Horizontal partitioning

[edit]

In this mode HOLAP stores some slice of data, usually the more recent one (i.e. sliced by Time dimension) in MOLAP for fast query performance, and older data in ROLAP. Moreover, we can store some dices in MOLAP and others in ROLAP, leveraging the fact that in a large cuboid, there will be dense and sparse subregions.[16]

Products

[edit]

The first product to provide HOLAP storage was Holos, but the technology also became available in other commercial products such as Microsoft Analysis Services, Oracle Database OLAP Option, MicroStrategy and SAP AG BI Accelerator. The hybrid OLAP approach combines ROLAP and MOLAP technology, benefiting from the greater scalability of ROLAP and the faster computation of MOLAP. For example, a HOLAP server may store large volumes of detailed data in a relational database, while aggregations are kept in a separate MOLAP store. The Microsoft SQL Server 7.0 OLAP Services supports a hybrid OLAP server

Comparison

[edit]

Each type has certain benefits, although there is disagreement about the specifics of the benefits between providers.

  • Some MOLAP implementations are prone to database explosion, a phenomenon causing vast amounts of storage space to be used by MOLAP databases when certain common conditions are met: high number of dimensions, pre-calculated results and sparse multidimensional data.
  • MOLAP generally delivers better performance due to specialized indexing and storage optimizations. MOLAP also needs less storage space compared to ROLAP because the specialized storage typically includes compression techniques.[15]
  • ROLAP is generally more scalable.[15] However, large volume pre-processing is difficult to implement efficiently so it is frequently skipped. ROLAP query performance can therefore suffer tremendously.
  • Since ROLAP relies more on the database to perform calculations, it has more limitations in the specialized functions it can use.
  • HOLAP attempts to mix the best of ROLAP and MOLAP. It can generally pre-process swiftly, scale well, and offer good function support.

Other types

[edit]

The following acronyms are also sometimes used, although they are not as widespread as the ones above:

  • WOLAP – Web-based OLAP
  • DOLAP – Desktop OLAP
  • RTOLAP – Real-time OLAP
  • GOLAP – Graph OLAP[17][18]
  • CaseOLAP – Context-aware Semantic OLAP,[19] developed for biomedical applications.[20] The CaseOLAP platform includes data preprocessing (e.g., downloading, extraction, and parsing text documents), indexing and searching with Elasticsearch, creating a functional document structure called Text-Cube,[21][22][23][24][25] and quantifying user-defined phrase-category relationships using the core CaseOLAP algorithm.

APIs and query languages

[edit]

Unlike relational databases, which had SQL as the standard query language, and widespread APIs such as ODBC, JDBC and OLEDB, there was no such unification in the OLAP world for a long time. The first real standard API was OLE DB for OLAP specification from Microsoft which appeared in 1997 and introduced the MDX query language. Several OLAP vendors – both server and client – adopted it. In 2001 Microsoft and Hyperion announced the XML for Analysis specification, which was endorsed by most of the OLAP vendors. Since this also used MDX as a query language, MDX became the de facto standard.[26] Since September-2011 LINQ can be used to query SSAS OLAP cubes from Microsoft .NET.[27]

Products

[edit]

History

[edit]

The first product that performed OLAP queries was Express, which was released in 1970 (and acquired by Oracle in 1995 from Information Resources).[28] However, the term did not appear until 1993 when it was coined by Edgar F. Codd, who has been described as "the father of the relational database". Codd's paper[1] resulted from a short consulting assignment which Codd undertook for former Arbor Software (later Hyperion Solutions, and in 2007 acquired by Oracle), as a sort of marketing coup.

The company had released its own OLAP product, Essbase, a year earlier. As a result, Codd's "twelve laws of online analytical processing" were explicit in their reference to Essbase. There was some ensuing controversy and when Computerworld learned that Codd was paid by Arbor, it retracted the article. The OLAP market experienced strong growth in the late 1990s with dozens of commercial products going into market. In 1998, Microsoft released its first OLAP Server – Microsoft Analysis Services, which drove wide adoption of OLAP technology and moved it into the mainstream.

Product comparison

[edit]
Main article: Comparison of OLAP servers

OLAP clients

[edit]

OLAP clients include many spreadsheet programs like Excel, web application, SQL, dashboard tools, etc. Many clients support interactive data exploration where users select dimensions and measures of interest. Some dimensions are used as filters (for slicing and dicing the data) while others are selected as the axes of a pivot table or pivot chart. Users can also vary aggregation level (for drilling-down or rolling-up) the displayed view. Clients can also offer a variety of graphical widgets such as sliders, geographic maps, heat maps and more which can be grouped and coordinated as dashboards. An extensive list of clients appears in the visualization column of the comparison of OLAP servers table.

Market structure

[edit]

Below is a list of top OLAP vendors in 2006, with figures in millions of US Dollars.[29]

Vendor Global Revenue Consolidated company
Microsoft Corporation 1,806 Microsoft
Hyperion Solutions Corporation 1,077 Oracle
Cognos 735 IBM
Business Objects 416 SAP
MicroStrategy 416 MicroStrategy
SAP AG 330 SAP
Cartesis (SAP) 210 SAP
Applix 205 IBM
Infor 199 Infor
Oracle Corporation 159 Oracle
Others 152 Others
Total 5,700

Open source

[edit]
  • Apache Pinot is used at LinkedIn, Cisco, Uber, Slack, Stripe, DoorDash, Target, Walmart, Amazon, and Microsoft to deliver scalable real time analytics with low latency.[30] It can ingest data from offline data sources (such as Hadoop and flat files) as well as online sources (such as Kafka). Pinot is designed to scale horizontally.
  • Mondrian OLAP server is an open-source OLAP server written in Java. It supports the MDX query language, the XML for Analysis and the olap4j interface specifications.
  • Apache Doris is an open-source real-time analytical database based on MPP architecture. It can support both high-concurrency point query scenarios and high-throughput complex analysis.[31]
  • Apache Druid is a popular open-source distributed data store for OLAP queries that is used at scale in production by various organizations.
  • Apache Kylin is a distributed data store for OLAP queries originally developed by eBay.
  • Cubes (OLAP server) is another lightweight open-source toolkit implementation of OLAP functionality in the Python programming language with built-in ROLAP.
  • ClickHouse is a fairly new column-oriented DBMS focusing on fast processing and response times.
  • DuckDB[32] is an in-process SQL OLAP[33] database management system.
  • MonetDB is a mature open-source column-oriented SQL RDBMS designed for OLAP queries.

See also

[edit]

References

[edit]

Further reading

[edit]
Revisions and contributorsEdit on WikipediaRead on Wikipedia

Online analytical processing

View on Grokipedia
from Grokipedia
Online analytical processing (OLAP) is a approach designed to enable rapid, interactive analysis of multidimensional data from data warehouses, supporting complex queries and decision-making by presenting information in hierarchical, cube-like structures. The term OLAP was coined in 1993 by , the inventor of the , in a that outlined its role in providing user-analysts with tools for synthesizing and consolidating large volumes of data. Codd proposed 12 rules (or guidelines) for OLAP systems to ensure they meet analytical needs, including support for multidimensional views, transparency to data sources, consistent performance, and unrestricted cross-dimensional operations. At its core, OLAP organizes into dimensions (e.g., time, product, location) and measures (e.g., sales figures), forming multidimensional that facilitate operations such as slicing (selecting a single dimension subset), (extracting a smaller ), (increasing detail), rolling up (summarizing), and pivoting (rotating views). These features allow users to explore intuitively, often meeting the FASMI : fast analysis of shared multidimensional information. Unlike , which handles real-time, operational transactions on normalized, current with frequent reads and writes, OLAP focuses on read-intensive queries over historical, denormalized, and aggregated for strategic insights, typically managing terabyte-scale volumes.

Fundamentals

Definition and Purpose

Online analytical processing (OLAP) is a technology designed to enable the rapid, interactive examination of large volumes of data organized in multiple dimensions, allowing users to gain insights from various analytical perspectives. Coined by in 1993, OLAP emphasizes multidimensional views of aggregated data to facilitate complex querying beyond traditional operations. The core purpose of OLAP is to empower processes, including trend identification, , and informed decision-making, by supporting exploration of datasets that assume familiarity with basic database concepts like tables and queries. It achieves this through key operations such as slicing (extracting data along a single , e.g., for a specific year), dicing (defining a sub-cube with ranges across dimensions), drilling down (adding finer , like from quarterly to monthly ), drilling up (aggregating to higher levels, such as from products to categories), and pivoting (rotating axes to view data differently, like swapping rows and columns for region versus ). These capabilities address the need for flexible, on-the-fly analytics in environments where predefined reports fall short. In contrast to (OLTP), which manages numerous short, update-oriented transactions for day-to-day operations like recording a single purchase, OLAP prioritizes read-intensive, aggregative queries over historical and integrated data for analytical depth. For instance, an OLAP system might compute total sales revenue by geographic region, product line, and fiscal quarter to uncover patterns, whereas OLTP systems ensure the of that individual transaction entry in real time. This distinction underscores OLAP's role in strategic analysis rather than operational efficiency.

Multidimensional Data Model

The multidimensional data model forms the foundational structure for online analytical processing (OLAP), enabling the organization and of large volumes of from multiple perspectives. This model, proposed by in 1993 as the basis for OLAP systems, emphasizes multidimensional databases that support dynamic, intuitive exploration over traditional relational approaches. In this paradigm, is conceptualized as a multidimensional , where categorical attributes define the axes of , allowing users to perform complex aggregations and insights without predefined queries. Dimensions represent the categorical attributes or perspectives along which data is analyzed, such as time, geography, or product categories, forming the edges of the analytical structure. Each dimension consists of discrete values that categorize the data, enabling slicing and dicing operations to focus on specific subsets. within organize these values into leveled structures for progressive aggregation and navigation; for instance, a time dimension might include a hierarchy progressing from year to quarter to month, where higher levels (e.g., year) aggregate data from lower ones (e.g., months). This facilitates drill-down , such as examining annual sales totals before breaking them into quarterly figures. Measures, in contrast, are the quantitative facts or numerical values stored at the intersections of dimensions, such as amounts or unit quantities, which are aggregated across dimensional axes to yield analytical results. These measures form the core content of the model, with their values computed through functions like sum or , providing the basis for metrics. For example, in a sales analysis, the measure might be , varying by dimensions like product and region. The logical representation of this model is the , a multidimensional that encapsulates measures along shared dimensions, visualized as a in higher dimensions but often exemplified in three dimensions for clarity. Consider a three-dimensional cube with axes for time (e.g., months), product (e.g., categories like or apparel), and (e.g., regions like or ); each cell at the intersection holds a measure value, such as dollars for in during January, enabling rapid pivoting to view data from alternative perspectives. In relational implementations, the multidimensional model is mapped to database schemas, primarily the star and snowflake designs, to store data in tables while preserving analytical efficiency. The star schema features a central fact table containing measures and foreign keys linking to surrounding dimension tables, each holding descriptive attributes for a single dimension, promoting simplicity and query performance. The snowflake schema extends this by normalizing dimension tables into multiple related sub-tables, one per hierarchy level, to reduce redundancy but potentially increasing join complexity during queries. For instance, a product dimension in a snowflake schema might split into separate tables for categories, subcategories, and individual items.

Key Operations and Aggregations

Online analytical processing (OLAP) relies on a set of core operations that allow users to manipulate and explore multidimensional data cubes interactively. These operations enable analysts to view data from various perspectives without restructuring the underlying model. The primary operations, as defined in foundational OLAP literature, include slice, dice, drill-down, roll-up, and pivot, each facilitating different aspects of data navigation and summarization. Slice fixes one dimension to a specific value, effectively reducing the cube to a lower-dimensional slice for focused analysis; for example, selecting sales data for a single year removes the time dimension, yielding a two-dimensional view of product and region. Dice extends this by selecting sub-ranges or specific values across multiple dimensions, extracting a smaller sub-cube; this might involve querying sales for a particular quarter in specific regions and product categories. Drill-down increases granularity by descending a hierarchy within a dimension, such as moving from yearly to monthly sales data to reveal underlying trends. Conversely, roll-up (also known as drill-up) aggregates data by ascending the hierarchy, summarizing lower-level details into higher-level overviews, like consolidating monthly sales into annual totals. Pivot rotates the axes of the cube to swap dimensions, providing alternative viewpoints; for instance, transposing rows (products) and columns (time) in a sales report to emphasize temporal patterns over products. These operations collectively support ad-hoc querying, allowing seamless transitions between detailed and summarized views. Aggregations form the backbone of OLAP analysis, applying functions to measures across selected dimensions to derive insights. Common aggregation functions include sum (totaling values), average (mean across a set), count (number of non-null entries), minimum, and maximum, which compute summaries like total revenue or peak sales. For instance, total sales can be calculated as the sum over all relevant records: Total Sales=∑(quantity×price)\text{Total Sales} = \sum (\text{quantity} \times \text{price}) where the summation occurs across the selected dimensions, such as time, product, and location. To achieve interactive speeds, OLAP systems pre-compute these aggregations by materializing views—storing the results of common aggregations in advance—reducing query times from minutes to seconds on large datasets. Multidimensional cubes often exhibit high sparsity, with most cells empty due to the of dimensions (e.g., not every product sells in every region every day). OLAP implementations address this through sparse storage techniques, such as hashing only non-zero cells or using indices and B-trees, which minimize memory usage while preserving query efficiency; this dynamic handling ensures that operations like roll-up or slice perform optimally even on sparse data.

History and Evolution

Origins in the 1990s

The emergence of online analytical processing (OLAP) in the early addressed the growing demand for advanced tools amid the proliferation of business following the boom of the . Relational database management systems (RDBMS), while effective for transactional processing, struggled with the complex, ad-hoc queries required for , such as multidimensional aggregations and slicing across large datasets, due to performance bottlenecks from extensive joins and normalization. This limitation became particularly acute as enterprises accumulated vast amounts of operational , necessitating faster, more intuitive to support decision-making without disrupting (OLTP) systems. A key precursor to OLAP was the concept of data warehousing, formalized by in his 1992 book Building the Data Warehouse. Inmon advocated for a centralized repository of integrated, historical data separated from operational OLTP systems, enabling efficient querying for analytical purposes and laying the groundwork for distinguishing OLAP workloads from transactional ones. This approach highlighted the need for specialized architectures to handle read-heavy, aggregate-oriented operations on cleaned, subject-oriented data stores. The term "OLAP" was coined by in his seminal 1993 technical report, Providing OLAP to User-Analysts: An IT Mandate, co-authored with Sharon B. Codd and C. T. Salley. In this work, Codd outlined 12 rules for designing OLAP systems, emphasizing multidimensional data views, fast query performance, and user-friendly interfaces to empower non-technical analysts. These rules positioned OLAP as an evolution beyond relational models, focusing on intuitive navigation of data cubes for business reporting. Early prototypes, such as the Express multidimensional database, originally released by Information Resources, Inc. in 1975 and later acquired by in 1995, demonstrated practical implementations of these ideas, allowing developers to build OLAP applications for financial and sales analysis.

Key Milestones and Developments

In the , the integration of OLAP with data warehousing tools advanced significantly through enhanced (Extract, Transform, Load) processes, enabling more efficient data consolidation from disparate sources into multidimensional structures for analysis. Tools like and DataStage, which emerged in the late 1990s, saw widespread adoption during this decade, facilitating automated data pipelines that supported OLAP's need for clean, aggregated datasets in enterprise environments. This period also marked the standardization of the Multidimensional Expressions (MDX) , initially released by in 1998 with SQL Server 7's OLAP Services, which gained broad industry adoption in the early 2000s for complex multidimensional querying across vendors. Additionally, the XML for Analysis (XML/A) standard, introduced by Microsoft around 2002-2003 as a SOAP-based protocol, emerged as a key specification for accessing OLAP metadata and executing queries over web services, promoting interoperability between OLAP servers and client applications. The 2010s brought a shift toward and integration in OLAP systems, with becoming a cornerstone for faster query performance on large datasets. SAP HANA, launched in 2010 as an in-memory columnar database, revolutionized OLAP by enabling real-time analytics directly on transactional data, reducing latency from hours to seconds for complex aggregations. Complementing this, columnar storage innovations like Apache Kudu, released in its 1.0 version in 2016 by , addressed challenges by providing a distributed storage engine optimized for OLAP workloads within Hadoop ecosystems, supporting both analytical scans and updates on petabyte-scale data. These developments aligned OLAP more closely with scalable cloud architectures, allowing organizations to handle exponentially growing data volumes without traditional hardware constraints. In the 2020s, OLAP evolved further with emphases on real-time processing of and AI integration for automated insights. Apache Druid, originally developed in 2011 and open-sourced in 2012, matured into a prominent real-time OLAP database by the early 2020s, ingesting at high velocities while delivering sub-second query responses on event-driven datasets for applications like user behavior analysis. Cloud-native platforms such as , founded in 2012 and reaching significant maturity in the late 2010s through 2020s expansions, provided separated storage and compute for OLAP, enabling elastic scaling and near-real-time analytics on massive datasets across multi-cloud environments. Concurrently, AI enhancements in OLAP tools, such as those integrating for predictive modeling and , began proliferating around 2023, with systems like IBM's offerings combining OLAP cubes with AI to automate insight generation and improve decision-making accuracy. In 2024, announced the deprecation of its OLAP option, signaling a broader industry transition to cloud-based and real-time analytics platforms.

Types of OLAP Systems

Multidimensional OLAP (MOLAP)

Multidimensional OLAP (MOLAP) employs specialized multidimensional databases that utilize array-based storage structures to organize into multi-dimensional cubes. These cubes are built by pre-computing and storing aggregates across dimensions, such as sums or averages, which allows for rapid access to summarized without requiring real-time calculations during queries. This architecture directly implements the multidimensional in optimized storage engines tailored for analytical . A key strength of MOLAP is its support for high-speed queries on pre-aggregated , enabling efficient handling of complex analytics like multi-dimensional slicing and aggregation. By storing results of common operations in advance, MOLAP minimizes processing overhead, delivering near-instantaneous responses for interactive exploration of large datasets. MOLAP systems typically use storage formats to enhance performance in multidimensional environments. For example, Essbase's Block Storage Option (BSO) structures into blocks defined by combinations of sparse dimension members, with each block holding values from dense dimensions. Sparsity is managed through a dedicated index that records only existing sparse combinations and points to corresponding data blocks, avoiding allocation of for non-existent cells and thereby optimizing storage efficiency. MOLAP excels with dense datasets, where most cube cells are populated, as the array-based approach maximizes storage utilization and query speed in such scenarios. The fixed of these systems, which enforces predefined dimensions and measures, constrains flexibility for unstructured changes but supports sub-second response times for anticipated analytical queries on pre-built cubes.

Relational OLAP (ROLAP)

Relational OLAP (ROLAP) is an OLAP implementation that operates directly on s, extending standard relational database management systems (RDBMS) to support without dedicated multidimensional storage structures. The positions ROLAP servers as an intermediate layer between the relational back-end, where data is stored in normalized or denormalized schemas such as or schemas, and client-front-end tools for querying. This setup leverages existing RDBMS like , using middleware to translate OLAP operations into optimized SQL queries, often incorporating materialized views for performance enhancement. Unlike multidimensional approaches, ROLAP avoids proprietary storage formats, relying instead on the RDBMS's native capabilities for . A key strength of ROLAP lies in its ability to handle very large and sparse datasets, as it stores only the actual facts without padding for empty cells, thereby optimizing storage efficiency. It capitalizes on the inherent and robustness of relational systems, which are designed for high-volume transactions and can manage terabyte-scale warehouses seamlessly. Additionally, ROLAP facilitates straightforward integration with operational transactional systems, as the analytical resides within the same relational environment, enabling real-time access to up-to-date information without data duplication. The query process in ROLAP involves dynamic, on-the-fly aggregation executed through generated SQL statements against the . For instance, a roll-up operation to aggregate from daily to monthly levels might employ the SQL GROUP BY , which computes subtotals hierarchically in a single query, such as SELECT product, month, SUM([sales](/page/Data)) FROM sales_table GROUP BY ROLLUP (product, month);. Aggregations may be supported via indexed views in the RDBMS to accelerate repeated access, but complex multidimensional queries often require multi-statement SQL execution, leading to potential performance slowdowns due to real-time computation overhead.

Hybrid OLAP (HOLAP)

Hybrid OLAP (HOLAP) integrates the multidimensional storage and fast aggregation capabilities of MOLAP with the relational storage and scalability of ROLAP, enabling systems to handle both precomputed summaries and detailed data efficiently. In this architecture, the OLAP server manages the division of data between relational databases for raw or detailed information and multidimensional cubes for aggregated views, allowing transparent access to users without specifying the underlying storage type. A key aspect of HOLAP architecture is vertical partitioning, where aggregated data is stored in a MOLAP structure for rapid access to summaries, while the underlying raw or detailed data remains in a relational format akin to ROLAP. This approach avoids duplicating the entire dataset in multidimensional storage, reducing and enabling real-time updates to source data. Horizontal partitioning complements this by allocating specific data slices—such as those requiring frequent querying—to MOLAP cubes for summary-level , while storing less-accessed or detailed portions in relational tables. For instance, recent summaries might be precomputed in cubes, with historical transaction details queried directly from relations. The benefits of HOLAP include optimized storage compared to pure MOLAP, which can become unwieldy with large sets, and superior query speeds for common aggregations over ROLAP's relational joins. It is particularly effective for scenarios balancing and flexibility, such as using MOLAP partitions for frequent reporting queries on summarized and ROLAP for ad-hoc explorations of granular details. Implementations like Jedox (formerly Palo) and Mondrian OLAP server exemplify this family of HOLAP systems, where Mondrian, for example, stores aggregates multidimensionally while retaining leaf-level relationally to mitigate MOLAP's storage constraints and ROLAP's latency issues. In modern cloud environments, HOLAP has gained prominence through platforms like Azure Analysis Services, introduced in the 2010s, which support hybrid storage modes for scalable, managed OLAP deployments handling petabyte-scale data without on-premises hardware. This evolution addresses earlier limitations by leveraging cloud elasticity for partitioning strategies, ensuring and integration with services like Azure Synapse Analytics.

Comparisons and Advanced Variants

Performance and Trade-offs

A fundamental distinction in database systems is between Online Analytical Processing (OLAP), also known as Analytical Processing (AP), and Online Transactional Processing (OLTP), also known as Transactional Processing (TP). OLAP systems are optimized for handling complex queries and aggregations on large datasets, often at terabyte or petabyte scales, with read-heavy workloads, columnar storage, and response times ranging from seconds to minutes, primarily used for data warehouses, business intelligence, and reporting. In contrast, OLTP systems manage small, real-time create, read, update, and delete (CRUD) operations, feature write-heavy workloads, row-based storage, millisecond response times, and strict ACID compliance, supporting operational business systems such as enterprise resource planning (ERP) and e-commerce platforms. The following table summarizes key differences:
AspectOLAP (Analytical Processing)OLTP (Transactional Processing)
OperationsComplex queries and aggregations on big dataSmall real-time CRUD operations
Data VolumeMassive (TB/PB scale)Small to medium
Response TimeSeconds to minutesMilliseconds
StorageColumnarRow-based
ScenariosData warehouses, BI, reportsERP, e-commerce
Performance in OLAP systems is primarily measured by query response time, storage efficiency, and , with each type of system—MOLAP, ROLAP, and HOLAP—exhibiting distinct characteristics in these areas. MOLAP systems achieve superior query response times for pre-aggregated, multidimensional analyses, often delivering results in 2-3 seconds for complex aggregations on datasets with around 124,000 records, thanks to their use of pre-computed cubes stored in formats. In contrast, ROLAP systems, which query relational databases directly, typically exhibit slower response times for similar operations due to on-the-fly computations, though they maintain for simpler queries. Storage efficiency represents a key across OLAP variants. MOLAP requires higher storage overhead—often 4-8 bytes per cell in multidimensional arrays—to accommodate pre-consolidated and handle sparsity, making it less efficient for very large or sparse datasets. ROLAP, leveraging standard relational tables, uses less storage by avoiding redundant aggregations but incurs computational costs during queries, which can degrade performance under high load. HOLAP addresses this by hybridizing approaches, storing detailed in relational structures for efficiency and summaries in multidimensional cubes for speed, resulting in balanced storage usage that scales better than pure MOLAP while outperforming pure ROLAP in aggregation-heavy workloads. Scalability further highlights these trade-offs, particularly as volumes grow. MOLAP struggles with large-scale due to cube rebuilding times and constraints, limiting it to departmental applications with fewer dimensions, whereas ROLAP excels in handling terabyte-scale datasets through optimizations. HOLAP improves by dynamically allocating storage modes, allowing seamless handling of both small, fast-access summaries and expansive . In environments, ROLAP-based systems demonstrate strong ; for instance, TPC-H benchmarks on Hadoop clusters show query times scaling linearly from 1.1 GB (0-450 seconds across 22 queries) to 11 GB (0-1400 seconds), with performance degradation of only 5-60% when integrating OLAP . These trade-offs influence practical deployment scenarios. MOLAP is ideal for financial reporting, where rapid access to pre-defined aggregations supports time-sensitive decisions on moderate datasets. ROLAP suits , enabling flexible, ad-hoc queries over vast transactional volumes without the rigidity of cube maintenance. HOLAP serves as a compromise in mixed environments, such as enterprise dashboards requiring both speed and adaptability. Benchmarks like TPC-H underscore these dynamics, evaluating OLAP-like decision support with ad-hoc queries on star schemas, though modern in-memory and cloud advancements have narrowed performance gaps across variants by enabling sub-second responses on petabyte-scale data.

Other Variants and Extensions

Spatial OLAP (SOLAP) integrates geographic information systems (GIS) with traditional OLAP to enable of geospatial data, supporting operations like spatial aggregation and visualization for applications in and . This variant emerged in the late 1990s and early 2000s as a response to the need for handling location-based dimensions alongside conventional measures. Real-time OLAP (RTOLAP) extends OLAP capabilities to process with minimal latency, allowing immediate insights from continuously incoming information sources. It often incorporates integration with streaming platforms such as to ingest and analyze high-velocity data in sectors like and IoT. For instance, systems like Apache Kylin support RTOLAP by querying directly through dedicated receivers. Mobile OLAP adapts OLAP processing for handheld devices by employing semantics-aware compression of data cubes, ensuring efficient query execution despite constraints on storage, bandwidth, and . This extension, exemplified by frameworks like Hand-OLAP, facilitates on-the-go for field-based in and . Collaborative OLAP promotes shared across distributed entities, leveraging architectures to federate data marts while preserving autonomy. It supports inter-organizational by enabling reformulation of OLAP queries over heterogeneous sources, as seen in collaborative environments. Cloud-native extensions of OLAP emphasize serverless architectures that scale dynamically without infrastructure provisioning, such as , which executes SQL-based analytical queries on data stored in for cost-effective, pay-per-query processing. These adaptations suit variable workloads in modern data lakes. Graph OLAP, developed in the , applies OLAP principles to graph-structured data for analyzing networks like social connections or supply chains, using constructs such as Graph Cubes to compute aggregations over nodes and edges. This variant addresses limitations of traditional OLAP in handling interconnected, non-tabular data. Post-2020 advancements have increasingly integrated AI and into OLAP systems, enabling predictive aggregations for forecasting trends within multidimensional cubes, automated query optimization, and interfaces to enhance proactive . Examples include AI-powered and real-time insights in platforms supporting OLAP workflows. Federated OLAP variants, including fast approaches for distributed environments, enable seamless querying across disparate sources without centralization, supporting scalable in multi-site enterprises.

Query Interfaces

APIs and Standards

for OLAP (ODBO), introduced by in 1997, extends the specification to provide programmatic access to multidimensional data stores, enabling developers to query and manipulate OLAP cubes through COM-based interfaces. This defines objects such as MDSchema rowsets for discovery and supports operations like slicing, dicing, and drilling down in OLAP datasets. Building on OD BO, XML for Analysis (XML/A), standardized in 2002 by , Hyperion, and SAS, introduces a SOAP-based web services protocol for accessing OLAP data over HTTP, facilitating in distributed environments. XML/A uses XML payloads to execute commands like multidimensional expressions (MDX) and retrieve results in XML format, making it suitable for cross-platform analytical applications. The Common Warehouse Metamodel (CWM), adopted by the (OMG) in 2001, serves as a standard for interchanging metadata across OLAP and data warehousing tools, using the Meta Object Facility (MOF) and (XMI) for representation. CWM models elements such as dimensions, measures, and transformations, promoting consistency in metadata management without prescribing data storage formats. JOLAP, proposed in Java Specification Request 69 by the in 2000 but withdrawn in 2004 without final approval, aimed to provide a pure for creating, accessing, and maintaining OLAP metadata and , analogous to JDBC for relational . It supported operations on multidimensional schemas and integrated with the Common Warehouse Metamodel for metadata handling, though adoption has been limited compared to vendor-specific implementations like Oracle's OLAP . As a community-driven successor, olap4j, first released in version 1.0 in 2011, has become a widely used open-source for OLAP, supporting connections to various OLAP servers and MDX querying. For .NET environments, ADOMD.NET, a Microsoft library released in the early , enables seamless integration of OLAP functionality by leveraging XML/A over the .NET Framework, allowing developers to connect to Analysis Services and execute analytical queries programmatically. In the 2010s, OLAP systems evolved toward RESTful APIs in cloud platforms, such as Google BigQuery's REST introduced in 2011, which supports HTTP-based queries for scalable analytical processing without proprietary protocols. This shift enhances accessibility for web and mobile applications, decoupling clients from server-specific interfaces. Modern extensions to ODBC and JDBC standards address OLAP needs; for instance, Druid's JDBC driver, compliant with JDBC 4.2 since 2015, enables SQL-like queries on distributed OLAP stores, while Google BigQuery's ODBC/JDBC drivers, updated in the 2020s, handle petabyte-scale with federated query support.

Query Languages

Query languages for online analytical processing (OLAP) enable users to express complex multidimensional queries against data cubes, facilitating operations such as slicing, , and aggregations across dimensions. These languages extend traditional relational querying paradigms to handle hierarchical and multidimensional data structures efficiently, allowing analysts to retrieve insights from large-scale datasets without procedural code. Primarily designed for ad-hoc analysis, OLAP query languages emphasize declarative syntax that abstracts underlying storage mechanisms, whether multidimensional arrays or relational tables. Multidimensional Expressions (MDX) is a SQL-like specifically tailored for querying and manipulating OLAP cubes in multidimensional databases. Developed by and adopted widely in tools like SQL Server Analysis Services, MDX supports the definition of axes for rows, columns, and filters, enabling precise retrieval of measures along dimensions. For instance, a basic MDX query to select sales measures on the columns axis from a sales cube might be written as:

SELECT [Measures].[Sales] ON COLUMNS, [Date].[Year].Members ON ROWS FROM [Sales Cube]

SELECT [Measures].[Sales] ON COLUMNS, [Date].[Year].Members ON ROWS FROM [Sales Cube]

This syntax retrieves sales values aggregated by year, demonstrating MDX's ability to navigate cube hierarchies and compute aggregates declaratively. MDX's extensibility includes functions for calculations, such as time intelligence operations, making it suitable for applications. SQL extensions for OLAP incorporate analytic functions, particularly window functions, to perform directly within relational databases. Standards like those in SQL:2011 define window functions such as RANK(), ROW_NUMBER(), and LAG() that operate over ordered partitions, mimicking OLAP operations like ranking within dimension slices or computing moving averages across . For example, in , OLAP-specific extensions to these functions allow computations like period-to-date aggregates, enabling queries such as SELECT RANK() OVER (PARTITION BY region ORDER BY sales DESC) to rank sales performance within geographic hierarchies. similarly supports OLAP specifications for these functions, integrating them into relational OLAP (ROLAP) systems for efficient aggregation without full cube materialization. These extensions bridge relational and multidimensional querying, reducing the need for specialized OLAP servers in hybrid environments. Data Mining Extensions (DMX) extends OLAP capabilities by providing a language for creating, training, and querying models integrated with multidimensional s. Part of Analysis Services, DMX uses a SQL-like syntax for data definition and manipulation tasks, such as building predictive models on OLAP data. For instance, the CREATE MINING MODEL statement defines structures for algorithms like decision trees, which can then be queried using DMX's SELECT INTO or PREDICTION JOIN syntax to infer patterns from cube measures and dimensions. This integration allows OLAP users to incorporate predictions, such as customer churn forecasts, directly within analytical workflows. Knowledge OLAP (KOLAP), often manifested as OLAP, introduces semantic querying for contextualized over knowledge graphs. This approach models OLAP cubes using semantic representations, where dimensions and measures are linked via RDF triples, enabling queries that incorporate ontological knowledge and context dependencies. The KG-OLAP Cube Model, for example, defines operations like contextual slicing that respect entity relationships and semantics, allowing queries to disambiguate terms based on graph inferences. Such semantics enhance traditional OLAP by supporting federated queries across heterogeneous data sources, as outlined in formal models relating KG-OLAP to contextualized knowledge representations. In .NET environments, (LINQ) integrates with OLAP through providers that translate LINQ expressions into MDX or native cube queries, simplifying multidimensional access for developers. Libraries like those in ComponentOne OLAP enable LINQ syntax to query cubes as IEnumerable collections, supporting operations like grouping by dimensions and aggregating measures without direct MDX authoring. For example, LINQ queries can filter and project OLAP data using lambda expressions, bridging object-oriented programming with analytical processing. This integration leverages providers for seamless connectivity to OLAP servers. Emerging OLAP variants leverage domain-specific languages for specialized multidimensional data. Cypher, the declarative query language for property graphs in , supports graph OLAP by expressing traversals and aggregations over graph dimensions, such as community detection in network cubes. Projects like Graph OLAP demonstrate Cypher's use in defining multidimensional views on graphs, enabling operations like roll-up along relationship hierarchies. Similarly, in Prometheus facilitates time-series OLAP for monitoring analytics, with functions for range vectors and aggregations over temporal dimensions, such as rate() for deriving per-second metrics from counters. These languages address gaps in traditional OLAP for graph and time-series workloads, providing efficient querying for high-velocity data.

Implementations and Market

Commercial Products

Commercial OLAP products have evolved significantly since the early 1990s, transitioning from standalone multidimensional databases to comprehensive components within integrated (BI) suites that support advanced analytics, visualization, and enterprise . This shift reflects the growing demand for scalable, user-friendly tools that combine OLAP capabilities with broader BI functionalities, such as reporting and predictive modeling. One of the pioneering commercial OLAP tools is , launched in 1992 by Arbor Software as a multidimensional OLAP (MOLAP)-focused solution for financial and budgeting. Originally designed for block storage optimization and complex calculations on sparse data sets, Essbase was acquired by Hyperion in 2001 and later integrated into 's ecosystem following the 2007 acquisition of Hyperion. Today, it offers cloud deployment options and advanced aggregation features, serving as a core engine for 's enterprise performance management applications. IBM Cognos, another key player, emphasizes hybrid OLAP (HOLAP) architectures that blend relational and multidimensional processing for flexible data exploration. acquired in 2007 for $4.9 billion, integrating it into its broader analytics portfolio to enhance reporting and dashboarding capabilities across hybrid environments. Analytics now supports AI-driven insights and connectivity to diverse data sources, including SAP BW/4HANA, making it suitable for large-scale enterprise deployments. Microsoft SQL Server Analysis Services (SSAS), introduced in 1998 as part of SQL Server (initially codenamed Plato), supports MOLAP, ROLAP, and HOLAP modes, enabling versatile multidimensional modeling and data mining. Evolving from early OLAP Services, SSAS has become integral to Microsoft's Power BI and Azure Synapse ecosystems, offering tabular models for in-memory processing and seamless integration with relational databases. In terms of market leadership, BW/4HANA is a leading player in the analytics and BI sector by 2025 revenue, powering data warehousing and OLAP operations within SAP's S/4HANA suite for real-time enterprise analytics. Other notable advancements include in-memory options like Tableau's Hyper engine, released in 2018, which accelerates extract creation and analytical queries on large datasets using columnar storage and vectorized processing. Additionally, , acquired in 2019 for $2.6 billion, introduces semantic OLAP through its modeling layer, allowing reusable data definitions and embedded analytics in cloud-native BI applications.

Open-Source and Cloud Solutions

Open-source OLAP solutions have democratized access to multidimensional data analysis by providing free, community-driven tools that support various architectures like ROLAP and real-time processing. Mondrian, an early ROLAP engine written in Java, enables OLAP queries against relational databases using MDX, facilitating flexible schema-on-read operations without proprietary hardware. Apache Kylin, incubated as an Apache project in 2015, serves as a distributed analytical data warehouse optimized for big data environments, delivering sub-second SQL queries on petabyte-scale datasets through pre-built cubes. Apache Druid, originating in 2011, specializes in real-time analytics for event-driven data, combining columnar storage with indexing to handle high-velocity ingestion and sub-second OLAP queries on streaming and batch sources. More recent advancements include Apache Pinot, an open-source system originally developed at LinkedIn in the mid-2010s and entered the Apache incubator in 2019, that excels in sub-second query latencies for user-facing applications, supporting distributed joins and aggregations on billions of rows without pre-aggregation. Cloud-based OLAP implementations emphasize scalability and managed services, often leveraging serverless or decoupled architectures to handle massive workloads. Google BigQuery, launched in 2010, operates as a serverless ROLAP platform, allowing petabyte-scale SQL analytics on decoupled storage using Google's engine for interactive queries without infrastructure management. Snowflake, founded in 2012, introduces a hybrid-like model with strict separation of storage and compute layers, enabling independent scaling for OLAP operations across multi-cluster shared data environments. provides columnar storage and processing tailored for OLAP, supporting features like materialized views and concurrency scaling to optimize analytical queries on large datasets. Emerging trends in these solutions include multi-cloud federation, where OLAP systems integrate data across providers like AWS, Azure, and Google Cloud for unified querying without data movement, enhancing flexibility in hybrid environments. among small and medium-sized enterprises (SMEs) benefits from the cost efficiencies of open-source tools, which run on commodity hardware and avoid licensing fees, while integrations with ecosystems like Hadoop and Spark enable seamless processing of diverse data pipelines. For instance, Apache Kylin natively builds cubes from Hadoop data lakes, and supports Spark for batch ingestion, lowering barriers for resource-constrained organizations. The OLAP market has experienced robust growth, driven by the increasing demand for advanced analytics in (BI) systems. In 2025, the global BI and analytics software market, which encompasses OLAP technologies, reached USD 38.15 billion, reflecting a (CAGR) of 8.17%. This expansion is fueled by widespread adoption across industries, particularly in and retail, where OLAP enables complex multidimensional data analysis for , such as in banking and inventory optimization in . A prominent trend in OLAP is the shift toward cloud-based deployments, offering scalability and reduced infrastructure costs compared to on-premises systems. By 2025, over half of enterprise and SMB workloads, including analytics, are running in public clouds, with OLAP solutions increasingly integrated into cloud data warehouses to handle distributed processing efficiently. Another key development is the integration of artificial intelligence (AI) and machine learning (ML) for predictive OLAP, enhancing capabilities like anomaly detection and forecasting within multidimensional cubes. In 2025, Gartner noted that less than 10% of cloud compute resources are devoted to AI workloads, projected to reach 50% by 2029, influencing OLAP systems' integration with AI for enhanced analytics. The influence of big data further amplifies this, as OLAP systems now routinely manage petabyte-scale datasets for real-time analytics, supporting applications in sectors requiring rapid insights from vast volumes of structured and unstructured data. Despite these advancements, OLAP adoption faces significant challenges, including data privacy concerns amplified by regulations like the General Data Protection Regulation (GDPR). Compliance requires robust anonymization and access controls in OLAP queries to mitigate risks of sensitive data exposure during aggregation. Additionally, a persistent skills gap in multidimensional modeling and query optimization hinders effective implementation, with nearly two-thirds of employers citing skills shortages as a barrier to transformation. Post-2020 trends highlight the emergence of edge OLAP for low-latency processing in IoT-driven environments, enabling closer to data sources in retail and to reduce bandwidth demands. Sustainability in data centers supporting OLAP workloads has also gained traction, with efforts focusing on energy-efficient cooling and adoption to counter the rising power consumption of infrastructure. Looking ahead, future directions include deeper AI synergies for automated cube design and explorations into hybrid edge-cloud architectures to balance performance and cost in an era of exponential data growth.

References

  1. https://www.sciencedirect.com/topics/computer-science/online-analytical-processing
  2. https://cci.drexel.edu/faculty/song/courses/info%20607/tutorial_OLAP/definition_Codd.htm
  3. https://www2.seas.gwu.edu/~bell/csci243/lectures/data_warehousing.pdf
  4. https://zimmer.fresnostate.edu/~sasanr/Teaching-Material/MIS/MRS/olap.pdf
  5. https://www.ibm.com/think/topics/olap
  6. https://www.microsoft.com/en-us/research/wp-content/uploads/2016/02/sigrecord.pdf
  7. https://learn.microsoft.com/en-us/sql/machine-learning/r/using-data-from-olap-cubes-in-r?view=sql-server-ver17
  8. https://www.ibm.com/think/topics/olap-vs-oltp
  9. https://www.oracle.com/database/what-is-oltp/
  10. https://learn.microsoft.com/en-us/azure/architecture/data-guide/relational-data/online-analytical-processing
  11. https://www.semanticscholar.org/paper/Providing-OLAP-to-User-Analysts%253A-An-IT-Mandate-Salley-Codd/a0bd1491a54a4de428c5eef9b836ef6ee2915fe7
  12. https://dl.acm.org/doi/10.1145/248603.248616
  13. https://docs.oracle.com/cd/B13789_01/olap.101/b10333/multimodel.htm
  14. https://datavis.cs.columbia.edu/files/papers/olap.pdf
  15. https://www.cockroachlabs.com/blog/history-of-databases-distributed-sql/
  16. https://cacm.acm.org/opinion/how-the-ai-boom-went-bust/
  17. https://www.dataversity.net/articles/mind-the-gap-analytics-architecture-stuck-in-the-1990s/
  18. https://www.cyfe.com/blog/history-evolution-business-analytics/
  19. http://www.r-5.org/files/books/computers/databases/warehouses/W_H_Inmon-Building_the_Data_Warehouse-EN.pdf
  20. https://scispace.com/pdf/providing-olap-to-user-analysts-an-it-mandate-1klqncel3i.pdf
  21. https://olap.com/learn-bi-olap/olap-business-intelligence-history/
  22. https://crayondata.ai/the-history-of-bi-the-2000s-and-now/
  23. https://blog.dataengineerthings.org/the-history-of-data-engineering-1762b017541a
  24. https://www.codemag.com/article/1308091/The-Baker%25E2%2580%2599s-Dozen-13-Differences-Between-Analysis-Services-OLAP-and-Tabular
  25. https://learn.microsoft.com/en-us/analysis-services/multidimensional-models-scripting-language-assl-xmla/developing-with-xmla-in-analysis-services?view=sql-analysis-services-2025
  26. https://www.sap.com/products/data-cloud/hana/what-is-sap-hana.html
  27. https://kudu.apache.org/releases/1.0.0/docs/
  28. https://druid.apache.org/
  29. https://research.isg-one.com/analyst-perspectives/snowflake-expands-beyond-analytics-in-the-cloud
  30. https://www.ibm.com/think/topics/ai-for-olap
  31. https://blogs.oracle.com/datawarehousing/migration-paths-for-oracle-olap-users
  32. https://www.cs.cornell.edu/courses/cs330/2006fa/slides/2006-11-08-CS330-OLAP.pdf
  33. https://www.sciencedirect.com/topics/computer-science/multidimensional-database
  34. https://docs.oracle.com/en/database/other-databases/essbase/21/essdm/data-storage.html
  35. https://doaj.org/article/8e5b765320864e85adda7c4fa713cd25
  36. https://www.exforsys.com/tutorials/msas/analysis-services-cube-storage-options.html
  37. https://learn.microsoft.com/en-us/analysis-services/multidimensional-models-olap-logical-cube-objects/partitions-partition-storage-modes-and-processing?view=sql-analysis-services-2025
  38. https://learn.microsoft.com/en-us/sql/t-sql/queries/select-group-by-transact-sql?view=sql-server-ver17
  39. https://learn.microsoft.com/en-us/analysis-services/multidimensional-models-olap-logical-cube-objects/partitions-partition-storage-modes-and-processing?view=sql-analysis-services-2022
  40. https://ceres-journal.eu/download.php?file=2020_02_03.pdf
  41. https://support.sas.com/resources/papers/proceedings-archive/SEUGI1998/doherty_hybrid_olap.pdf
  42. https://mondrian.pentaho.com/documentation/architecture.php
  43. https://aws.amazon.com/compare/the-difference-between-olap-and-oltp/
  44. https://www.geeksforgeeks.org/dbms/difference-between-olap-and-oltp-in-dbms/
  45. https://www.snowflake.com/en/fundamentals/olap-vs-oltp-the-differences/
  46. http://www.scielo.org.co/scielo.php?script=sci_arttext&pid=S0120-56092006000300016
  47. https://www.brickclay.com/blog/business-intelligence/olap-a-deep-dive-into-online-analytical-processing/
  48. https://www.researchgate.net/publication/287010120_TPC-H_Benchmark_Analytics_Scenarios_and_Performances_on_Hadoop_Data_Clouds
  49. https://www.tpc.org/tpch/
  50. https://ieeexplore.ieee.org/document/4737127/
  51. https://kylin.apache.org/docs/3.1.3/tutorial/real_time_olap/
  52. https://dl.acm.org/doi/10.1145/1871940.1871950
  53. https://link.springer.com/chapter/10.1007/978-3-642-27358-2_9
  54. https://aws.amazon.com/blogs/architecture/building-a-cloud-based-olap-cube-and-etl-architecture-with-aws-managed-services/
  55. https://dl.acm.org/doi/10.1145/1989323.1989413
  56. https://dl.acm.org/doi/10.1145/2390045.2390051
  57. https://learn.microsoft.com/en-us/previous-versions/windows/desktop/ms714903%28v=vs.85%29
  58. https://learn.microsoft.com/en-us/previous-versions/windows/desktop/ms717005%28v=vs.85%29
  59. https://news.microsoft.com/source/2002/11/21/hyperion-microsoft-and-sas-release-new-specification-of-xml-for-analysis/
  60. https://learn.microsoft.com/en-us/analysis-services/xmla/xml-for-analysis-xmla-reference?view=sql-analysis-services-2025
  61. https://www.omg.org/spec/CWM/1.1/About-CWM
  62. https://jcp.org/en/jsr/detail?id=69
  63. https://olap4j.org/
  64. https://learn.microsoft.com/en-us/analysis-services/client-libraries?view=sql-analysis-services-2025
  65. https://learn.microsoft.com/en-us/analysis-services/multidimensional-models/mdx/mdx-query-the-basic-query?view=sql-analysis-services-2025
  66. https://docs.pentaho.com/pdia-10.2-install/multidimensional-data-modeling-in-pentaho/about-multidimensional-expression-language
  67. https://docs.oracle.com/en/database/oracle/oracle-database/21/olaxs/olap-functions.html
  68. https://www.ibm.com/docs/en/db2-for-zos/12.0.0?topic=expressions-olap-specifications
  69. https://learn.microsoft.com/en-us/sql/dmx/data-mining-extensions-dmx-reference?view=sql-server-ver17
  70. https://learn.microsoft.com/en-us/analysis-services/data-mining/data-mining-ssas?view=asallproducts-allversions
  71. https://www.semantic-web-journal.net/content/knowledge-graph-olap-multidimensional-model-and-query-operations-contextualized-knowledge
  72. https://developer.mescius.com/componentone/docs/win/online-olap/usinglinqasanolapdat.html
  73. https://learn.microsoft.com/en-us/dotnet/csharp/linq/
  74. https://neo4j.com/product/cypher-graph-query-language/
  75. https://github.com/michelcaradec/Graph-OLAP
  76. https://cube.dev/blog/the-evolution-of-olap
  77. https://tactickco.com/en/essbase-an-evolution-over-time/
  78. https://www.oracle.com/a/ocom/docs/essbase-scenario-modeling-ds.pdf
  79. https://mindmajix.com/cognos/a-brief-overview-of-ibm-cognos-10-business-intelligence
  80. https://www.ibm.com/investor/att/pdf/ircorner/07-11-12-1.pdf
  81. https://community.ibm.com/community/user/blogs/shaveta-srivastava/2025/10/29/introducing-sap-bw
  82. https://senturus.com/blog/olap-past-present-future/
  83. https://www.appsruntheworld.com/top-10-analytics-and-bi-software-vendors-and-market-forecast/
  84. https://www.tableau.com/blog/extracts-hyper-speed-what-you-need-know-79397
  85. https://cloud.google.com/blog/topics/inside-google-cloud/expanding-our-platform-for-business-intelligence-and-embedded-analytics
  86. https://mondrian.pentaho.com/documentation/olap.php
  87. https://kylin.apache.org/docs/overview/
  88. https://pinot.apache.org/
  89. https://docs.snowflake.com/en/user-guide/intro-key-concepts
  90. https://aws.amazon.com/redshift/features/
  91. https://www.atscale.com/resource/wbr-datafest2020-cloud-olap-azure-databricks-and-azure-synapse/
  92. https://www.acceldata.io/blog/how-hadoop-software-powers-big-data-analytics-across-industries
  93. https://www.mordorintelligence.com/industry-reports/global-business-intelligence-bi-vendors-market-industry
  94. https://markwideresearch.com/online-analytical-processing-market/
  95. https://dataintelo.com/report/olap-database-systems-market
  96. https://info.flexera.com/CM-REPORT-State-of-the-Cloud
  97. https://www.linkedin.com/pulse/olap-market-key-trends-growth-opportunities-anzte/
  98. https://www.gartner.com/en/newsroom/press-releases/2025-05-13-gartner-identifies-top-trends-shaping-the-future-of-cloud
  99. https://www.firebolt.io/blog/ai-cloud-data-warehouses-2025-2030-market-projections
  100. https://www.aidataanalytics.network/data-governance/articles/7-trends-shaping-data-privacy-in-2025
  101. https://www.td.org/content/td-magazine/skills-2025
  102. https://www.acceldata.io/blog/top-8-big-data-trends-shaping-2025
  103. https://www.networkworld.com/article/4036023/data-center-sustainability-efforts-stall-slightly-in-2025.html
  104. https://www.strategysoftware.com/blog/5-ai-and-bi-adoption-trends-every-leader-must-know-in-2025
Add your contribution
Related Hubs
User Avatar
No comments yet.