OLAP & Data Mining

OLAP: querying the data cube

Online Analytical Processing (OLAP) gives users *multidimensional* views of data and lets them analyze it with simple windowing operations. Conceptually the data is a cube (or hypercube): each dimension is an analytical perspective (Product, Region, Time) and each cell holds a measure (Sales, Cost, Quantity). The five core operations:

- Slice — fix one dimension to a single value (e.g. only line = 'apparel'), dropping the cube by one dimension. - Dice — select ranges across several dimensions, producing a sub-cube. - Drill-down — move to a *finer* level of a hierarchy (quarter → month). - Roll-up — the inverse: aggregate to a *coarser* level (month → quarter → year). - Pivot — rotate the view to see the data from another angle.

Three OLAP implementations: ROLAP queries a relational star schema directly with SQL (always current, can be slow); MOLAP pre-loads a proprietary multidimensional cube (very fast, but needs an ETL refresh and may be stale); HOLAP mixes the two. OLAP is the user-facing face of *descriptive* analytics.

OLAP vs. data mining

The distinction the exam contrasts: OLAP users search for answers to questions they already have ("why did Northeast revenue drop?" → slice, drill); data mining users look for patterns they don't yet know exist. Data mining is knowledge discovery blending statistics, AI, and visualization, with three goals — explanatory (explain an observed event), confirmatory (test a hypothesis), exploratory (find unknown relationships, the most distinctive goal). Techniques include regression, decision trees, clustering, affinity (market-basket) analysis, sequence association, and neural nets.

Worked example: OLAP in plain SQL with window functions

Modern SQL brings OLAP *into the database* via window (analytic) functions: func(...) OVER (PARTITION BY ... ORDER BY ...). Unlike GROUP BY, a window function returns a value for every row while still aggregating within its partition. To rank each product's total revenue *within its product line* (a roll-up + ranking, exactly an OLAP move) on the sales tables:

SELECT p.line,
       p.name,
       SUM(ol.qty * ol.unit_price) AS revenue,
       RANK() OVER (PARTITION BY p.line
                    ORDER BY SUM(ol.qty * ol.unit_price) DESC) AS rank_in_line
FROM products p
JOIN order_lines ol ON ol.product_id = p.product_id
GROUP BY p.line, p.name
ORDER BY p.line, rank_in_line;

The PARTITION BY p.line resets the ranking for each line; the ORDER BY ... DESC inside OVER ranks highest revenue = 1. This is ROLAP made concrete — the multidimensional analysis lives in ordinary SQL, no separate cube required. (Note: when a window function's ORDER BY references an aggregate, you aggregate with GROUP BY first, then the window applies over the grouped rows.)

A second classic OLAP pattern is the running total — a cumulative measure ordered along the time dimension — written as SUM(...) OVER (PARTITION BY ... ORDER BY ...) with no GROUP BY, so it accumulates row by row.