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

# OLAP vs OLTP; schema, fact, dimension

Data Mining · Chapter 2: Data Pre-processing

- Asked in: year not given, 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-schema-fact-dimension
- Whole chapter: https://www.bctengineer.com/md/semester_7/data_mining/chapter-2

## Question

Differentiate between OLAP & OLTP. Explain the warehouse schema, Fact table and Dimension table with example.

## Short answer

OLTP handles day-to-day transactions; OLAP supports analysis. Warehouse schemas: star (central fact table, denormalised dimensions), snowflake (normalised dimensions), fact constellation (multiple fact tables sharing dimensions). Fact table stores measures and keys, e.g. Sales_Fact(time_key, item_key, branch_key, location_key, units_sold); dimension tables store descriptive attributes, e.g. Time(time_key, day, month, quarter, year).

## 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 |

#### Warehouse schema
A schema is the logical structure of the warehouse. The dimensional models are:
- **Star schema**: central fact table connected to denormalised dimension tables.
- **Snowflake schema**: dimensions are normalised into sub-tables.
- **Fact constellation**: several fact tables sharing dimensions.

```text
 Time --+          +-- Item
        +-- SALES -+
 Branch-+   FACT   +-- Location
```

#### Fact table
Holds the numeric measures and foreign keys. Example: `Sales_Fact(time_key, item_key, branch_key, location_key, units_sold, dollars_sold)`.

#### Dimension table
Holds descriptive attributes of the dimension. Examples:
- `Time(time_key, day, month, quarter, year)`
- `Item(item_key, item_name, brand, type)`
- `Branch(branch_key, branch_name, branch_type)`
- `Location(location_key, street, city, province, country)`

A query "dollars_sold per brand per quarter" joins the fact table with the Item and Time dimensions.

## Check yourself

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

1. Which pairing of system and purpose is correct?
   A. OLTP: decision support; OLAP: day-to-day transactions
   B. OLTP: day-to-day transactions; OLAP: analysis and decision support
   C. Both are used mainly for historical trend analysis
   D. OLTP: data mining only; OLAP: billing only
   Answer: B. OLTP handles routine transactions while OLAP supports analysis and decisions. (Key point: OLAP vs OLTP purpose)

2. Which describes the data typically held for OLAP?
   A. Current, detailed, flat relational
   B. Only the latest transaction of each customer
   C. Historical, summarised, multidimensional
   D. Raw, normalised ER tables
   Answer: C. OLAP data is historical, summarised and multidimensional; OLTP data is current and detailed. (Key point: OLAP data characteristics)

3. What does a fact table such as Sales_Fact contain?
   A. Only descriptive text such as brand and city
   B. Only the primary keys of dimension tables, no measures
   C. Normalised sub-tables of each dimension
   D. Numeric measures (units_sold, dollars_sold) and foreign keys to dimensions
   Answer: D. The fact table holds numeric measures plus foreign keys; descriptive attributes live in dimension tables. (Key point: Fact table)

4. How does a snowflake schema differ from a star schema?
   A. Dimension tables are normalised into sub-tables
   B. It has several fact tables sharing dimensions
   C. It has no fact table
   D. Dimension tables are merged into the fact table
   Answer: A. Snowflake normalises the dimensions; sharing dimensions among several fact tables is a fact constellation. (Key point: Star vs snowflake schema)

## 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 modeling; star DMQL](https://www.bctengineer.com/question.md?key=semester_7%2Fdata_mining%3Aolap-oltp-schema-star-dmql) — 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
- [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×