- Year not given
OLAP vs OLTP; data cube, operations
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.
Differentiate between OLAP and OLTP. Define data cube, OLAP operations, fact table and dimension table.
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.
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).
✓ 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, fact, dimension · Data Mining, chapter 2
- OLAP vs OLTP; schema modeling; star DMQL · 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
- Differentiate OLTP and OLAP · Information System, chapter 4 · asked 5×