Online Analytical Processing (OLAP) is a type of software that provides analysis of data stored in a database or data warehouse. OLAP tools can quickly provide the answer to complex database queriesand enable users to analyze different dimensions of multidimensional data. OLAP often is used in the data processing of data warehouse, which is called data mining.
Relational databases store entities in discrete tables if they have been properly normalized. This structure is good for operational databases but for complex multi-table queries it is relatively slow. A better model for querying, but worse for operational use, is a dimensional database.
The chief component of OLAP is the OLAP server, which sits between a client and a database management systems (DBMS). The OLAP server understands how data is organized in the database and has special functions for analyzing the data. For example, OLAP software takes a snapshot of a relational database and restructures it into dimensional data. The queries can then be run against this.
An OLAP structure created from the operational data is called an OLAP cube. OLAP cubes can potentially contain all the answers to every query which can be answered from the data. The OLAP cube is created from a star schema of tables. At the centre is the fact table which lists the core facts which make up the query. Numerous dimension tables are linked to the fact tables. Due to the potential number of aggregations to be calculated, often only a predetermined number are fully calculated while the remainder are solved when demanded.
There are three types of OLAP:
Multidimensional OLAP (MOLAP) - MOLAP is the 'classic' form of OLAP and is sometimes referred to as just OLAP. It uses a summary database, has a specific dimensional database engine and creates the required schema as a dimensional set of both base data and aggregations. MOLAP is better on smaller sets of data, it is faster to calculate the aggregations and return answers but does create enormous amounts of data.
Relational OLAP (ROLAP) - ROLAP works directly with relational databases, the base data and the dimension tables are stored as relational tables and new tables are created to hold the aggregation information. ROLAP is considered more scalable and uses the least space but is the slowest at pre-processing and query performance.
Hybrid OLAP (HOLAP) - Hybrid OLAP uses relational tables to hold base data and multi-dimensional tables to hold the speculative aggregations. HOLAP is between MOLAP and ROLAP in all areas, but it can pre-process quickly and scale well.
The difficulty in implementing OLAP comes in forming the queries, choosing the base data and developing the schema, as a result of which most modern OLAP products come with huge libraries of pre-configured queries. Another problem is in the base data - it must be complete and consistent.
OLAP: Online Analytical Processing
Related Terms: ERP, OLTP, Relational Database, Data Mining, Dimensional database, Data warehouse