Online Analytical Processing or OLAP is one of the approaches used for multidimensional analytics queries. It can be considered one of the constituent parts of a broader category of business intelligence tools that also contains storage systems such as relational databases, data lakes, and processing tools for data mining and visualization. The most common applications of OLAP systems are data analytics, reporting, and process management.

OLAP tools were designed to run analytics on multidimensional data. This analysis consists mainly of three types of operations.

The Three Common Types of OLAP Operations

  • Consolidation or roll up Aggregate information from different objects into a single consolidated view.
  • Drill-down: Allows users to navigate to specific details.
  • Slicing and dicing: Enables users to view the data from different called dimensions, which can be considered individual viewpoints that provide a broader perspective of the data.

OLAP databases use multidimensional data models that seek to provide ad hoc queries and rapid execution times. This multidimensional database concept borrows aspects from several database paradigms such as relational, navigational, and hierarchical databases.

The alternative to OLAP is the Online Transactional Processing or OLTP databases. These databases are categorized by more simple queries and larger volumes of data. This difference is because the purpose of OLTP databases lies in providing support for transactions of data rather than for analytical reporting. In this context, the main difference between OLAP and OLTP is that the first is optimized for reading data. The latter is for the running query focused on modifications and insertions of data such as commands such as read insert, update and delete. OLAP databases are intended for fast access aggregations of data and performing complex calculations on multidimensional data models. They differ from relational databases because these databases store the records as cubes of data, which are arrays of dimensions of consolidated information.

OLAP databases are heavily used in business analysis as these analyses depend heavily on the aggregation of different dimensions of data to create analytics views. These dimensions might correspond to variables that can be considered paired in as two-dimensional arrays, such as might be the case of sales over time. In these structures, which are the standard way to store information in tabular databases, the columns represent the X variables, and the vertical axis represents the Y variable. In OLAP databases, we can link these variables or dimensions across several tables called measures. Each measure is composed of labels, which are described by the dimensions.

The benefit of OLAP databases is that it enables them to run complex questions over large amounts of data, emphasizing providing a reduced time of response. These characteristics allow decision-makers to run complex queries that are ultimately used to create analytical views of the data. This is why it is primarily used in the context of business analytics and operational intelligence. If a query fails to be computed, OLAP databases don’t interrupt any data transaction but might hinder the performance of other analytic queries being run simultaneously.

Data commonly arrive at OLAP databases from ETL/ELT processes from OLTP databases and other systems such as IoT devices or data mining tools.

Satori logo2 white