- 2063 · 3+6 marks
OLAP vs OLTP; OLAP operations, fact, dimension
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.
What are the differences between OLAP & OLTP? Define OLAP operations, fact table and dimension table.
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).
✓ Answer checked by Claude Opus 5.5 (high effort)
Related questions
- OLAP vs OLTP; schema, fact, dimension · Data Mining, chapter 2
- OLAP vs OLTP; data cube, operations · Data Mining, chapter 2
- OLAP operations and star schema in DMQL · Data Mining, chapter 2
- OLAP vs OLTP; schema modeling; star DMQL · Data Mining, chapter 2
- Uses of OLAP; OLAP vs OLTP · Data Mining, chapter 2
- Short note: OLAP and OLTP · Data Mining, chapter 2
- Differentiate OLTP and OLAP · Information System, chapter 4 · asked 5×