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 that data be analyzed to display a spreadsheet showing all of a company's beach ball products sold in Florida in the month of July, 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 data 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 then 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 are derived from information stored in fact tables in the data warehouse.
Types of OLAP systems
OLAP (online analytical processing) 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 was developed to combine the greater data capacity of ROLAP with the superior processing capability of MOLAP.
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 (ODBC), data can be imported from existing relational databases to create a multidimensional database for OLAP.
OLAP products include IBM Cognos, Oracle OLAP and Oracle Essbase. 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.
