Skip to content
CampusPrep
CS-703 (B) · Data Mining and Warehousing/Quick Revision Short Notes

Data Mining and Warehousing (CS-703 (B)) - Unit 2 Short Notes

How unit 2 is examined

This unit covers OLAP concepts, OLAP versus OLTP, the ROLAP, MOLAP and HOLAP server types, the five cube operations, and warehouse hardware, security, backup and recovery. Marks sit in OLAP server types and OLAP operations, with one 7-mark question each on the rest.

Basic concepts

<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 technology that lets analysts view and analyse summarised warehouse data interactively and quickly from many dimensions, using a multidimensional data model of cubes, dimensions and measures.</mark>

Key points.

  1. OLAP works on historical, integrated data in the data warehouse, while OLTP works on current, detailed, operational data in the day-to-day database.
  2. An OLAP query is a complex, read-mostly aggregation such as "sales by region and quarter", whereas an OLTP transaction is a short insert, update or delete touching a few rows.
  3. OLAP users are managers and analysts, and OLTP users are clerks and customers, so OLAP measures response by query time and OLTP by transactions per second.
  4. ROLAP stores the data in relational tables and computes cubes with SQL on the fly, while MOLAP stores pre-aggregated data in a multidimensional array.
Basis OLTP OLAP
Purpose Run daily business Decision support and analysis
Data Current, detailed, operational Historical, summarised, integrated
Design Normalised, ER model Star or snowflake schema, cube
Operation Insert, update, delete, short reads Complex read-only aggregate queries
Size MB to GB GB to TB
Users Clerks, DBAs Managers, analysts
Basis ROLAP MOLAP
--- --- ---
Storage Relational tables Multidimensional array (cube)
Speed Slower, SQL at query time Fast, pre-computed
Scalability Handles very large data Limited by cube size
Sparse data Handles well Wastes space

Asked: [7 marks] (Dec 2020) Differentiate between: i) OLAP and OLTP ii) ROLAP and MOLAP

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">Not asked since 2022</span>

Definition. <mark>An OLAP query is a request that aggregates measures of a cube along chosen dimensions, written in SQL for ROLAP or in MDX (Multidimensional Expressions) for MOLAP.</mark>

Key points.

  1. A typical query picks a measure (sales), the dimensions to group by (region, quarter) and filters on members (year = 2024).
  2. In ROLAP it becomes SQL with GROUP BY, extended by GROUP BY CUBE and ROLLUP that produce all subtotals in one query.
  3. In MDX, SELECT {measures} ON COLUMNS, {members} ON ROWS FROM [Cube] WHERE (slicer) returns a grid, and the WHERE slicer works as a slice.
  4. Each OLAP operation is really a query pattern, so the operations below are answered by such queries.

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">High weight</span>

Definition. <mark>OLAP servers are categorised by how they store the multidimensional data: ROLAP uses a relational database, MOLAP uses a multidimensional array store, and HOLAP combines both.</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-01" viewBox="0 0 338 252" width="338" height="252" role="img" aria-label="OLAP server layers. UI = front-end tool, Eng = OLAP engine, RDB = relational store (ROLAP and HOLAP detail), MDB = multidimensional store (MOLAP and HOLAP summary)"><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="ah5" 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="ahh5" 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(#ah5)"/><path class="e" d="M184.8,115.5 L280.5,51.6" marker-end="url(#ah5)"/><path class="e" d="M184.8,136.5 L280.5,200.4" marker-end="url(#ah5)"/><circle class="n" cx="40" cy="126" r="18"/><text class="t" x="40" y="126" dy=".35em" text-anchor="middle">UI</text><circle class="n" cx="169" cy="126" r="18"/><text class="t" x="169" y="126" dy=".35em" text-anchor="middle">Eng</text><circle class="n" cx="298" cy="40" r="18"/><text class="t" x="298" y="40" dy=".35em" text-anchor="middle">RDB</text><circle class="n" cx="298" cy="212" r="18"/><text class="t" x="298" y="212" dy=".35em" text-anchor="middle">MDB</text></svg><figcaption style="font-size:.82em;opacity:.72;margin-top:.45rem">OLAP server layers. UI = front-end tool, Eng = OLAP engine, RDB = relational store (ROLAP and HOLAP detail), MDB = multidimensional store (MOLAP and HOLAP summary)</figcaption></figure>

Key points.

  1. ROLAP (Relational OLAP) keeps data in relational tables in a star or snowflake schema and an OLAP engine turns each request into SQL, so it scales to very large data volumes.
  2. ROLAP is slower because aggregates are computed at query time, but it needs no cube loading and handles sparse and high-cardinality dimensions well.
  3. MOLAP (Multidimensional OLAP) loads and pre-aggregates data into a proprietary multidimensional array, so queries are very fast through direct array indexing.
  4. MOLAP suffers from limited scalability, wasted space on sparse data and the extra load time to build the cube.
  5. HOLAP (Hybrid OLAP) keeps detailed data in the relational database like ROLAP and summaries in a multidimensional store like MOLAP, so it drills down to detail and still answers summary queries quickly.
  6. Categorisation matters because the storage choice decides speed, size limit and cost, so a designer picks the type that fits the data volume and query pattern.
  7. Web-enabled (Internet) OLAP puts a thin browser client in front of an OLAP server: the browser sends an HTTP request, the web server passes it to the OLAP engine, and the result returns as an HTML page or applet chart.
  8. Using OLAP on the Internet needs only a browser: log in, choose a cube, drag dimensions to rows and columns, apply slice, dice or drill, and view the grid or chart. It gives no client installation, central maintenance and access from anywhere, as in Microsoft Analysis Services or Oracle OLAP over the web.
Basis ROLAP MOLAP HOLAP
Storage Relational tables Multidimensional array Relational detail plus array summary
Query speed Slowest, SQL at run time Fastest Fast for summaries, medium for detail
Scalability Very high Limited High
Sparse data Efficient Wasteful Moderate
Load time None Long, cube build Medium
Example MicroStrategy Essbase Microsoft Analysis Services

Answer frame. Open with the definition of OLAP and why servers are categorised; draw the three architectures (front end, OLAP engine, relational or multidimensional store); develop points 1-5 in order ROLAP, MOLAP, HOLAP; add the comparison table; close with the one-line rule "large data: ROLAP, speed: MOLAP, balance: HOLAP". For the Internet question, use points 7-8 with a browser, web server, OLAP server, database diagram.

Pitfall: Do not say MOLAP handles more data than ROLAP; MOLAP is faster but ROLAP is the more scalable.

Asked: [7 marks] (Nov 2023, Dec 2024, Jun 2025, Dec 2025) Explain the categorization of OLAP tools with necessary diagrams. Also: difference between MOLAP and ROLAP; define OLAP and compare different types of OLAP servers. Asked: [7 marks] (Dec 2024) Explain about how to use OLAP tools on the Internet.

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 actions roll-up, drill-down, slice, dice and pivot that let a user navigate a multidimensional data cube, whose axes are dimensions (time, product, location) and whose cells hold measures (sales).</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 431 80" width="431" height="80" role="img" aria-label="Roll-up and drill-down along a location hierarchy. Q = quarter view, City = city level, Cty = country level"><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="ah6" 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="ahh6" 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="M57.8,46.6 Q126,72 185.8,49.8" marker-end="url(#ah6)"/><path class="e" d="M187.6,30.9 Q126,8 59.7,32.7" marker-end="url(#ah6)"/><path class="e" d="M236.4,49.1 Q298,72 364.3,47.3" marker-end="url(#ah6)"/><path class="e" d="M366.2,33.4 Q298,8 238.2,30.2" marker-end="url(#ah6)"/><g class="wl"><rect x="82.8" y="51.1" width="82.2" height="18" rx="9"/><text class="t" x="123.9" y="60.1" dy=".35em" text-anchor="middle">drill-down</text></g><g class="wl"><rect x="94.1" y="10.9" width="61.5" height="18" rx="9"/><text class="t" x="124.8" y="19.9" dy=".35em" text-anchor="middle">roll-up</text></g><g class="wl"><rect x="268.4" y="51.1" width="61.5" height="18" rx="9"/><text class="t" x="299.2" y="60.1" dy=".35em" text-anchor="middle">roll-up</text></g><g class="wl"><rect x="259" y="10.9" width="82.2" height="18" rx="9"/><text class="t" x="300.1" y="19.9" dy=".35em" text-anchor="middle">drill-down</text></g><circle class="n" cx="40" cy="40" r="18"/><text class="t" x="40" y="40" dy=".35em" text-anchor="middle">Q</text><rect class="n" x="187" y="25" width="50" height="30" rx="15"/><text class="t" x="212" y="40" dy=".35em" text-anchor="middle">City</text><circle class="n" cx="384" cy="40" r="18"/><text class="t" x="384" y="40" dy=".35em" text-anchor="middle">Cty</text></svg><figcaption style="font-size:.82em;opacity:.72;margin-top:.45rem">Roll-up and drill-down along a location hierarchy. Q = quarter view, City = city level, Cty = country level</figcaption></figure>

Key points.

  1. A data cube is the multidimensional model in which each dimension has a concept hierarchy, for example day, month, quarter, year, and each cell stores a measure such as sales.
  2. Roll-up aggregates data by climbing a hierarchy (city to country) or by removing a dimension, giving a more summarised view.
  3. Drill-down is the reverse: it moves to finer detail (quarter to month) or adds a dimension, so summarised data becomes detailed.
  4. Slice fixes one dimension to a single value and gives a sub-cube, for example only time = Q1.
  5. Dice fixes values on two or more dimensions and picks a smaller sub-cube, for example time = Q1 or Q2, location = Delhi or Mumbai.
  6. Pivot (rotate) turns the cube axes to give another view of the same data, for example swapping rows (product) and columns (location).
  7. Example on sales data: roll-up gives sales per country instead of per city, drill-down gives sales per month instead of per quarter, slice gives Q1 sales only, dice gives Q1-Q2 sales for Delhi and Mumbai, and pivot places locations on rows.
Operation Effect on cube Example
Roll-up Fewer levels, more summary City to country
Drill-down More detail Quarter to month
Slice One dimension fixed, sub-cube Time = Q1
Dice Two or more dimensions restricted Q1-Q2, Delhi-Mumbai
Pivot Axes rotated Swap rows and columns

Answer frame. Open by defining OLAP and the data cube with dimensions and measures; draw a small 3-D cube (time, product, location) and the hierarchy arrows; develop points 2-6 in the order roll-up, drill-down, slice, dice, pivot with one example each; close with the sentence that these operations make interactive analysis of the cube possible. For "OLAP queries and operations" in a short note, add the query points from OLAP queries.

Asked: [7 marks] (Dec 2020, Nov 2023, Dec 2024) Explain OLAP operations with examples. Also: list and explain the OLAP operations in the multidimensional data model; explain roll-up, drill-down, slice and dice. Asked: [14 marks] (Dec 2025) Write short notes on any two: a) OLAP Queries and Operations b) Summary statistics and Data distributions c) Multidimensional Data model d) Neural network-based algorithms

Data warehouse hardware and operational design: security

<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>Hardware in a data warehouse is the set of processors, memory, disks, I/O channels and network that must be sized so that loading and large analytical queries finish in the time window.</mark>

Key points.

  1. The CPU count and parallel processing (SMP, MPP or clusters) decide how fast scans, joins and aggregations run, since queries touch millions of rows.
  2. Large memory caches indexes and intermediate results, so it cuts disk reads and speeds OLAP queries.
  3. Storage uses RAID for speed and fault tolerance, with partitioning and indexes spread across disks so scans run in parallel.
  4. High I/O bandwidth and a fast network carry ETL loads and OLAP traffic, and the design must scale by adding nodes as data grows.
  5. Sizing is done from data volume, user count and query load, with spare capacity for availability, and security adds access control, encryption and audit.

Asked: [7 marks] (Dec 2025) Explain the role of Hardware components in Operational Design of Data Warehouse.

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">Low weight</span>

Definition. <mark>Backup is a copy of warehouse data taken regularly, and recovery is the restoration of the warehouse from that copy plus logs after a failure, so that the historical data is never lost.</mark>

Key points.

  1. Backup is essential because a warehouse holds years of history that cannot be rebuilt from the source systems.
  2. A full backup copies everything, an incremental backup copies only data changed since the last backup of any kind, and a differential backup copies data changed since the last full backup.
  3. Recovery restores the last full backup, applies the incremental or differential backups, and then replays the redo log to the failure point.
  4. Standby databases, mirrored disks and an offsite disaster-recovery site keep the warehouse available after a site failure.
  5. Backups must be tested by trial restores and scheduled outside the ETL and query window.

Asked: [7 marks] (Dec 2025) Describe backup and recovery strategies in data warehouse environments.

Last-minute revision

  • OLAP analyses historical summarised data; OLTP runs current operational transactions.
  • Five operations: roll-up, drill-down, slice, dice, pivot.
  • Roll-up summarises, drill-down details; slice fixes one dimension, dice fixes two or more.
  • Pivot rotates the cube axes.
  • ROLAP: relational, scalable, slower. MOLAP: array, fastest, limited size. HOLAP: mix of both.
  • MDX is the query language for multidimensional cubes.
  • Full backup copies all; incremental since last backup; differential since last full.
  • Hardware: CPU, memory, RAID storage, I/O, network, parallelism.
  • Web OLAP: browser, web server, OLAP engine, database.

Memory hooks

  • "RD SDP": Roll-up, Drill-down, Slice, Dice, Pivot.
  • ROLAP = Relational = Rows; MOLAP = Multidimensional = Memory-fast.
  • HOLAP = Half and half.
  • Slice = one cut; Dice = many cuts.
  • Incremental = Increase since last; Differential = Difference from full.

Coverage checklist

  • Basic concepts: OLAP vs OLTP, ROLAP vs MOLAP (Dec 2020).
  • OLAP queries: SQL and MDX query forms (not asked).
  • Types of OLAP servers: categorisation of OLAP tools, MOLAP vs ROLAP, compare OLAP servers, OLAP on the Internet (Nov 2023, Dec 2024, Jun 2025, Dec 2025).
  • OLAP operations etc.: operations with examples, list and explain, short notes (Dec 2020, Nov 2023, Dec 2024, Dec 2025).
  • Data Warehouse Hardware and Operational Design: Security: role of hardware components (Dec 2025).
  • Backup And Recovery: backup and recovery strategies (Dec 2025).
Go to where you left off?

Quick Add to Notes

Save questions, your own notes and screenshots into notes filed by unit. It takes a free account.

Create free account

Have an account? Log in