OLAP Cube and Rollup

Context: FIT3003_MOC · one query that returns detail rows and their subtotals and grand total · family map in OLAP (On-Line Analytical Processing) Problem it solves: a cross-tab report (product $\times$ location with row, column and grand totals) without writing and union-ing one group by per total.

Quick Revision

• 🎯 Trigger: “with subtotals”, “and the grand total”, “cross-tabulation” ➔ group by cube (…) for every combination, group by rollup (…) for the drill path from most detailed to grand total only.
• ⚡ Key Constraint: count the grouping sets before running ➔ cube of $n$ columns gives $2^n$ sets; rollup gives $n+1$, dropping columns right-to-left, so rollup (A, B) $\ne$ rollup (B, A).

🔧 Minimal Working Example

select   ProductName, Location, sum(Total_Sales) as Total_Sales
from     SalesFact S, ProductDim P, LocationDim D
where    S.ProductID  = P.ProductID
and      S.LocationID = D.LocationID
and      S.LocationID in ('MEL', 'PER')
and      ProductName  in ('Clothing', 'Shoes')
group by cube (ProductName, Location)
order by ProductName, Location;

Expected output — $9$ rows $=2\times 2$ detail $+2+2+1$:

ProductName	Location	Total_Sales	grouping set
Clothing	Melbourne	1477348	$(P,L)$
Clothing	Perth	861268	$(P,L)$
Clothing	(null)	2338616	$(P)$ — product subtotal
Shoes	Melbourne	1311316	$(P,L)$
Shoes	Perth	897153	$(P,L)$
Shoes	(null)	2208469	$(P)$ — product subtotal
(null)	Melbourne	2788664	$(L)$ — cube only
(null)	Perth	1758421	$(L)$ — cube only
(null)	(null)	4547085	$()$ — grand total

• (null) means “all” ➔ a null in a dimension column marks that column as aggregated away in this row; ascending order by sorts nulls last, so each subtotal lands under its detail rows.
• Swap cube ➔ rollup ➔ the same query returns $7$ rows: the two $(L)$ rows vanish, leaving product subtotals $+$ grand total.
• Cross-tab reading ➔ the $(P,L)$ rows are the grid cells, $(P)$ the row totals, $(L)$ the column totals, $()$ the corner.

🔀 Variations

Cube vs rollup vs partial — row-count arithmetic

(Chapter 19 example: $2$ products $\times$ $2$ locations $\times$ $2$ TimeIDs '201801', '201901')

group by clause	grouping sets	rows
cube (P, L, T)	all $2^3=8$: $(P,L,T),(P,L),(P,T),(L,T),(P),(L),(T),()$	$8+4+4+4+2+2+2+1=27$
rollup (P, L, T)	$3+1=4$: $(P,L,T),(P,L),(P),()$	$8+4+2+1=15$
P, cube (L, T) — partial cube	$P$ glued onto each set of cube (L, T): $(P,L,T),(P,L),(P,T),(P)$	$8+4+4+2=18$
P, rollup (L, T) — partial rollup	$(P,L,T),(P,L),(P)$	$8+4+2=14$

• Rows per set ➔ the number of value combinations of the kept columns that actually occur in the filtered data — at most the $\prod$ of their distinct-value counts, equal only when every combination exists (as in the chapter’s dense example); the empty set $()$ is always one row.
• Cube $\supseteq$ rollup ➔ every rollup set is also a cube set; the slide strikes $12$ rows out of the $27$-row cube to leave exactly the $15$-row rollup.
• Partial = no grand total ➔ the column outside the parentheses is never rolled up, so every row keeps a real ProductName and no row is all-null.
• Set-count formula (W10 recap) ➔ $N$ counts only the columns inside the parentheses: full cube $2^N$ · full rollup $N+1$ · A, cube (B, C) $=1\times 2^2=4$ · A, rollup (B, C) $=1\times (2+1)=3$ ➔ a fixed column multiplies by $1$: it appears in every set and adds none.

Lab 9a — sparse data and a constant column (CHARTER fuel report)

(dw.charter_fact $\bowtie$ dw.pilot, Oct–Dec 1995, commercial pilots, model C-90A: $3$ periods $\times$ $3$ pilots, but only $7$ (period, pilot) pairs flew)

group by clause	sets that survive, with rows each	rows
time_id, c.emp_num, mod_code	$(T,P,M)$ $7$	$7$
cube (time_id, c.emp_num, mod_code)	$(T,P,M)$ $7$ · $(T,P)$ $7$ · $(T,M)$ $3$ · $(T)$ $3$ · $(P,M)$ $3$ · $(P)$ $3$ · $(M)$ $1$ · $()$ $1$	$28$
cube (time_id, c.emp_num), mod_code	$(T,P,M)$ $7$ · $(T,M)$ $3$ · $(P,M)$ $3$ · $(M)$ $1$	$14$
rollup (time_id, c.emp_num, mod_code)	$(T,P,M)$ $7$ · $(T,P)$ $7$ · $(T)$ $3$ · $()$ $1$	$18$
rollup (time_id, c.emp_num), mod_code	$(T,P,M)$ $7$ · $(T,M)$ $3$ · $(M)$ $1$	$11$

• Sparse, not dense ➔ $(T,P)$ yields $7$ rows, not $3\times 3=9$: two pilot–month pairs never occur, so they never appear in any grouping set.
• A constant column doubles the cube ➔ mod_code holds only 'C-90A', so every set with $M$ and its twin without $M$ return identical sums ($525.2$ / $525.2$, …) ➔ $14$ redundant “All Models” rows.
• The fix is a partial form ➔ move the constant column outside the parentheses — cube (time_id, c.emp_num), mod_code halves $28\to 14$, rollup (time_id, c.emp_num), mod_code cuts $18\to 11$; the plain column may sit before or after the cube/rollup term.
• Grand total ➔ $2194.8$ litres in every form ($()$ in the full forms, $(M)$ in the partial ones).

Labelling the subtotal rows — grouping $+$ decode

select decode(grouping(ProductName), 1, 'All Products',  ProductName) as Product_Name,
       decode(grouping(Location),    1, 'All Locations', Location)    as Location_Name,
       sum(Total_Sales) as Total_Sales
from   SalesFact S, ProductDim P, LocationDim D
where  S.ProductID  = P.ProductID
and    S.LocationID = D.LocationID
and    S.LocationID in ('MEL', 'PER')
and    ProductName  in ('Clothing', 'Shoes')
group by cube (ProductName, Location)
order by ProductName, Location;

• grouping(col) ➔ binary: $1$ when col is rolled up in this row, $0$ when it holds a real group value; on cube (P, L) the pair reads $(0,0)$ detail · $(0,1)$ product subtotal · $(1,0)$ location subtotal · $(1,1)$ grand total.
• decode(a, b, c, d) ➔ if $a=b$ then $c$ else $d$ ➔ (null) becomes 'All Products' / 'All Locations', the rest pass through.
• Lab form ➔ decode(grouping(c.emp_num), 1, 'All Pilots', c.emp_num) as Pilot — the numeric emp_num is converted to text to share a column with 'All Pilots'.
• Distinct aliases ➔ Location_Name keeps order by Location pointing at the raw column, so the subtotal rows still sort last.

🔭 Beyond the lecture (not in the slides) ➔ grouping is what separates a subtotal's null from a genuine NULL value stored in a dimension column; is null cannot tell the two apart.

✍️ Practice

Practice 1: For the 2-product, 2-location data above, how many rows does group by rollup (Location, ProductName) return, and which rows does it contain that rollup (ProductName, Location) lacks?

Reference solution

$$\begin{array}{rl}\text{sets}& =(L,P),(L),()\\ \text{rows}& =4+2+1=7\end{array}$$

• Key move: rollup drops the rightmost column first ➔ it now returns the per-location subtotals (Melbourne $2788664$, Perth $1758421$) instead of the per-product ones; same count, different rows.

Practice 2: 2019 total sales per location and month for MEL and PER, with a subtotal per location and a grand total, labelled 'All Months' / 'All Locations'. How many rows?

Reference solution

select decode(grouping(Location), 1, 'All Locations', Location) as Location_Name,
       decode(grouping(Month),    1, 'All Months',    Month)    as Month_Name,
       sum(Total_Sales) as Total_Sales
from   SalesFact S, LocationDim D, TimeDim T
where  S.LocationID = D.LocationID
and    S.TimeID     = T.TimeID
and    S.LocationID in ('MEL', 'PER')
and    Year = 2019
group by rollup (Location, Month)
order by Location, Month;

• Key move: “subtotal per location” names the drill path Location $\to$ Month $\to$ all ➔ rollup, Location first; rows $=2\times 12+2+1=27$. cube would add $12$ unwanted per-month-across-locations rows.

Practice 3: Give each product its own subtotal block (per location, then per product) but no grand total, over the 3-column example. Clause and row count?

Reference solution

group by ProductName, rollup (Location, TimeID)

• Key move: partial rollup ➔ sets $(P,L,T),(P,L),(P)$ ➔ $8+4+2=14$ rows; ProductName outside the parentheses is never nulled.

Practice 4 (Lab 9a C.7): the CHARTER cube returns 28 rows and the rollup 18. Which 10 rows does the rollup lack, and what report do they belong to?

Reference solution

• Missing sets ➔ $(T,M)$ $3$ $+$ $(P,M)$ $3$ $+$ $(P)$ $3$ $+$ $(M)$ $1$ $=10$.
• Key move: rollup only walks Period $\to$ Pilot $\to$ Model from the left, so it never produces a row with the period rolled up but a pilot kept ➔ “each pilot’s total over all three months” ($105$: $631.8$, $106$: $848.5$, $109$: $714.5$) exists only in the cube.

⚠️ Common Mistakes

• 💡 Unqualified LocationID ➔ the slide’s decode example writes and LocationID in (…) with both SalesFact and LocationDim in from ➔ ORA-00918 column ambiguously defined; write S.LocationID.
• 💡 Reading (null) as missing data ➔ in a cube/rollup result it means “all values of this column”; label it with decode(grouping(…)) before it reaches a report.
• 💡 Reaching for cube by default ➔ $2^n$ grows fast and fills a hierarchy report with meaningless cross-combinations; a drill path (Year $\to$ Month, Product $\to$ Location) is rollup.
• 💡 Putting a single-valued column inside the cube ➔ every row appears twice with the same sum (Lab 9a’s “All Models”); filter-fixed columns belong outside the parentheses as a partial cube/rollup.