- Year not given · 3+6 marks
OLAP vs OLTP; schema, fact, dimension
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).
Differentiate between OLAP & OLTP. Explain the warehouse schema, Fact table and Dimension table with example.
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.
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.
✓ Answer checked by Claude Opus 5.5 (high effort)
Related questions
- OLAP vs OLTP; OLAP operations, fact, dimension · Data Mining, chapter 2
- OLAP vs OLTP; schema modeling; star DMQL · 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
- 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×