Summarizing Data
Producing summary reports of aggregated transaction data for decision support systems can be a complex and resource-intensive operation. The following components are the main tools that programmers should use in performing multidimensional analysis of SQL Server data:
SQL Server Integration Services
Integration Services supports extracting transaction data and transforming it into summary aggregates in a data warehouse or data mart. For more information, see Overview (Integration Services).
SQL Server Analysis Services
Analysis Services organizes data from a data warehouse into multidimensional cubes with precalculated summary information. This information is used 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 that are used in the Analysis processing, and a Microsoft Management Console snap-in for administering the Analysis structures. Applications can then use a set of APIs to analyze the Analysis data. For more information, see Developing and Implementing Using Business Intelligence Development Studio.
Using Transact-SQL for Simple Summary Reports
Applications generating simple summary reports can use the following Transact-SQL elements:
The ROLLUP, CUBE, or GROUPING SETS operators. These are extensions to the GROUP BY clause of the SELECT statement. For more information, see.GROUP BY (Transact-SQL).
The COMPUTE or COMPUTE BY operators. These are also associated with GROUP BY. For more information, see Summarizing Data Using COMPUTE and COMPUTE 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.
Applications should use Analysis Services instead of CUBE, ROLLUP, COMPUTE, or COMPUTE BY. Specifically, 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 that return the detail rows for each group, or a result set that contains 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 is supported with server cursors. However, ROLLUP is supported with server cursors. CUBE and ROLLUP generate a single result set that contains 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 that contains the aggregate subtotals for the group. There are no detail rows in the result set.