OLAP (Online Analytical Processing)

OLAP (Online Analytical Processing):

  • OLAP is an approach to answering multi-dimensional analytical (MDA) queries swiftly.
  • OLAP is part of the broader category of business intelligence, which also encompasses relational databases, report writing, and data mining.
  • OLAP tools enable users to analyze multidimensional data interactively from multiple perspectives.

OLAP consists of three basic analytical operations:

  • Consolidation (Roll-Up)
  • Drill-Down
  • Slicing And Dicing
  • Consolidation involves the aggregation of data that can be accumulated and computed in one or more dimensions. For example, all sales offices are rolled up to the sales department or sales division to anticipate sales trends.
  • The drill-down is a technique that allows users to navigate through the details. For instance, users can view the sales by individual products that make up a region’s sales.
  • Slicing and dicing is a feature whereby users can take out (slicing) a specific set of data of the OLAP cube and view (dicing) the slices from different viewpoints.

Types of OLAP:

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 aggregated information. It depends on a specialized schema design.
  • This methodology relies on manipulating the data stored in the relational database to give the appearance of traditional OLAP slicing and dicing functionality. In essence, each action of slicing and dicing is equivalent to adding a “WHERE” clause in the SQL statement.
  • ROLAP tools do not use pre-calculated data cubes but instead pose the query to the standard relational database and its tables in order to bring back the data required to answer the question.
  • ROLAP tools feature the ability to ask any question because the methodology does not limit to the contents of a cube. ROLAP also has the ability to drill down to the lowest level of detail in the database.

Multidimensional OLAP (MOLAP):

  • MOLAP is the ‘classic’ form of OLAP and is sometimes referred to as just OLAP.
  • MOLAP stores this data in an optimized multi-dimensional array storage, rather than in a relational database. Therefore it requires the pre-computation and storage of information in the cube – the operation known as processing.
  • MOLAP tools generally utilize a pre-calculated data set referred to as a data cube.
  • The data cube contains all the possible answers to a given range of questions.
  • MOLAP tools have a very fast response time and the ability to quickly write back data into the data set.

Hybrid OLAP (HOLAP):

  • There is no clear agreement across the industry as to what constitutes Hybrid OLAP, except that a database will divide data between relational and specialized storage.
  • For example, for some vendors, a HOLAP database will use relational tables to hold the larger quantities of detailed data, and use specialized storage for at least some aspects of the smaller quantities of more-aggregate or less-detailed data.
  • HOLAP addresses the shortcomings of MOLAP and ROLAP by combining the capabilities of both approaches.
  • HOLAP tools can utilize both pre-calculated cubes and relational data sources.