Part 1: Core Concepts (2 Marks)
- Data Warehouse (Bill Inmon Definition): A subject-oriented, integrated, time-variant, non-volatile collection of data in support of management’s decision-making process.
- Multidimensional Data Modeling: Represents data as multidimensional cubes defined by dimensions (perspectives, e.g., Time, Store, Product) and facts (numerical measures, e.g., Sales Revenue).
- OLTP vs OLAP:
- OLTP (Online Transaction Processing): Supports day-to-day operational transactions (fast inserts/updates, normalized 3NF).
- OLAP (Online Analytical Processing): Supports complex historical data analysis (denormalized star/snowflake schemas, heavy aggregations).
Part 2: Structural Frameworks (10 Marks)
Star Schema vs Snowflake Schema Architecture
Product Dimension
| Time Dimension | --- [FACT TABLE] ------ | Branch Dimension |
|---|---|---|
| Customer Dimension |
Part 3: Distinction Tables
| Basis of Comparison | Star Schema | Snowflake Schema |
|---|---|---|
| Normalization | Completely denormalized dimension tables. | Dimensions are normalized into hierarchies. |
| Query Performance | Extremely fast; minimal SQL table joins required. | Slower; requires multi-table joins across normalized hierarchies. |
| Maintenance | Higher redundancy; slightly larger storage size. | Reduced redundancy; saves disk storage space. |
Part 4: Worked Problems & Exam Traps
Core OLAP Operations
- Roll-Up: Summarizes data by climbing up a hierarchy (e.g. from Day
Month Year). - Drill-Down: Navigates from less detailed data to more detailed data (e.g. from Region
City Store). - Slice: Selects a single sub-dimension from a cube (e.g.
Time = 2026). - Dice: Selects a sub-cube by specifying conditions on two or more dimensions.
- Pivot: Rotates axes of the cube to view data from alternative perspectives.
OLTP databases should NEVER be used directly for complex analytical reporting! Running multi-table aggregate queries on live production OLTP tables locks rows and halts daily customer transactions.