Summarizing Data

Producing summary reports of aggregated transaction data for decision support systems can be a complex and resource-intensive operation. Microsoft® SQL Server™ 2000 provides two flexible and powerful components for building SQL Server 2000 Analysis 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 DTS Overview.

• Microsoft SQL Server Analysis Services

  Analysis 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. Analysis Services also provides a set of wizards for defining the multidimensional structures used in the Analysis processing, and a Microsoft Management Console snap-in for administering the Analysis structures. Applications can then use either the OLE DB for Analysis API or the Microsoft ActiveX® Data Objects (Multidimensional) (ADO MD) API to analyze the Analysis data. For more information, see Analysis Services Overview.

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 Analysis Services instead of CUBE, ROLLUP, COMPUTE, or COMPUTE BY. CUBE and ROLLUP should be reserved for environments that do not have access to OLE DB or ADO, such as scripts or stored procedures.

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.