Skip Headers

Oracle® OLAP DML Reference
10g Release 1 (10.1)

Part Number B10339-02
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page

Go to previous page
Go to next page
View PDF


The ROLLUP command calculates totals for a hierarchy of values where each level of the hierarchy is an aggregation of the values in the level below it.

The ROLLUP command only performs simple sum aggregation. Additionally, it only aggregates data when the members of the hierarchy are contained in a single rollup or "embedded-total" dimension, so called because it contains both a detail (lowest) level and levels that are aggregations of lower levels. A relation between the embedded-total dimension and itself, called a "parent relation," specifies the arrangement of the hierarchy. For each value of the dimension, the parent relation contains the value that is immediately above it in the hierarchy (that is, its "parent" value).

Before using ROLLUP, make sure that the data variable that is dimensioned by the embedded-total dimension has data for the lowest-level values in the hierarchy. ROLLUP uses the data at the lowest level to calculate the totals for the higher levels.


Most applications aggregate data using an aggmap object rather than using the ROLLUP command. Aggmap objects allow you to write complex aggregation specifications. See "Aggregations" for more information.


ROLLUP data [OVER embed-tot-dim] [USING parent-rel] [ZEROFILL]



A numeric variable whose values are to be rolled up. When the variable has more than one dimension, one of them must be the OVER dimension.

OVER embed-tot-dim

A dimension of data whose values form a hierarchy. When data has only one dimension, then that dimension is the OVER dimension by default and you can omit the OVER phrase.

USING parent-rel

A relation between the OVER dimension and itself, called a parent relation, that specifies a hierarchy among the dimension values. For each dimension value, this parent relation specifies another value of the dimension which is its immediate parent. The parent relation holds NAs for the values at the highest level of the hierarchy. When there is more than one relation between the OVER dimension and itself, then you must specify the relation you want to use as the parent relation.


Specifies that parent totals should be set to zero when all of their child values are NA. When you do not specify ZEROFILL, ROLLUP sets parent totals to NA when all of their child values are NA.


Generation Levels in a Parent Relation

In the hierarchy specified by the parent relation, you can think of the lowest level as the "child" values, all the other values as "parents," and each level as a "generation." The relation specifies the parent at the next higher level for each dimension value. The following example shows the values of an embedded-total rollup dimension called area, that has three levels, and the values of the child-parent relation area.area.

             ----------  ----------
Level 1 ->   Totalus     NA
      2 ->   East        Totalus
      3 ->   Boston      East
      3 ->   Newyork     East
      2 ->   South       Totalus
      3 ->   Atlanta     South

A hierarchy can consist of several trees, so that there is more than one value at level 1. The value of the relation will be NA for all level-1 values, because these values have no parent in the hierarchy.

Including Child Values in the Status List

ROLLUP always looks to the lowest level of a hierarchy to calculate results. It rolls up only from the child values that are in the status list for the rollup dimension, but it still rolls up through all the levels in the hierarchy.

For example, suppose you use the area dimension and the area.area child-parent relation described in "Generation Levels in a Parent Relation", and you change the data value for NewYork. When you then roll up with only the child values for East in the status list (Boston and NewYork), the rollup occurs without including the child value for South (Atlanta), but still includes level 2 as it goes from level 3 to level 1 (TotalUS). When you want all the child values included in rolling up to TotalUS, you must explicitly include all of them in the status list. In the example, you would limit area and add Atlanta to the status list.

Rolling Up from Changed Child Values

When the data has changed for some, but not all, of the child values in the embedded-total rollup dimension, you can set the status to roll up just the values that have changed. For example, assume your embedded-total dimension is called d2, and its parent relation is called reld2, first limit d2 to the values that have changed. Then use the following statements to add the appropriate additional values to the status list.


Non-Rollup Dimensions

When the data variable being rolled up has more than one dimension, then the dimensions other than the rollup dimension are treated "normally." ROLLUP loops over their status and repeats the aggregation for each of their values.

Generation-Skipping Hierarchies

ROLLUP automatically distinguishes between generations in the parent relation, even to the extent of allowing generation-skipping hierarchies. For example, you can have a four-level hierarchy (for example, neighborhoods, cities, states, and total U.S.) that has a three-level branch (for example, Boston, Massachusetts, and total U.S.).

Status of the Rollup Dimension

Because ROLLUP automatically distinguishes parent values from child values, you can have all the values of the embedded-total rollup dimension in the status list when you execute ROLLUP.


When a variable includes a composite in its dimension list, you cannot roll up the data over the composite. However, you can roll up data over a base dimension of the composite. You specify which base dimension to loop over with the OVER keyword. The parent relation must be a relation between the base dimension and itself. When the composite is missing a value that is required for the rollup, ROLLUP will create the missing value.

Converting a Composite to a Conjoint Dimension

When your data is sparse, it is usually better to define a variable with a composite than with a conjoint dimension. However, in some situations you might find it advantageous to convert the composite to a conjoint dimension before you execute a ROLLUP command. When a variable is dimensioned by a conjoint dimension, you can define a parent relation between the conjoint dimension and itself and roll up the data over the conjoint dimension.

To convert a composite to a conjoint dimension, use the CHGDFN command.

Conjoint Dimensions

When a variable is dimensioned by a conjoint dimension, you can roll up the data over the conjoint dimension, but you cannot roll up over a base dimension of the conjoint. The parent relation must be a relation between the conjoint dimension and itself.

Multidimensional Parent Relations

You can have a multidimensional parent relation that defines more than one hierarchy, so that child values contribute to more than one higher-level total. However, at each level, the hierarchies should each point to a separate higher-level total, so that the data is not counted more than once at the higher level. See Chapter 21, "Using a Multidimensional Relation".


A multidimensional parent relation cannot share any dimensions with the data variable other than the embedded-total dimension.

Avoiding Circular Hierarchies

The parent relation must not create a circular hierarchy. That is, the relation must not contain any dimension values that are their own parent, either directly or indirectly. A parent relation that creates a circular hierarchy would put the calculations of ROLLUP into an infinite loop. In your application, you should ensure that your hierarchies are not circular. To do so, use the HIERCHECK program to check every parent relation in your analytic workspace for circularity. You can use HIERCHECK either as a command or as a function.

Improving Performance

When you feel that the ROLLUP command is taking longer than expected, consider the following strategies:


Example 21-7 Rolling up Sales Data

This example illustrates the use of ROLLUP. You can create an embedded-total geography dimension by combining the values in the district and region dimensions. Another value, TotalUS, is the parent of the regions. The order of the dimension values does not matter because the parent relation (that you define later) provides the parent-child information. After the following statements are executed.

MAINTAIN geography ADD 'TotalUS' VALUES(region) -
REPORT geography

The following report is created.


Next, create the child-parent relation, geog.geog, which is the relation between geography and itself.

DEFINE geog.geog RELATION geography <geography>

Each value of the geog.geog relation should be the parent of the corresponding geography value. You can add the values that are shown in the following report.

-------------- ----------
TotalUS        NA
East           TotalUS
Central        TotalUS
West           TotalUS
Boston         East
Atlanta        East
Chicago        Central
Dallas         Central
Denver         West
Seattle        West

Finally, you can define a variable, g.units, for the data that is currently held in the units variable plus the totals for the higher levels. After limiting geography to the values of district, you can transfer the units data to g.units and use ROLLUP to fill in the totals.

DEFINE g.units INTEGER <month product geography>
LIMIT geography TO VALUES(district NOSTATUS)
g.units = UNRAVEL(units)
ROLLUP g.units OVER geography USING geog.geog
LIMIT geography TO ALL
LIMIT product TO 'Tents'
LIMIT month TO 'Jan95' TO 'Jul95'
REPORT W 9 DOWN geography W 9 geog.geog ACROSS month: W 5 -

The preceding statements produce the following output.

GEOGRAPHY GEOG.GEOG Jan95 Feb95 Mar95 Apr95 May95 Jun95 Jul95 
--------- --------- ----- ----- ----- ----- ----- ----- -----
TotUS     NA        1,429 1,440 1,860 2,534 3,378 3,779 4,058
East      TotUS       453   479   589   848 1,092 1,248 1,315
Central   TotUS       478   494   666   848 1,137 1,247 1,360
West      TotUS       498   467   605   838 1,149 1,284 1,383
Boston    East        200   203   269   359   507   556   545
Atlanta   East        253   276   320   489   585   692   770
Chicago   Central     181   181   247   304   416   443   461
Dallas    Central     297   313   419   544   721   804   899
Denver    West        227   210   283   358   497   573   642
Seattle   West        271   257   322   480   652   711   741

Example 21-8 Using a Multidimensional Relation

In this example, we have defined a new dimension called area that includes the values in the geography dimension that was created in "Rolling up Sales Data". In addition, area includes European and Asian regions and countries that roll up into these regions.

There is also a multidimensional parent relation named area.area that defines two hierarchies. The relation area.area has area for one of its dimensions, while its other dimension, hier, holds a list of hierarchies. One of these hierarchies, Nation, specifies continental and global totals. The second hierarchy, Corporate, divides the child values into divisions and groups of divisions. When an area value is not part of a hierarchy, area.areahier has an NA value for that area. The area.arearelation also has an NA for the top-level area in each hierarchy, since the top level has no parent value. Executing a DESCRIBE area HIER a.ah statement

shows the following definitions that have been created in the analytic workspace.

DEFINE area.area RELATION area <area hier>

Assume that a REPORT DOWN area W 20 area.area statement executes.

The resulting report shows the values of the multidimensional parent relation area.area.

AREA                  NATION              CORPORATE
-------------- -------------------- --------------------
Global         NA                   NA
GroupI         NA                   Global
GroupII        NA                   Global
DivI           NA                   GroupI
DivII          NA                   GroupI
DivIII         NA                   GroupII
TotalUS        Global               NA
TotInternation Global               NA
TotalEurope    TotInternation       NA
Germany        TotalEurope          Divii
England        TotalEurope          DivI
Spain          TotalEurope          Diviii
France         TotalEurope          Diviii
TotalAsia      TotInternation       NA
India          TotalAsia            DivI
Malaysia       TotalAsia            Diviii
East           TotalUS              NA
Central        TotalUS              NA
West           TotalUS              NA
Boston         East                 DivI
Atlanta        East                 DivI
Chicago        Central              DivI
Dallas         Central              DivI
Denver         West                 DivI
Seattle        West                 DivI

The analytic workspace also contains a variable named a.units that has area as one of its dimensions. Its definition is as follows.

DEFINE a.units INTEGER <month product area>

After data has been loaded into a.units for the lowest level areas (the districts in the United States and the countries of Europe and Asia), you can execute the following ROLLUP command to roll up the data and fill in the totals in the a.units variable. The command rolls up data over the area dimension, using the multidimensional parent relation area.area. This aggregates data both in the Nation hierarchy and in the Corporate hierarchy.

ROLLUP a.units OVER area USING area.area

When you use the following statements to produce a report of the a.units data, the data for each of the two hierarchies (Nation and Corporate) will be shown on separate pages of the report.

FOR hier
   LIMIT area TO a.ah NE NA
   LIMIT area ADD 'Global'
   REPORT DOWN area W 14 area.area ACROSS month: W 7 a.units