Skip Headers
Oracle® OLAP DML Reference
11g Release 1 (11.1)

Part Number B28126-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

AGGREGATION

Within a model, the AGGREGATION function allows you to create a model that represents a custom aggregate. Such an aggmap can be used for dynamic aggregation with the AGGREGATE function.

Note:

Because the AGGREGATION function is intended only for dynamic aggregation, a model that contains such a function cannot be used with the AGGREGATE command.

Syntax

AGGREGATION(dimval-list)

Arguments

dimval-list

A list of one or more dimension values to include in the custom aggregation. The specified values must belong to the same dimension to which the target dimension value belongs. You must specify each dimension value as a text literal. That is, they cannot be represented by a text expression such as a variable.

Examples

Example 7-9 Using the AGGREGATION Function to Create a Custom Aggregate

The following lines of code from a program perform these steps:

  1. Add the new dimension value my_time to the time dimension.

    MAINTAIN time ADD 'My_Time'
    
    
  2. Define the model mytime_custagg and set the specification of the model using the AGGREGATION function.

    DEFINE mytime_custagg MODEL
    MODEL JOINLINES('DIMENSION time' 'My_Time = AGGREGATION(\'23\' \'24\')')
    
    

    (Note that backslash escape characters are required to include quotation marks within a quoted string.)

  3. Define the sales_aggmap aggmap.

    DEFINE sales_aggmap AGGMAP <time cpc <customer product channel> >
    AGGMAP
    RELATION prntrel.time
    RELATION prntrel.chan
    RELATION prntrel.prod
    RELATION prntrel.cust
    END
    
    
  4. Add the model mytime_custagg to sales_aggmap.

    AGGMAP ADD mytime_custagg TO sales_aggmap
    
    
  5. Limit the dimensions to the values of interest and run a report. 

    " Run a report
    LIMIT time TO 'My_Time' '23' '24'
    LIMIT channel TO '5'
    LIMIT product TO '70'
    LIMIT customer TO '114'
    REPORT DOWN time AGGREGATE(sales USING sales_aggmap)
    
    

    The report generates the following output.

    CHANNEL: 5
    PRODUCT: 70
                   --AGGREGATE(SALES---
                   USING SALES_AGGMAP)-
                   ------CUSTOMER------
    TIME                   114
    -------------- --------------------
    my_time                  682,904.34
    23                        84,982.92
    24                       597,921.42