Cube usage in SQL

The result set generated by the cube operator is a multi-dimensional dataset. A multi-dimensional dataset is an extension of fact data (that is, data that records individual events. Extensions are created based on the columns you want to analyze. These columns are called dimensions. A multi-dimensional dataset is a result set that contains all possible combinations of cross tables for each dimension.

The cube operator is specified in the group by clause of the SELECT statement. The statement selection list contains dimension columns and aggregate function expressions. Group by specifies the dimension column and keyword with cube. The result set contains all possible combinations of values in the dimension column and the aggregated values in the basic rows that match the combination of these dimension values.

Cube returns more possible combinations. If there are n columns or N expressions in the group by clause, sqlserver will return a possible combination of N-1 power 2 in the result set.

Note:

The cube operator can contain up to 10 grouping expressions.

The All keyword cannot be used in cube.

Example (from msdn)

For example, a simple tableInventoryContains the following data:

Item                 Color                Quantity                   -------------------- -------------------- -------------------------- Table                Blue                 124                        Table                Red                  223                        Chair                Blue                 101                        Chair                Red                  210                        

The following query returns a result set, includingItemAndColorAll possible combinationsQuantitySubtotal:

SELECT Item, Color, SUM(Quantity) AS QtySumFROM InventoryGROUP BY Item, Color WITH CUBE

The following 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                     

We focus on the following rows in the result set:

Chair                (null)               311.00                     

This line reports onItemDimensions includeChairThe subtotal of all rows of the value. PairColorDimension returnednullValue to indicate that the aggregation of the report for this row includesColorThe row whose dimension is any value.

Table                (null)               347.00                     

This line is similar, but the report isItemDimensions includeTableThe subtotal of all rows of the value.

(null)               (null)               658.00                     

This row reports the total number of multidimensional datasets.ItemAndColorAll dimensions containnullValue. This indicates that all values of these two dimensions are collected in this row.

(null)               Blue                 225.00                     (null)               Red                  433.00                     

The two rows reportedColorSubtotal of a dimension. In the two rowsItemAll dimension valuesnullIndicates that the aggregated data comes fromItemThe row whose dimension is any value.

Use grouping to differentiate null values

When the cube operation generates a null value, the following question occurs: how to distinguish between the null value generated by the cube operation and the null value returned in actual data? You can use the grouping function to solve this problem. If the column value comes from fact data, the grouping function returns 0. If the column value is null generated by the cube operation, 1 is returned. In the cube operation, the generated null represents all values. You can write a SELECT statement to replace any null generated with the string all using the grouping function. Since null in the fact data indicates that the data value is unknown, you can encode the SELECT statement as the returned string unknown to indicate null in 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 QtySumFROM InventoryGROUP BY Item, Color WITH CUBE

Multi-dimensional dataset

The cube operator can be used to generateNA Multidimensional Dataset with any dimension. Only one 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 QtySumFROM InventoryGROUP BY Item WITH CUBEGO

ThisSELECTThe result set returned by the statement is displayedItemReturns the subtotal of each value inItemTotal of all values in:

Item                 QtySum                     -------------------- -------------------------- Chair                311.00                     Table                347.00                     ALL                  658.00                     

Select statements that contain cubes with multiple dimensions can generate a large result set, because these statements generate corresponding rows for all combinations of values in all dimensions. These large result sets may contain too much data and are not easy to read and understand. One solution to this problem isSELECTStatement into the View:

CREATE VIEW InvCube ASSELECT 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 QtySumFROM InventoryGROUP BY Item, Color WITH CUBE

Then you can use this view to query only the dimension values you are interested in:

SELECT *FROM InvCubeWHERE Item = 'Chair'  AND Color = 'ALL'Item                 Color                QtySum                     -------------------- -------------------- -------------------------- Chair                ALL                  311.00                     (1 row(s) affected)