Intelligent Enterprise

Grouping capabilities are critical to OLAP functionality. ROLLUP and CUBE are extensions of the GROUP BY clause. The functionalities of ROLLUP and CUBE are often referred to as supergroups.

• A ROLLUP group is an extension to the GROUP BY clause that produces a result set that contains subtotal rows in addition to the "regular" grouped rows. Subtotal rows are superaggregate rows that contain further aggregates whose values are derived by applying the same column functions that were used to obtain the grouped rows. A ROLLUP grouping is a series of grouping-sets:
  
  GROUP BY ROLLUP (a,b,c) is equivalent to:
  
  GROUP BY GROUPING SETS
(
(a,b,c)
(a,b)
(a)
()
)

Notice that the n elements of the ROLLUP translate to an n+1 grouping set. Another point to remember is that the order in which the grouping expressions are specified is significant for ROLLUP.

• The CUBE supergroup is the other extension to the GROUP BY clause that produces a result set that contains all the subtotal rows of a ROLLUP aggregation and, in addition, contains "cross-tabulation" rows. Cross-tabulation rows are additional superaggregate rows. They are, as the name implies, summaries across columns as if the data were represented as a spreadsheet. Like ROLLUP, a CUBE group can also be thought of as a series of grouping-sets. In the case of a CUBE, all permutations of the cubed grouping expression are computed along with the grand total. Therefore, the n elements of a CUBE translate to 2n grouping-sets.
  
  GROUP BY CUBE (a,b,c) is equivalent to:
  
  GROUP BY GROUPING SETS
(
(a,b,c)
(a,b)
(a,c)
(b,c)
(a)
(b)
(c)
()
)
  
  Notice that the three elements of the CUBE translate to eight grouping sets. Unlike ROLLUP, the order of specification of elements doesn't matter for CUBE: CUBE (DayOfYear, Sales_Person) is the same as CUBE (Sales_Person, DayOfYear).
  
  CUBE is an extension of the Rollup function. The CUBE function not only provides the column summaries we saw in rollup but also calculates the row summaries and grand totals for the various dimensions.

Aside from these functions, the ability to define a window is equally important to SQL's OLAP functionality. You use windows to define a set of rows over which a function is applied and the sequence in which it occurs. Another way to view the concept of a window is to equate it with the concept of a slice. In other words, a window is simply a slice of the overall data domain.