OLAP (online analytical processing) (original) (raw)

OLAP (online analytical processing) is a computing method that enables users to easily and selectively extract and query data in order to analyze it from different points of view. OLAP business intelligence queries often aid in trends analysis, financial reporting, sales forecasting, budgeting and other planning purposes.

For example, a user can request data analysis to display a spreadsheet showing all of a company's beach ball products sold in Florida in the month of July. They can compare revenue figures with those for the same products in September and then see a comparison of other product sales in Florida in the same time period.

How OLAP systems work

To facilitate this kind of analysis, data is collected from multiple sources and stored in data warehouses, then cleansed and organized into data cubes. Each OLAP cube contains data categorized by dimensions (such as customers, geographic sales region and time period) derived by dimensional tables in the data warehouses. Dimensions are then populated by members (such as customer names, countries and months) that are organized hierarchically. OLAP cubes are often pre-summarized across dimensions to drastically improve query time over relational databases.

Analysts can then perform five types of OLAP analytical operations against these multidimensional databases:

OLAP software locates the intersection of dimensions, such as all products sold in the Eastern region above a certain price during a certain time period, and displays them. The result is the measure; each OLAP cube has at least one to perhaps hundreds of measures, which derive from information stored in fact tables in the data warehouse.

Flow of data through an OLAP system.

OLAP begins with data accumulated from multiple sources and stored in a data warehouse. The data is then cleansed and stored in OLAP cubes, which users run queries against.

Types of OLAP systems

OLAP systems typically fall into one of three types:

OLTP vs. OLAP

OLAP focuses on data analysis to generate business insights, whereas online transactional processing (OLTP) focuses on real-time processing of online transactions. OLTP is used for executing online database transactions that frontline workers such as cashiers and bank tellers generate. Customer self-service applications such as online banking, travel and e-commerce also generate database transactions and tie into OLTP systems. OLTP can be a data source for OLAP systems.

OLTP vs. OLAP comparison.

This table shows the differences between online analytical processing and online transaction processing.

Uses of OLAP

OLAP can be used for data mining or the discovery of previously undiscerned relationships between data items. An OLAP database does not need to be as large as a data warehouse, since not all transactional data is needed for trend analysis. Using Open Database Connectivity, data can be imported from existing relational databases to create a multidimensional database for OLAP.

OLAP products include IBM Cognos, Microsoft Power BI, Oracle OLAP and Tableau. OLAP features are also included in tools such as Microsoft Excel and Microsoft SQL Server's Analysis Services. OLAP products are typically designed for multiple-user environments, with the cost of the software based on the number of users.

This was last updated in October 2023

Continue Reading About OLAP (online analytical processing)

Dig Deeper on Database management