Consider a fact table in an OLAP application: Facts(D1, D2, val), where D1 and D2 are its dimension attributes and val is a dependent attribute. Suppose attribute D1 takes 3 values and D2 takes 2 values, and all combinations of these values are present in the table Facts. How many tuples are there in the result of the following query?
SELECT D1, D2, sum(val)
FROM Facts
GROUP BY CUBE (D1, D2);Answer
(D)
Explanation
GROUP BY CUBE (D1, D2) produces one group of rows for every subset of the grouping attributes:
- (D1, D2): one row per combination, \(3 \times 2 = 6\);
- (D1): one subtotal per value of D1, 3 rows;
- (D2): one subtotal per value of D2, 2 rows;
- (): the grand total, 1 row.
That gives \(6 + 3 + 2 + 1 = 12\) tuples. Equivalently, each dimension is either one of its values or ALL, so \((3 + 1)(2 + 1) = 12\). The value 6 is a plain GROUP BY D1, D2, which leaves out the subtotal and total rows.
Answer: D