User Tools

Site Tools


concepts:roll-up
  • Bookmark "Pre-aggregation (roll-up)" at del.icio.us
  • Bookmark "Pre-aggregation (roll-up)" at Digg
  • Bookmark "Pre-aggregation (roll-up)" at Furl
  • Bookmark "Pre-aggregation (roll-up)" at Reddit
  • Bookmark "Pre-aggregation (roll-up)" at Ask
  • Bookmark "Pre-aggregation (roll-up)" at Google
  • Bookmark "Pre-aggregation (roll-up)" at Netscape
  • Bookmark "Pre-aggregation (roll-up)" at StumbleUpon
  • Bookmark "Pre-aggregation (roll-up)" at Technorati
  • Bookmark "Pre-aggregation (roll-up)" at Live Bookmarks
  • Bookmark "Pre-aggregation (roll-up)" at Yahoo! Myweb
  • Bookmark "Pre-aggregation (roll-up)" at Facebook
  • Bookmark "Pre-aggregation (roll-up)" at Newsvine
  • Bookmark "Pre-aggregation (roll-up)" at Yahoo! Bookmarks
  • Bookmark "Pre-aggregation (roll-up)" at Twitter
  • Bookmark "Pre-aggregation (roll-up)" at myAOL
  • Bookmark "Pre-aggregation (roll-up)" at Slashdot
  • Bookmark "Pre-aggregation (roll-up)" at Mister Wong

Pre-aggregation (roll-up)

Normally data warehouses are populated a star schema, for example the sales contains each order for each day. It is easy to see the total for each order, but often it is required to support a higher level of details. For example could someone be interested in knowing the sum of units sold aggregated by region, month or so on. With the star schema these types of aggregation, will need a high response time, if it computed at runtime. The target should be to get an acceptable response time. Essentially, we can either aggregate at runtime or pre-aggregate the data offline, making the sums available without real-time computation. There is a simple alternative to real-time aggregation: Just write a SQL Statement to pre-aggregate the data according to the dimension that the end-user wants to see. 1) When facts are aggregated, it is done by eliminating dimensionality or by associating the facts with a rolled up dimension. Rolled up dimensions should be shrunken versions of the dimensions associated with the granular base facts. This way, the aggregated dimension tables should conform to the base dimension tables. The reason why aggregates can make such a dramatic increase in the performance of the data warehouse is the reduction of the number of rows to be accessed when responding to a query. 2)

SQL: ROLLUP Aggregate

ROLLUP performs these aggregations across multiple dimensions, at different levels, within a single SQL query. ROLLUP creates n+1 levels of subtotals (including a grand total). n is the number of grouping columns.

ROLLUP(A, B, C) creates four groups: (A, B, C), (A, B), (A), ().

ROLLUP ( (A, B), C) treats (A, B) as a single entity and only creates three groups: (A, B, C), (A, B), ()

Examples

SELECT * FROM something ORDER BY Category, Year

Year Category Amount
2009 DVDs 39.99
2010 DVDs 29.99
2009 Blurays 39.99
2011 Blurays 49.99

For the something table, ROLLUP computes the subtotals in each category.

 SELECT Category, Year, SUM(Amount) FROM something
 GROUP BY ROLLUP(Category, Year) ORDER BY 1,2, GROUPING_ID();
Category Year SUM
DVDs 2009 39.99
DVDs 2010 29.99
DVDs 69.98
Blurays 2009 39.99
Blurays 2010 49.99
Blurays 89.98
159,96

The following example shows how to use the HAVING clause with ROLLUP to restrict the GROUP BY results. The following query produces only those ROLLUP categories where year is subtotaled, based on the expression in the GROUPING function:

 SELECT Category, Year, SUM(Amount) FROM something 
 GROUP BY ROLLUP(Category,Year) HAVING GROUPING(Year)=1
 ORDER BY 1, 2, GROUPING_ID(); 
Category Year SUM
DVDs 69.98
Blurays 89.98
159,96

3)

1) Burleson Consulting: Data Warehouse Aggregation, Roll-Ups and Star Schemas - Stand 19.05.2016 http://www.dba-oracle.com/data_warehouse/star_schemas.htm
2) Kimball, Ralph & Ross, Margy: The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling:: WILEY 2013
3) Hewlett Packard Enterprise – Vertica: SQL-Reference-Manual – ROLLUP Clause - Stand 19.05.2016 https://my.vertica.com/docs/7.1.x/HTML/Content/Authoring/SQLReferenceManual/Statements/SELECT/ROLLUPClause.htm
  • Bookmark "Pre-aggregation (roll-up)" at del.icio.us
  • Bookmark "Pre-aggregation (roll-up)" at Digg
  • Bookmark "Pre-aggregation (roll-up)" at Furl
  • Bookmark "Pre-aggregation (roll-up)" at Reddit
  • Bookmark "Pre-aggregation (roll-up)" at Ask
  • Bookmark "Pre-aggregation (roll-up)" at Google
  • Bookmark "Pre-aggregation (roll-up)" at Netscape
  • Bookmark "Pre-aggregation (roll-up)" at StumbleUpon
  • Bookmark "Pre-aggregation (roll-up)" at Technorati
  • Bookmark "Pre-aggregation (roll-up)" at Live Bookmarks
  • Bookmark "Pre-aggregation (roll-up)" at Yahoo! Myweb
  • Bookmark "Pre-aggregation (roll-up)" at Facebook
  • Bookmark "Pre-aggregation (roll-up)" at Newsvine
  • Bookmark "Pre-aggregation (roll-up)" at Yahoo! Bookmarks
  • Bookmark "Pre-aggregation (roll-up)" at Twitter
  • Bookmark "Pre-aggregation (roll-up)" at myAOL
  • Bookmark "Pre-aggregation (roll-up)" at Slashdot
  • Bookmark "Pre-aggregation (roll-up)" at Mister Wong
concepts/roll-up.txt · Last modified: 2016/05/19 12:00 by schnabep