# OLAP

OLAP stands for Online Analytical Processing.

It allows for interactive analysis of data, allowing data to be summarized and viewed in different ways in an online fashion (with negligible delay).

Data that can be modeled as *dimension attributes* and *measure attributes* are called **multidimensional data**.

* Measure attributes
  * measure some value
  * can be aggregated upon
  * Ex. the attribute *quantity* of the sales relation
* Dimension attributes
  * define the dimensions on which measure attributes (or aggregates thereof) are viewed
  * Ex. the attributes *item\_name*, *color*, and *clothes\_size* of the sales relation

Example (sales relation):

![](/files/-M5-0a-Q-CRFSr5y-EOD)

## Cross Tabulation / Pivot

![](/files/-M5-0a-SmpdDEqh_l4_E)

The above shows the cross-tabulation table (or cross-tab or pivot table) of the sales table by item\_name and color.

* Values for one of the dimension attributes form the row headers
* Values for another dimension attribute form the column headers
* Other dimension attributes are listed on top
* Values in individual cells are (aggregates of) the values of the dimension attributes that specify the cell

## Data Cube

A data cube is a multidimensional generalization of a cross-tab.

Cross-tabs can be used as views on a data cube.

## Cross-Tabulation with Hierarchy

![](/files/-M5-0a-U4RPH3fhvKjlv)

## Extended Aggregation

The **cube** operation computes a union of **group by’**s on every subset of the specified attributes.

```
select item_name, color, size, sum(quantity)
from sales
group by cube(item_name, color, size)
```

This computes the union of eight different groupings of the sales relation:

{ (item\_name, color, size),

(item\_name, color),

(item\_name, size),

(color, size),

(item\_name),

(color),

(size),

( ) }

where ( ) denotes an empty **group by** list.

For each grouping, the result contains null for attributes not present in the grouping.

## OLAP Operations

* **Pivoting**: changing the dimensions used in a cross-tab
* **Slicing**: creating a cross-tab for fixed values only
  * Sometimes called **dicing**, particularly when values for multiple dimensions are fixed
* **Rollup**: moving from finer-granularity data to a coarser granularity
* **Drill down**: The opposite operation - that of moving from coarser granularity data to finer-granularity data

## OLAP Implementation

* The earliest OLAP systems used multidimensional arrays in memory to store data cubes, and are referred to as **multidimensional OLAP (MOLAP)** systems
* OLAP implementations using only relational database features are called **relational OLAP (ROLAP)** systems
* Hybrid systems, which store some summaries in memory and store the base data and other summaries in a relational database, are called **hybrid OLAP (HOLAP)** systems


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://vikram-bajaj.gitbook.io/cs-gy-6083-principles-of-database-systems/sql/advanced-sql/olap.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.