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);1
6
9
12
ANSWER
The query returns 12 tuples, which is option (D).
Official answer: DSource: Official answer key, GATE 2025 DA (IIT Roorkee)
GROUP BY CUBE (D1, D2) produces the aggregate for every subset of the grouping attributes. With two attributes that is groupings: , , and . The result is the union of these four group-bys.
Grouping by : every one of the combinations is present in Facts, so this gives 6 tuples.
Grouping by alone, with D2 shown as NULL: one tuple per value of D1, so 3 tuples.
Grouping by alone, with D1 shown as NULL: one tuple per value of D2, so 2 tuples.
The empty grouping is the grand total: 1 tuple with both D1 and D2 NULL.
Total tuples:
When every combination is present, a CUBE over attributes with distinct values has tuples. Here that is , which is option (D).
KEY IDEA
CUBE over attributes is the union of all group-bys. When every combination of values is present, its size is , because each attribute is either kept (n_i values) or rolled up into NULL (1 extra).
WATCH OUT FOR
Counting only the finest grouping () as in a plain GROUP BY. Another slip is to forget the grand-total row, or to write something instead of adding the four group-by sizes (6 + 3 + 2 + 1).
From the GATE 2025 DA paper. Every question of this paper · Download the full paper · All 3 Data warehousing questions, with notes
