Once you have created tables and indexes in your data warehouse, extracted, loaded, and processed data, it is time to begin analyzing the data you have stored. Microsoft offers several tools within SQL Server as well as applications external to SQL Server such as Microsoft Access, Visual Basic, Visual C++, and Internet Explorer that you can use to query your DSS database.
The CUBE operator is an additional switch in the GROUP BY clause in the SELECT statement.
The CUBE operator is used with an aggregate function to generate additional rows in a result set. Columns included in the GROUP BY clause are cross-referenced to produce a superset of groups. The aggregate function specified in the select_list is applied to these groups to produce summary values for the additional super-aggregate rows. The number of extra groups in the result set is determined by the number of columns included in the GROUP BY clause.
The aggregate function used in the SELECT statement is applied to the cross-referenced columns for the super-aggregate calculation. The CUBE operator can be applied to all aggregate functions, including AVG, SUM, MAX, MIN, and COUNT.
Every possible combination of the columns or expressions in the GROUP BY clause is used to produce super-aggregates. If you have n columns or expressions, there are 2 (n) -1 possible super-aggregate combinations. Mathematically, these combinations form an n-dimensional cube, which is how the operator got its name.
The ROLLUP operator is an aggregate operator that delivers aggregates and super-aggregates for elements within a GROUP BY statement. The ROLLUP operator is applicable to cumulative aggregates such as running sums or running averages. It differs from the CUBE operator only in that it is sensitive to the column's position in the GROUP BY clause. Aggregate groupings are made up of columns to the right of the current column value.
The ROLLUP operator can be used by report writers to extract statistics and summary information from result sets. The cumulative aggregates, such as running sums, can be used in reports, charts, and graphs.
The ROLLUP operator creates groupings by moving in only one direction, from right to left, along the list of columns in the GROUP BY clause. It then applies the aggregate function to these groupings. The CUBE operator creates all combinations of groupings from the list of columns in the GROUP BY clause.
SQL Workbook Publisher, included on the BackOffice Resource Kit CD-ROM, is a Microsoft Excel 97 add-in that allows users to analyze data stored in SQL Server databases on Excel spreadsheets.
SQL Workbook Publisher is a complete workgroup solution. Components are installed on the computer running Excel as well as on the computer running
SQL Server, and you can optionally install components to enable your corporate intranet for viewing saved workbooks.