Summarizing Data

Producing summary reports of aggregated transaction data for decision support systems can be a complex and resource-intensive operation. Microsoft® SQL Server™ provides two flexible and powerful components for building OLAP services. These components are the main tools programmers should use in performing multidimensional analysis of SQL Server data:

• Data Transformation Services (DTS)

  DTS supports extracting transaction data and transforming it into summary aggregates in a data warehouse or data mart. For more information, see Overview of Data Transformation Services.

• Microsoft OLAP Services

  Microsoft SQL Server OLAP Services organizes data from a data warehouse into multidimensional cubes with precalculated summary information to provide rapid answers to complex analytical queries. PivotTable® Service provides client access to multidimensional data. OLAP Services also provides a set of wizards for defining the multidimensional structures used in the OLAP processing, and a Microsoft Management Console snap-in for administering the OLAP structures. Applications can then use either the OLE DB for OLAP Services API or the ActiveX Data Objects Multidimensional (ADO MD) API to analyze the OLAP data. For more information, see Microsoft SQL Server OLAP Services.

Using Transact-SQL for Simple Summary Reports

Applications generating simple summary reports can use these Transact-SQL elements:

• The CUBE or ROLLUP operators, which are both part of the GROUP BY clause of the SELECT statement.
• The COMPUTE or COMPUTE BY operators, which are also associated with GROUP BY.

These operators generate result sets that contain both detail rows for each item in the result set and summary rows for each group showing the aggregate totals for that group. The GROUP BY clause can be used to generate results that contain aggregates for each group, but no detail rows.

It is recommended that applications use OLAP Services instead of CUBE, ROLLUP, COMPUTE, or COMPUTE BY. CUBE and ROLLUP should be reserved for environments, such as scripts or stored procedures, that do not have access to OLE DB or ADO.

COMPUTE and COMPUTE BY are supported for backward compatibility. The ROLLUP operator is preferred over either COMPUTE or COMPUTE BY. The summary values generated by COMPUTE or COMPUTE BY are returned as separate result sets interleaved with the result sets returning the detail rows for each group, or a result set containing the totals appended after the main result set. Handling these multiple result sets increases the complexity of the code in an application. Neither COMPUTE nor COMPUTE BY are supported with server cursors, and ROLLUP is. CUBE and ROLLUP generate a single result set containing embedded subtotal and total rows. The query optimizer can also sometimes generate more efficient execution plans for ROLLUP than it can for COMPUTE and COMPUTE BY.

When GROUP BY is used without these operators, it returns a single result set with one row per group containing the aggregate subtotals for the group. There are no detail rows in the result set.