Summarizing Data Using CUBE
The CUBE operator generates a result set that is a multidimensional cube. A multidimensional cube is an expansion of fact data, or data that records individual events. The expansion is based on columns that the user wants to analyze. These columns are called dimensions. The cube is a result set that contains a cross tabulation of all the possible combinations of the dimensions.
The CUBE operator is specified in the GROUP BY clause of a SELECT statement. The select list contains the dimension columns and aggregate function expressions. The GROUP BY specifies the dimension columns and the keywords WITH CUBE. The result set contains all possible combinations of the values in the dimension columns, together with the aggregate values from the underlying rows that match that combination of dimension values.
For example, a simple table Inventory contains the following:
Item Color Quantity
-------------------- -------------------- --------------------------
Table Blue 124
Table Red 223
Chair Blue 101
Chair Red 210
The following query returns a result set that contains the Quantity subtotal for all possible combinations of Item and Color:
SELECT Item, Color, SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH CUBE
Here is the result set.
Item Color QtySum
-------------------- -------------------- --------------------------
Chair Blue 101.00
Chair Red 210.00
Chair (null) 311.00
Table Blue 124.00
Table Red 223.00
Table (null) 347.00
(null) (null) 658.00
(null) Blue 225.00
(null) Red 433.00
The following rows from the result set are of special interest:
Chair (null) 311.00
This row reports a subtotal for all rows having the value Chair in the Item dimension. The value null is returned for the Color dimension to show that aggregate reported by the row includes rows with any value of the Color dimension.
Table (null) 347.00
This row is similar, but reports the subtotal for all rows having Table in the Item dimension.
(null) (null) 658.00
This row reports the grand total for the cube. Both the Item and Color dimensions have the value null. This shows that all values of both dimensions are summarized in the row.
(null) Blue 225.00
(null) Red 433.00
These two rows report the subtotals for the Color dimension. Both have null in the Item dimension to show that the aggregate data came from rows having any value for the Item dimension.
Using GROUPING to Distinguish Null Values
The null values generated by the CUBE operation present a problem: How can a NULL generated by the CUBE operation be distinguished from a NULL returned in the actual data? This is achieved by using the GROUPING function. The GROUPING function returns 0 if the column value came from the fact data, and 1 if the column value is a NULL generated by the CUBE operation. In a CUBE operation, a generated NULL represents all values. The SELECT statement can be written to use the GROUPING function to substitute the string ALL for any generated NULL. Because a NULL from the fact data indicates the data value is unknown, the SELECT can also be coded to return the string UNKNOWN for any NULL from the fact data. For example:
SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'
ELSE ISNULL(Item, 'UNKNOWN')
END AS Item,
CASE WHEN (GROUPING(Color) = 1) THEN 'ALL'
ELSE ISNULL(Color, 'UNKNOWN')
END AS Color,
SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH CUBE
Multidimensional Cubes
The CUBE operator can be used to generate n-dimensional cubes, or cubes with any number of dimensions. A single dimension cube can be used to generate a total, for example:
SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'
ELSE ISNULL(Item, 'UNKNOWN')
END AS Item,
SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item WITH CUBE
GO
This SELECT statement returns a result set that shows both the subtotals for each value of Item and the grand total for all values of Item:
Item QtySum
-------------------- --------------------------
Chair 311.00
Table 347.00
ALL 658.00
SELECT statements that contain a CUBE with many dimensions can generate large result sets, because these statements generate rows for all combinations of the values in all the dimensions. These large result sets may contain too much data to be easily read and understood. One solution to this problem is to put the SELECT statement into a view:
CREATE VIEW InvCube AS
SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'
ELSE ISNULL(Item, 'UNKNOWN')
END AS Item,
CASE WHEN (GROUPING(Color) = 1) THEN 'ALL'
ELSE ISNULL(Color, 'UNKNOWN')
END AS Color,
SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH CUBE
The view can then be used to query only the dimension values of interest:
SELECT *
FROM InvCube
WHERE Item = 'Chair'
AND Color = 'ALL'
Item Color QtySum
-------------------- -------------------- --------------------------
Chair ALL 311.00
(1 row(s) affected)