OLAP (online analytical processing)
What is OLAP (online analytical processing)?
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:
- Roll-up. Also known as consolidation, or drill-up, this operation summarizes the data along the dimension.
- Drill-down. This allows analysts to navigate deeper among the dimensions of data. For example, drilling down from "time period" to "years" and "months" to chart sales growth for a product.
- Slice. This enables an analyst to take one level of information for display, such as "sales in 2017."
- Dice. This allows an analyst to select data from multiple dimensions to analyze, such as "sales of blue beach balls in Iowa in 2017."
- Pivot. Analysts can gain a new view of data by rotating the data axes of the cube.
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.
Types of OLAP systems
OLAP systems typically fall into one of three types:
- Multidimensional OLAP (MOLAP) is OLAP that indexes directly into a multidimensional database.
- Relational OLAP (ROLAP) is OLAP that performs dynamic multidimensional analysis of data stored in a relational database.
- Hybrid OLAP (HOLAP) is a combination of ROLAP and MOLAP. HOLAP combines the greater data capacity of ROLAP with the superior processing capability of MOLAP.
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.
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.
