How unit 2 is examined
This unit covers OLAP concepts, the ROLAP/MOLAP/HOLAP server types, the five OLAP operations and warehouse hardware with operational design; OLAP operations and hardware carry the marks.
OLAP Systems: Basic concepts, OLAP queries
<span style="display:inline-block;padding:.16em .6em;border:1.5px solid currentColor;border-radius:999px;font-size:.68em;font-weight:700;letter-spacing:.06em;text-transform:uppercase;opacity:.75">Low weight</span>
Definition. <mark>OLAP (Online Analytical Processing) is a technique for interactive, multidimensional analysis of large volumes of historical, summarised data, whereas OLTP (Online Transaction Processing) handles the day-to-day insert, update and delete transactions of an organisation.</mark>
Key points.
- OLAP views data as a multidimensional cube of measures (such as sales) along dimensions (such as time, item and location), so a query is a request to summarise, navigate or slice that cube.
- OLAP queries are complex, read-mostly and aggregate-heavy (sum, count, average grouped by dimensions), and each may scan millions of rows, while OLTP queries are short and touch a few records.
- OLTP is application-oriented and used by clerks and database professionals, whereas OLAP is subject-oriented and used by managers, executives and analysts for decision support.
- OLTP keeps current, detailed, normalised data in the hundreds of MB to GB, while OLAP keeps historical, consolidated, denormalised data in the GB to TB range.
| Feature | OLTP | OLAP |
|---|---|---|
| Orientation | Transaction (application) | Analysis (subject) |
| Users | Clerks, DBAs, IT staff | Managers, analysts, executives |
| Data | Current, detailed, normalised | Historical, summarised, multidimensional |
| Queries | Short, simple, read and write | Complex, aggregate, mostly read |
| Access | Few records, indexed | Millions of records, scans |
| Metric | Transactions per second | Query response time |
| Size | MB to GB | GB to TB |
Answer frame. Open with the two definitions; draw the comparison table with all seven rows; close with "OLTP runs the business, OLAP helps decide how to run it."
Asked: [7 marks] (May 2023) Compare and contrast online transaction processing with online analytical processing.
Types of OLAP servers
<span style="display:inline-block;padding:.16em .6em;border:1.5px solid currentColor;border-radius:999px;font-size:.68em;font-weight:700;letter-spacing:.06em;text-transform:uppercase;opacity:.75">Low weight</span>
Definition. <mark>An OLAP server sits between the data warehouse and the client tool and answers multidimensional queries; it is ROLAP (relational), MOLAP (multidimensional array) or HOLAP (hybrid) according to where the data is stored.</mark>
Key points.
- ROLAP keeps detail and aggregates in relational tables and turns each cube request into SQL, so it scales to very large data but answers more slowly (example: Microstrategy).
- MOLAP stores the data in pre-computed multidimensional arrays, so queries are very fast through direct array indexing, but the cube is limited in size and sparse cubes waste space (example: Essbase).
- HOLAP keeps detailed data in the relational store and aggregates in a MOLAP cube, so it gives good scalability with fast summary queries (example: Microsoft SQL Server Analysis Services).
| Feature | ROLAP | MOLAP | HOLAP |
|---|---|---|---|
| Storage | Relational tables | Multidimensional arrays | Detail relational, summary array |
| Scalability | Very high | Limited | High |
| Speed | Slower, runs SQL | Fastest | Fast for summaries |
| Pre-computation | Little | Full | Partial |
| Use case | Huge, detailed data | Small dense cubes | Balanced need |
Answer frame. Open with the definition of an OLAP server; draw the table above; close with the use case of each type.
Asked: [7 marks] (May 2023) Differentiate ROLAP, MOLAP and HOLAP server functionalities.
OLAP operations
<span style="display:inline-block;padding:.16em .6em;border:1.5px solid currentColor;border-radius:999px;font-size:.68em;font-weight:700;letter-spacing:.06em;text-transform:uppercase;opacity:.75">High weight</span>
Definition. <mark>OLAP operations are the analytical manipulations of a data cube, namely roll-up, drill-down, slice, dice and pivot, that let a user view the multidimensional data model at different levels and angles.</mark> The multidimensional data model stores measures (numeric facts, e.g. sales) in a data cube whose edges are dimensions (e.g. time, item, location), each dimension having a concept hierarchy (day, month, quarter, year).
Diagram. <figure class="ds-fig" style="margin:1.4rem 0;overflow-x:auto"><svg xmlns="http://www.w3.org/2000/svg" id="dsfig-u2-01" viewBox="0 0 499.8 389.6" width="499.8" height="389.6" role="img" aria-label="Cube with time, item, location; RU roll-up, DD drill-down, SL slice, DC dice, PV pivot"><style>#dsfig-u2-01 .e{stroke:#454C5A;stroke-width:1.4;fill:none}#dsfig-u2-01 .e.hi{stroke:#2340B8;stroke-width:2.6}#dsfig-u2-01 .n{fill:#FFFFFF;stroke:#16181D;stroke-width:1.4}#dsfig-u2-01 .n.hi{fill:#E3E9FC;stroke:#2340B8;stroke-width:2.2}#dsfig-u2-01 .n.rb-b{fill:#16181D;stroke:#16181D}#dsfig-u2-01 .n.rb-r{fill:#BD3227;stroke:#BD3227}#dsfig-u2-01 text{font-family:"JetBrains Mono",ui-monospace,Menlo,Consolas,monospace;font-size:13px}#dsfig-u2-01 .t{fill:#16181D;font-weight:500}#dsfig-u2-01 .t.inv{fill:#FFFFFF;font-weight:700}#dsfig-u2-01 .kd{stroke:#16181D;stroke-width:1.2}#dsfig-u2-01 .dot{fill:#16181D}#dsfig-u2-01 .ann{fill:#2340B8;font-size:11px;font-weight:700}#dsfig-u2-01 .lbl{fill:#6F7787;font-family:system-ui,-apple-system,sans-serif;font-size:12px;font-weight:700}#dsfig-u2-01 .ptr{fill:#2340B8;font-size:12px;font-weight:700}#dsfig-u2-01 .ah{fill:#454C5A}#dsfig-u2-01 .ah.hi{fill:#2340B8}#dsfig-u2-01 .wl rect{fill:#FFFFFF;stroke:#DCE0E7}#dsfig-u2-01 .wl .t{font-size:12px;font-weight:700}#dsfig-u2-01 .wl.hi rect{fill:#2340B8;stroke:#2340B8}#dsfig-u2-01 .wl.hi .t{fill:#FFFFFF}html.dark #dsfig-u2-01 .e{stroke:#B1B7C3}html.dark #dsfig-u2-01 .e.hi{stroke:#8FA3FF}html.dark #dsfig-u2-01 .n{fill:#161920;stroke:#E6E8ED}html.dark #dsfig-u2-01 .n.hi{fill:#1E2748;stroke:#8FA3FF}html.dark #dsfig-u2-01 .n.rb-b{fill:#E6E8ED;stroke:#E6E8ED}html.dark #dsfig-u2-01 .n.rb-r{fill:#FF7E71;stroke:#FF7E71}html.dark #dsfig-u2-01 .t{fill:#E6E8ED}html.dark #dsfig-u2-01 .t.inv{fill:#0F1115}html.dark #dsfig-u2-01 .kd{stroke:#E6E8ED}html.dark #dsfig-u2-01 .dot{fill:#E6E8ED}html.dark #dsfig-u2-01 .ann{fill:#8FA3FF}html.dark #dsfig-u2-01 .lbl{fill:#858D9C}html.dark #dsfig-u2-01 .ptr{fill:#8FA3FF}html.dark #dsfig-u2-01 .ah{fill:#B1B7C3}html.dark #dsfig-u2-01 .ah.hi{fill:#8FA3FF}html.dark #dsfig-u2-01 .wl rect{fill:#161920;stroke:#2A2E37}html.dark #dsfig-u2-01 .wl.hi rect{fill:#8FA3FF;stroke:#8FA3FF}html.dark #dsfig-u2-01 .wl.hi .t{fill:#0F1115}</style><defs><marker id="ah2" viewBox="0 0 10 10" refX="9" refY="5" markerWidth="7" markerHeight="7" orient="auto-start-reverse"><path class="ah" d="M0,1 L9,5 L0,9 z"/></marker><marker id="ahh2" viewBox="0 0 10 10" refX="9" refY="5" markerWidth="7" markerHeight="7" orient="auto-start-reverse"><path class="ah hi" d="M0,1 L9,5 L0,9 z"/></marker></defs><path class="e" d="M216,154.8 L57.6,51.5" marker-end="url(#ah2)"/><path class="e" d="M40,59 L40,277" marker-end="url(#ah2)"/><path class="e" d="M260.1,155.6 L434.8,50.8" marker-end="url(#ah2)"/><path class="e" d="M260.1,182.4 L434.8,287.2" marker-end="url(#ah2)"/><path class="e" d="M237.8,195 L237.8,328.6" marker-end="url(#ah2)"/><g class="wl"><rect x="111.7" y="95.5" width="54.3" height="18" rx="9"/><text class="t" x="138.9" y="104.5" dy=".35em" text-anchor="middle">rollup</text></g><g class="wl"><rect x="2" y="160" width="75.9" height="18" rx="9"/><text class="t" x="40" y="169" dy=".35em" text-anchor="middle">drilldown</text></g><g class="wl"><rect x="321.8" y="95.5" width="47.1" height="18" rx="9"/><text class="t" x="345.3" y="104.5" dy=".35em" text-anchor="middle">slice</text></g><g class="wl"><rect x="324.9" y="224.5" width="40.8" height="18" rx="9"/><text class="t" x="345.3" y="233.5" dy=".35em" text-anchor="middle">dice</text></g><g class="wl"><rect x="214.2" y="250.3" width="47.1" height="18" rx="9"/><text class="t" x="237.8" y="259.3" dy=".35em" text-anchor="middle">pivot</text></g><rect class="n" x="212.8" y="154" width="50" height="30" rx="15"/><text class="t" x="237.8" y="169" dy=".35em" text-anchor="middle">Cube</text><circle class="n" cx="40" cy="40" r="18"/><text class="t" x="40" y="40" dy=".35em" text-anchor="middle">RU</text><circle class="n" cx="40" cy="298" r="18"/><text class="t" x="40" y="298" dy=".35em" text-anchor="middle">DD</text><circle class="n" cx="452.8" cy="40" r="18"/><text class="t" x="452.8" y="40" dy=".35em" text-anchor="middle">SL</text><circle class="n" cx="452.8" cy="298" r="18"/><text class="t" x="452.8" y="298" dy=".35em" text-anchor="middle">DC</text><circle class="n" cx="237.8" cy="349.6" r="18"/><text class="t" x="237.8" y="349.6" dy=".35em" text-anchor="middle">PV</text></svg><figcaption style="font-size:.82em;opacity:.72;margin-top:.45rem">Cube with time, item, location; RU roll-up, DD drill-down, SL slice, DC dice, PV pivot</figcaption></figure>
Key points.
- Roll-up aggregates data by climbing a concept hierarchy (city to country) or by removing a dimension, so the cube becomes smaller and more summarised.
- Drill-down is the reverse of roll-up: it moves to finer detail (quarter to month) or adds a dimension.
- Slice fixes one dimension to a single value and yields a sub-cube of one lower dimension, such as time = Q1.
- Dice selects a sub-cube by choosing ranges or sets of values on two or more dimensions, such as time in Q1 or Q2 and item in Mobile or Laptop.
- Pivot (rotate) turns the axes of the cube or report to give an alternative view, such as swapping rows and columns.
- These operations are performed on the cube and let a manager move interactively from summary to detail without writing SQL.
Example. Sales cube (in thousand Rs), dimensions time, item, location.
| Operation | Effect on the sales cube |
|---|---|
| Roll-up | Location city to country: Bhopal 40 + Indore 30 = India 70 |
| Drill-down | Quarter Q1 to months: Jan 20, Feb 25, Mar 25 (Q1 = 70) |
| Slice | time = Q1 gives a 2-D item by location table |
| Dice | time in (Q1, Q2), item in (Mobile, Laptop), location = Bhopal |
| Pivot | Rows item and columns location become rows location and columns item |
Answer frame. Open by defining the multidimensional model, data cube and OLAP; draw a 3-D cube with the three dimensions labelled and show each operation beside it; develop points 1-5 in order with the example table; close with "together they give the analyst a fast, interactive view of the same data at any level."
Pitfall: Slice uses one value of one dimension, while dice uses ranges on two or more dimensions; confusing them loses marks.
Asked: [7 marks] (May 2023, May 2024, Jun 2025) Describe the various OLAP operations performed on the multidimensional data model; discuss typical OLAP operations with an example; with necessary diagrams and data cubes explain the operations.
Data Warehouse Hardware and Operational Design
<span style="display:inline-block;padding:.16em .6em;border:1.5px solid currentColor;border-radius:999px;font-size:.68em;font-weight:700;letter-spacing:.06em;text-transform:uppercase;opacity:.75">High weight</span>
Definition. <mark>Warehouse hardware and operational design is the choice of servers, storage, parallel architecture and networks, together with the day-to-day processes of loading, refreshing, partitioning and managing the data, so that OLAP queries and mining run fast on very large data.</mark>
Diagram. <figure class="ds-fig" style="margin:1.4rem 0;overflow-x:auto"><svg xmlns="http://www.w3.org/2000/svg" id="dsfig-u2-02" viewBox="0 0 474 252" width="474" height="252" role="img" aria-label="Source systems, ETL, warehouse server with disk array, then OLAP and mining tools"><style>#dsfig-u2-02 .e{stroke:#454C5A;stroke-width:1.4;fill:none}#dsfig-u2-02 .e.hi{stroke:#2340B8;stroke-width:2.6}#dsfig-u2-02 .n{fill:#FFFFFF;stroke:#16181D;stroke-width:1.4}#dsfig-u2-02 .n.hi{fill:#E3E9FC;stroke:#2340B8;stroke-width:2.2}#dsfig-u2-02 .n.rb-b{fill:#16181D;stroke:#16181D}#dsfig-u2-02 .n.rb-r{fill:#BD3227;stroke:#BD3227}#dsfig-u2-02 text{font-family:"JetBrains Mono",ui-monospace,Menlo,Consolas,monospace;font-size:13px}#dsfig-u2-02 .t{fill:#16181D;font-weight:500}#dsfig-u2-02 .t.inv{fill:#FFFFFF;font-weight:700}#dsfig-u2-02 .kd{stroke:#16181D;stroke-width:1.2}#dsfig-u2-02 .dot{fill:#16181D}#dsfig-u2-02 .ann{fill:#2340B8;font-size:11px;font-weight:700}#dsfig-u2-02 .lbl{fill:#6F7787;font-family:system-ui,-apple-system,sans-serif;font-size:12px;font-weight:700}#dsfig-u2-02 .ptr{fill:#2340B8;font-size:12px;font-weight:700}#dsfig-u2-02 .ah{fill:#454C5A}#dsfig-u2-02 .ah.hi{fill:#2340B8}#dsfig-u2-02 .wl rect{fill:#FFFFFF;stroke:#DCE0E7}#dsfig-u2-02 .wl .t{font-size:12px;font-weight:700}#dsfig-u2-02 .wl.hi rect{fill:#2340B8;stroke:#2340B8}#dsfig-u2-02 .wl.hi .t{fill:#FFFFFF}html.dark #dsfig-u2-02 .e{stroke:#B1B7C3}html.dark #dsfig-u2-02 .e.hi{stroke:#8FA3FF}html.dark #dsfig-u2-02 .n{fill:#161920;stroke:#E6E8ED}html.dark #dsfig-u2-02 .n.hi{fill:#1E2748;stroke:#8FA3FF}html.dark #dsfig-u2-02 .n.rb-b{fill:#E6E8ED;stroke:#E6E8ED}html.dark #dsfig-u2-02 .n.rb-r{fill:#FF7E71;stroke:#FF7E71}html.dark #dsfig-u2-02 .t{fill:#E6E8ED}html.dark #dsfig-u2-02 .t.inv{fill:#0F1115}html.dark #dsfig-u2-02 .kd{stroke:#E6E8ED}html.dark #dsfig-u2-02 .dot{fill:#E6E8ED}html.dark #dsfig-u2-02 .ann{fill:#8FA3FF}html.dark #dsfig-u2-02 .lbl{fill:#858D9C}html.dark #dsfig-u2-02 .ptr{fill:#8FA3FF}html.dark #dsfig-u2-02 .ah{fill:#B1B7C3}html.dark #dsfig-u2-02 .ah.hi{fill:#8FA3FF}html.dark #dsfig-u2-02 .wl rect{fill:#161920;stroke:#2A2E37}html.dark #dsfig-u2-02 .wl.hi rect{fill:#8FA3FF;stroke:#8FA3FF}html.dark #dsfig-u2-02 .wl.hi .t{fill:#0F1115}</style><defs><marker id="ah3" viewBox="0 0 10 10" refX="9" refY="5" markerWidth="7" markerHeight="7" orient="auto-start-reverse"><path class="ah" d="M0,1 L9,5 L0,9 z"/></marker><marker id="ahh3" viewBox="0 0 10 10" refX="9" refY="5" markerWidth="7" markerHeight="7" orient="auto-start-reverse"><path class="ah hi" d="M0,1 L9,5 L0,9 z"/></marker></defs><path class="e" d="M59,126 L148,126" marker-end="url(#ah3)"/><path class="e" d="M188,126 L277,126" marker-end="url(#ah3)"/><path class="e" d="M313.8,115.5 L403.7,55.5" marker-end="url(#ah3)"/><path class="e" d="M313.8,136.5 L403.7,196.5" marker-end="url(#ah3)"/><circle class="n" cx="40" cy="126" r="18"/><text class="t" x="40" y="126" dy=".35em" text-anchor="middle">Src</text><circle class="n" cx="169" cy="126" r="18"/><text class="t" x="169" y="126" dy=".35em" text-anchor="middle">ETL</text><circle class="n" cx="298" cy="126" r="18"/><text class="t" x="298" y="126" dy=".35em" text-anchor="middle">DW</text><rect class="n" x="402" y="25" width="50" height="30" rx="15"/><text class="t" x="427" y="40" dy=".35em" text-anchor="middle">OLAP</text><rect class="n" x="402" y="197" width="50" height="30" rx="15"/><text class="t" x="427" y="212" dy=".35em" text-anchor="middle">Mine</text></svg><figcaption style="font-size:.82em;opacity:.72;margin-top:.45rem">Source systems, ETL, warehouse server with disk array, then OLAP and mining tools</figcaption></figure>
Key points.
- Warehouse queries scan and aggregate huge tables, so the server needs high-performance multi-core CPUs and large memory to hold working data, indexes and intermediate results.
- Storage must be large and fast: disk arrays (RAID) give capacity, striping for throughput and redundancy for fault tolerance, and SAN or SSD tiers speed up hot data.
- Parallel architectures are the key to speed: SMP shares memory among processors, MPP or clusters use shared-nothing nodes, and NUMA sits between them; queries run in parallel across partitions.
- The network and I/O bandwidth between source systems, warehouse and clients must be high, as I/O is usually the bottleneck for scans and mining workloads.
- Scalability is planned by adding processors, disks or nodes as data and users grow, with query throughput and mining run-time as the measures.
- Operational design covers ETL (extract, clean, transform and load), a regular refresh schedule in the batch window, and partitioning of large tables by time to speed queries and archiving.
- Operations also include indexing and summary tables for aggregates, monitoring performance, and capacity planning.
- An example architecture is source systems feeding ETL into a warehouse on a parallel server with a disk array, and OLAP and mining tools on top.
Answer frame. Open with the definition and why OLAP and mining need strong hardware; draw the architecture diagram; develop points 1-5 (hardware) and then 6-7 (operations); close with the scalability and throughput point.
Asked: [14 marks] (May 2024, Jun 2025) Discuss data warehouse hardware and operational design; discuss the key hardware considerations, focusing on components supporting OLAP queries and data mining.
Security, Backup And Recovery
<span style="display:inline-block;padding:.16em .6em;border:1.5px solid currentColor;border-radius:999px;font-size:.68em;font-weight:700;letter-spacing:.06em;text-transform:uppercase;opacity:.75">Not asked since 2022</span>
Definition. Warehouse security protects the sensitive data by controlling who may access what, while backup and recovery copy the warehouse regularly so it can be restored after a failure.
Key points.
- Security uses authentication, role-based authorisation, views that hide rows and columns, encryption and audit logs.
- Backups may be full, incremental or differential, taken in the batch window after loads, with copies kept off-site.
- Recovery restores the last full backup and applies later incrementals or logs; the warehouse is read-mostly, so it can be reloaded from sources.
- Plans define recovery time and are tested periodically.
Last-minute revision
- OLAP is analysis of historical, summarised, multidimensional data; OLTP handles current transactions.
- OLTP users are clerks, OLAP users are managers and analysts.
- ROLAP uses relational tables, MOLAP uses arrays, HOLAP is a hybrid of both.
- MOLAP is fastest, ROLAP is most scalable, HOLAP balances the two.
- Five operations: roll-up, drill-down, slice, dice, pivot.
- Roll-up climbs a hierarchy or drops a dimension; drill-down is its reverse.
- Slice fixes one dimension value; dice selects on two or more dimensions.
- Pivot rotates the axes of the cube.
- Hardware needs: multi-core CPU, memory, RAID disk arrays, fast I/O and network.
- Parallel architectures: SMP, MPP or cluster, NUMA.
- Operational design: ETL, refresh, partitioning, indexing, monitoring.
- Backups are full, incremental or differential, with off-site copies.
Memory hooks
- Operations: "R D S D P", Roll, Drill, Slice, Dice, Pivot.
- Slice is one value, dice is a box of values.
- ROLAP = Relational, MOLAP = Multidimensional array, HOLAP = Hybrid.
- Hardware trio: CPU, Disk, Network, all made parallel.
- OLTP runs the shop, OLAP studies the shop.
Coverage checklist
- OLAP Systems: Basic concepts, OLAP queries: OLTP versus OLAP comparison (7 marks, May 2023).
- Types of OLAP servers: ROLAP, MOLAP, HOLAP differences (7 marks, May 2023).
- OLAP operations: five operations with data cube example and diagram (7 marks, May 2023, May 2024, Jun 2025).
- Data Warehouse Hardware and Operational Design: hardware and operational design (14 marks, May 2024, Jun 2025).
- Security, Backup And Recovery: not asked recently, taught briefly.