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

# OLAP vs OLTP; OLAP operations, fact, dimension

Data Mining · Chapter 2: Data Pre-processing

- Asked in: 2063, 3+6 marks
- 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-operations-fact-dimension
- Whole chapter: https://www.bctengineer.com/md/semester_7/data_mining/chapter-2

## Question

What are the differences between OLAP & OLTP? Define OLAP operations, fact table and dimension table.

## Short answer

OLTP handles daily transactions (normalized, current data, many users); OLAP supports analysis (denormalized, historical data, few users). OLAP operations: roll-up, drill-down, slice, dice, pivot. Fact table holds measures and dimension keys; dimension table provides descriptive attributes and hierarchies.

## Answer

#### Differences between OLAP and 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 |

#### OLAP operations
- **Roll-up**: summarise data by climbing a hierarchy (city → country) or reducing a dimension.
- **Drill-down**: from summary to detail (quarter → month).
- **Slice**: fix one dimension to a single value, giving a sub-cube (time = Q1).
- **Dice**: select values on two or more dimensions (time = Q1 or Q2 and location = Kathmandu).
- **Pivot**: rotate the axes to view from another angle.

#### Fact table
Central table of a star schema containing numeric **measures** (units_sold, amount) and foreign keys to the dimensions. Example: `Sales(time_key, item_key, branch_key, units_sold, amount)`.

#### Dimension table
Describes the context of the facts using descriptive attributes and hierarchies. Example: `Item(item_key, name, brand, type)`, `Time(time_key, day, month, quarter, year)`.

## Check yourself

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

1. The performance metric of OLTP vs OLAP:
   A. OLTP: transaction throughput; OLAP: query response time
   B. OLTP: query response time; OLAP: throughput
   C. Both throughput
   D. Both size
   Answer: A. OLTP handles many short transactions. (Key point: Metric)

2. A fact table contains:
   A. Descriptive attributes only
   B. Numeric measures and foreign keys to dimensions
   C. Only hierarchies
   D. Only metadata
   Answer: B. Example: Sales(time_key, item_key, branch_key, units_sold, amount). (Key point: Fact table)

3. A dimension table:
   A. Holds numeric measures
   B. Describes context of facts with descriptive attributes and hierarchies
   C. Is the central table
   D. Has no keys
   Answer: B. Example: Item(item_key, name, brand, type). (Key point: Dimension table)

4. Records per access in OLTP vs OLAP:
   A. Tens vs millions
   B. Millions vs tens
   C. Same
   D. Thousands vs thousands
   Answer: A. OLTP is short, OLAP is large aggregates. (Key point: Records per access)

## Related in Data Mining

- [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; data cube, operations](https://www.bctengineer.com/question.md?key=semester_7%2Fdata_mining%3Aolap-oltp-data-cube-fact-dimension) — 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
- [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
- [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
- [Short note: OLAP and OLTP](https://www.bctengineer.com/question.md?key=semester_7%2Fdata_mining%3Aolap-oltp-note) — 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×