Changing things up a bit this week, as I recently learned some cool Oracle features in a class.

Examples using the grouping extensions CUBE, ROLLUP, and GROUPING SETS based on the Oracle Sales History data warehouse. These are commonly referred to as multi-dimensional aggregation techniques.

These grouping extensions are widely used in analytical reporting and data summarization. They were recently introduced in a graduate-level database programming course. Despite spending 20 years in the database field without directly using them, I can now think of several situations where they would have been handy.

ROLLUP and CUBE were introduced in Oracle8i, and GROUPING SETS followed in Oracle9i, so these features have been part of Oracle SQL for quite some time.

There are three key distinctions:

• CUBE – generates all possible combinations of the grouping columns
• ROLLUP – generates hierarchical combinations along a drill-down path
• GROUPING SETS – generates user-defined combinations

Oracle Data Warehouse: Sales History

CUBE Extension:

Behavior:

• Creates all possible combinations of the specified grouping columns, producing a fully multidimensional aggregation.
  • Subtotals across every combination of dimensions, along with grand total.
  • No hierarchy implied, every column treated independently

Use Case:

• Multi-dimensional analysis across multiple dimensions.
• Exploring cross-tab style summaries:
  • Totals by product, by time, by region, and all combinations.
  • OLAP-style reporting and multi-axis summaries.
  • Used when every combination of attributes may reveal patterns.

Grouping Method:

• Number of groupings is: 2ⁿ | n = number of fields.
• Ex: Category, Year, Quarter = 2³ = 8
• Groupings:
  • (Category, Year, Quarter)
  • (Category, Year)
  • (Category, Quarter)
  • (Year, Quarter)
  • (Category)
  • (Year)
  • (Quarter)
  • () = ALL = Grand Totals

SQL Example:

Results:

Observations:

• All combinations aggregated
• Non-hierarchical

ROLLUP Extension:

Behavior:

• Creates hierarchical subtotals along a single drill-down path.
  • Subtotals are produced beginning with the most granular grouping to grand totals.

Use Case:

• Creates progressive aggregations (hierarchies): Year à Quarter à Month.
• Any natural parent-child relationship where subtotals follow a linear progression.

Grouping Method:

• Number of groupings:  n + 1 | n = number of fields.
  • Ex:
    • Category, Year, Quarter = 3 = 3 + 1 = 4
    • Groupings:
      • (Category, Year, Quarter)
      • (Category, Year)
      • (Category)
      • () = ALL = Grand Total

SQL Example:

Results:

Observations:

• ROLLUP does not control the position of grand totals in a result set.
  • Notice the use of GROUPING() in the ORDER BY clause; GROUPING() or GROUPING_ID() may be used to make sure the subtotal rows are ordered correctly.

GROUPING SETS:

Behavior:

• For use in fine-grained control of grouping set combinations.
• No hierarchy or full combinatorial expansion is implied.

Use Case:

• When fine-grained, custom grouping definitions are required.
  • ROLLUP produces too many or not the right combinations.
  • CUBE produces far too many combinations.
• Reporting that requires non-hierarchical or non-symmetric combinations of subtotal patterns.

Grouping Method:

• The number of sets, n, is defined by the number of explicitly defined sets.
• Example:
  • Leveraging the identical three attributes: Category, Year, Quarter(Category, Year, Quarter)(Category, Year)(Year)

SQL:

Results (2019):