CUBE takes a specified set of attributes and creates subtotals for all of their possible combinations.
If n attributes are specified for a CUBE, there will be 2 to the n combinations of subtotals returned.
CUBE(a, b, c) = GROUPING SETS((a,b,c), (a,b), (a,c), (b,c), (a), (b), (c), ())
GROUP BY CUBE(attributeList)where attributeList is either one attribute or a comma-separated list of multiple attributes.
DEFINE Resellers AS SELECT DimReseller_AnnualSales AS Sales, DimGeography_CountryRegionName AS Countries, DimGeography_StateProvinceName AS States, DimReseller_OrderMonth AS OrderMonth WHERE DimReseller_OrderMonth IS NOT NULL; RETURN ResellerSales AS SELECT SUM(Sales) AS TotalSales FROM Resellers GROUP BY CUBE(Countries, States, OrderMonth)
Partial CUBE is similar to partial ROLLUP in that you can limit it to certain attributes and precede it with attributes outside the CUBE operator. In this case, subtotals of all possible combinations are limited to the attributes within the cube list (in parentheses), and they are combined with the preceding items in the GROUP BY list.
GROUP BY expr1, CUBE(expr2, expr3)
DEFINE Resellers AS SELECT ... RETURN ResellerSales AS SELECT SUM(Sales) AS TotalSales FROM Resellers GROUP BY Countries, CUBE(States, OrderMonth)