SQL Server Magazine

Table 6, page 34, shows the output from this query. You now have a result set for every combination of product, country, and language. To save this CUBE for future queries, you could turn the query into a VIEW or save the results as a new table. Here's how to save the results into a new table called all_combinations:

SELECT Units = SUM(units_sold),
	Product = CASE    WHEN (GROUPING(product)=1) THEN 'ALL'
    	ELSE ISNULL(product, 'N/A')
    	END,
	Country = CASE    WHEN (GROUPING(country)=1) THEN 'ALL'
    	ELSE ISNULL(country, 'N/A')
    	END,
	Language = CASE    WHEN (GROUPING(language)=1) THEN 'ALL'
    	ELSE ISNULL(language, 'N/A')
    	END
	INTO all_combinations
FROM product_sales2 
	GROUP BY product, country, language 
WITH CUBE

You can use this table to answer any question about this data. For example, if you wanted to know how many Spanish Excel products were sold in all countries, you could use the following query:

SELECT units
FROM all_combinations
WHERE language = 'Spanish'
  and product = 'Excel'
  and country = 'ALL'

Rolling Up the CUBE with ROLLUP
The CUBE option to GROUP BY is powerful, but this option might generate many more result rows than you want, making it hard to find the information you're really looking for. If you use CUBE, you'll get all permutations, including superaggregates, for all attributes for which corresponding data exists. SQL Server provides the ROLLUP option as an alternative to CUBE. ROLLUP returns only the values for a hierarchy of the attributes you specify.

For example, if you change the CUBE query to ROLLUP instead, the results are more compact and possibly easier to interpret when you drill into details for sales by product. Here is the query with ROLLUP:

SELECT Units = SUM(units_sold),
Product = CASE    WHEN (GROUPING(product)=1) THEN 'ALL'
    ELSE ISNULL(product, 'N/A')
    END,
Country = CASE    WHEN (GROUPING(country)=1) THEN 'ALL'
    ELSE ISNULL(country, 'N/A')
    END,
Language = CASE    WHEN (GROUPING(language)=1) THEN 'ALL'
    ELSE ISNULL(language, 'N/A')
    END
FROM product_sales2 
GROUP BY product, country, language 
WITH ROLLUP

The results of this query are much more compact than the full CUBE output. Although the results of the ROLLUP query won't answer every possible question that you can answer with CUBE, you obtain more information than with a simple GROUP BY. (For example, the previous ROLLUP query won't answer, "How many French products were sold?")The ROLLUP query results are similar to the results of the T-SQL COMPUTE BY operation, which generates something similar to a control-break report. The significant difference between ROLLUP and COMPUTE BY is that ROLLUP produces relational output, which you can save as a view or insert into another table. The COMPUTE BY results are not relational, and the extra nonrelational rows returned with that operation are difficult for many client tools to handle.

The choice between CUBE or ROLLUP depends on how deeply you want to drill down. If you want to save the results in a new table, use CUBE so that you have all possible combinations when you need them. Most of the work of generating the CUBE is in the GROUP BY operation. The additional work SQL Server requires to produce all combinations of grouped values usually will be negligible. In other words, if you're going to GROUP BY multiple columns, adding either CUBE or ROLLUP to the query doesn't cost much in performance overhead.

Simple Operations, Big Results
One benefit of a data warehouse is having large amounts of data available for OLAP and decision-support activities. If you don't have SQL Server 7.0's data warehousing capabilities, you can use SQL Server 6.5's CUBE and ROLLUP extensions to summarize large amounts of data. You need only T-SQL queries without additional tools or add-on products. And after you've produced a cube, you can ask any questions about any combination of the grouped data, without running additional queries. Even if you do have SQL Server 7.0, using CUBE and ROLLUP offloads some of the processing to SQL Server and lets you obtain data warehousing aggregations within SQL Server procedures.

End of Article

Prev. page     1 2 [3]     next page -->