---
title: "OLAP vs OLTP; data cube, operations"
subject: "Data Mining"
chapter: 2
key: "semester_7/data_mining:olap-oltp-data-cube-fact-dimension"
source: https://www.bctengineer.com/q/semester_7/data_mining/olap-oltp-data-cube-fact-dimension
site: BCT Engineering
---

# OLAP vs OLTP; data cube, operations

Data Mining · Chapter 2: Data Pre-processing

- Asked in: year not given
- Video: [Lec -8 : OLAP vs OLTP | Difference between OLAP vs OLTP with Real Life Examples](https://www.youtube.com/watch?v=DeL3dgiy6vk) — Gate Smashers
- Answer checked by Claude Opus 5.5 (high effort) on 2026-10-14
- Page: https://www.bctengineer.com/q/semester_7/data_mining/olap-oltp-data-cube-fact-dimension
- Whole chapter: https://www.bctengineer.com/md/semester_7/data_mining/chapter-2

## Question

Differentiate between OLAP and OLTP. Define data cube, OLAP operations, fact table and dimension table.

## Short answer

OLTP handles daily transactions with current detailed normalised data; OLAP supports analysis on historical summarised multidimensional data. A data cube is multidimensional data modelled by dimensions and facts, with OLAP operations roll-up, drill-down, slice, dice, pivot. Fact table holds numeric measures and keys; dimension table holds descriptive attributes.

## Answer

#### OLAP vs OLTP
| Point | OLTP | OLAP |
|---|---|---|
| Purpose | Day-to-day transactions | Analysis, decision support |
| Users | Clerks, clients, DBAs | Managers, analysts, executives |
| Data | Current, detailed, flat relational | Historical, summarised, multidimensional |
| Database design | ER model, normalised, application-oriented | Star/snowflake, subject-oriented |
| Access | Read/write, short transactions | Mostly read, complex queries |
| Unit of work | Short, simple transaction | Complex query with aggregates |
| Records per access | Tens | Millions |
| Number of users | Thousands | Hundreds |
| DB size | MB to GB | GB to TB |
| Metric | Transaction throughput | Query response time |

#### Data cube
A **data cube** is a multidimensional representation of data that lets it be modelled and viewed in many dimensions. It is defined by dimensions (entities with respect to which data is kept: time, item, location) and facts (numeric measures such as sales). A cube with n dimensions has 2ⁿ cuboids (for no hierarchy); the lowest-level cuboid is the *base cuboid* and the highest, with no dimension, is the *apex cuboid*.

```text
        location
          /
         +--------+
        /|       /|
       +--------+ |   Cell = sales for
       | +------|-+   (time, item, location)
       |/       |/
       +--------+----- item
      time
```

#### OLAP operations
- **Roll-up**: aggregate by moving up a hierarchy (city → country).
- **Drill-down**: go to a finer level (quarter → month).
- **Slice**: pick a single value in one dimension.
- **Dice**: pick values in two or more dimensions to form a sub-cube.
- **Pivot**: rotate axes for another view.

#### Fact table
Central table holding numeric measures and keys: `Sales(time_key, item_key, loc_key, units, amount)`.

#### Dimension table
Descriptive table for a dimension with hierarchy: `Location(loc_key, street, city, province, country)`.

## Check yourself

The answer's key points as multiple-choice questions, with the correct option and why.

1. A cube with n dimensions (no hierarchy) has how many cuboids?
   A. n
   B. 2n
   C. 2^n
   D. n^2
   Answer: C. There are 2^n cuboids. (Key point: Number of cuboids)

2. The apex cuboid is:
   A. The lowest-level cuboid
   B. The highest cuboid with no dimension
   C. A fact table
   D. A dimension
   Answer: B. The base cuboid is the lowest level. (Key point: Base and apex cuboid)

3. A data cube is defined by:
   A. Dimensions and facts (numeric measures)
   B. Only keys
   C. Only text
   D. Only hierarchies
   Answer: A. Example dimensions: time, item, location. (Key point: Data cube definition)

4. Users of OLTP vs OLAP:
   A. Clerks, clients, DBAs vs managers, analysts, executives
   B. Managers vs clerks
   C. Same
   D. None
   Answer: A. OLTP serves daily operations. (Key point: OLTP vs OLAP users)

## Related in Data Mining

- [OLAP vs OLTP; OLAP operations, fact, dimension](https://www.bctengineer.com/question.md?key=semester_7%2Fdata_mining%3Aolap-oltp-operations-fact-dimension) — Data Mining, chapter 2
- [OLAP vs OLTP; schema, fact, dimension](https://www.bctengineer.com/question.md?key=semester_7%2Fdata_mining%3Aolap-oltp-schema-fact-dimension) — Data Mining, chapter 2
- [OLAP vs OLTP; schema modeling; star DMQL](https://www.bctengineer.com/question.md?key=semester_7%2Fdata_mining%3Aolap-oltp-schema-star-dmql) — Data Mining, chapter 2
- [OLAP operations and star schema in DMQL](https://www.bctengineer.com/question.md?key=semester_7%2Fdata_mining%3Aolap-operations-star-dmql) — Data Mining, chapter 2
- [Uses of OLAP; OLAP vs OLTP](https://www.bctengineer.com/question.md?key=semester_7%2Fdata_mining%3Aolap-uses-compare-oltp) — Data Mining, chapter 2

## Related in other subjects

- [Differentiate OLTP and OLAP](https://www.bctengineer.com/question.md?key=semester_8%2Finformation_system%3Aoltp-vs-olap) — Information System, chapter 4, asked 5×