Introduction to OLAP

OLAP means "online analytical processing". It refers to technologies that enable the dynamic visualization and analysis of data.

Typical OLAP tools include "OLAP cubes" and pivot tables such as the ones provided by Microsoft Excel. These tools take large sets of data and summarize it by grouping records based on a set of criteria. For example, an OLAP cube might summarize sales data grouping it by product, region, and period. In this case, each grid cell would display the total sales for a particular product, in a particular region, and for a specific period. This cell would normally represent data from several records in the original data source.

OLAP tools allow users to redefine these grouping criteria dynamically (on-line), making it easy to perform ad-hoc analysis on the data and discover hidden patterns.

For example, consider the following table:

Date	Product	Region	Sales
Oct 2007	Product A	North	12
Oct 2007	Product B	North	15
Oct 2007	Product C	South	4
Oct 2007	Product A	South	3
Nov 2007	Product A	South	6
Nov 2007	Product C	North	8
Nov 2007	Product A	North	10
Nov 2007	Product B	North	3

Now suppose you were asked to analyze this data and answer questions such as:

Are sales going up or down?
Which products are most important to the company?
Which products are most popular in each region?

In order to answer these simple questions, you would have to summarize the data to obtain tables such as these:

Sales by Date and by Product

Date	Product A	Product B	Product C	Total
Oct 2007	15	15	4	34
Nov 2007	16	3	8	27
Total	31	18	12	61

Sales by Product and by Region

Product	North	South	Total
Product A	22	9	31
Product B	18		18
Product C	8	4	12
Total	48	13	61

Each cell in the summary tables represents several records in the original data source, where one or more values fields are summarized (sum of sales in this case) and categorized based on the values of other fields (date, product, or region in this case).

This can be done easily in a spreadsheet, but the work is tedious, repetitive, and error-prone. Even if you wrote a custom application to summarize the data, you would probably have to spend a lot of time maintaining it to add new views, and users would be constrained in their analyses to the views that you implemented.

OLAP tools allow users to define the views they want interactively, in ad-hoc fashion. They can use pre-defined views or create and save new ones. Any changes to the underlying data are reflected automatically in the views, and users can create and share reports showing these views. In short, OLAP is a tool that provides flexible and efficient data analysis.