Previous
Previous
 
Next
Next


AGGMAP

The AGGMAP command identifies an aggmap object as a specification for aggregation and adds an aggregation specification to the definition of the current aggmap object. To use AGGMAP to assign an aggregation specification to n aggmap object, the definition must be the one most recently defined or considered during the current session. When it is not, you must first use a CONSIDER statement to make it the current definition.

An alternative to the AGGMAP command is the EDIT AGGMAP statement, which is available only in OLAP Worksheet. The EDIT AGGMAP statement opens an Edit window in which you can add, delete, or change the aggregation specification for an aggmap object.

(Note that there are two other OLAP DML statements that are also sometimes referred to as "AGGMAP statements": AGGMAP ADD or REMOVE model statement that you can use to add or remove a model from an aggmap object of type AGGMAP, and AGGMAP SET that you can use to specify the default aggmap for a variable.)

Syntax

AGGMAP [specification]

Parameters

specification

A multiline text expression that is the aggregation specification for the current aggmap object. Each statement is a line of the multiline text expression. When coding an AGGMAP command at the command line level, separate statements with newline delimiters (\n), or use JOINLINES.

An aggregation specification begins with AGGMAP and ends with an END. Between these statements, you code one or more the following statements depending on the calculation that you want to specify. Minimally, you must code one RELATION (for aggregation) statement.

AGGINDEX
BREAKOUT DIMENSION
CACHE
DIMENSION (for aggregation)
DROP DIMENSION
MEASUREDIM (for aggregation)
MODEL (in an aggregation)
PRECOMPUTE
RELATION (for aggregation)

Note:

You cannot specify a conjoint dimension in the specification for the aggmap; use composites instead.

Usage Notes

Creating Temporary or Custom Aggregates

Most aggmap objects are defined to calculate variable values that are dimensioned by permanent dimension members (that is, dimension members that persist from one session to another). However, users might want to create their own aggregates at run time for forecasting or what-if analysis, or just because they want to view the data in an unforeseen way. Adding temporary members to dimensions and aggregating data for those members is sometimes called creating temporary or custom aggregates. For example, you can use a MAINTAIN ADD SESSION statement like the one below to temporarily add a model to an aggmap object.

MAINTAIN dimension ADD SESSION member = model APPLY TO AGGMAP aggmap

Aggregating Variables Dimensioned by Compressed Composites

Keep the following points in mind when designing an aggregation specification for a variable dimensioned by a compressed composite:

Aggregation Options and System Properties

Several options can impact aggregation as outlined in "Aggregation Options".

See "System Properties by Category" for a list of system properties that relate to aggregation or allocation.

Checking for Circularity

AGGREGATE automatically checks relations for circularity in and among multiple hierarchies. When you first define hierarchies, check for circularity by setting PRECOMPUTE statements to NA and AGGINDEX to NO. A XSHIERCK01 error during aggregation indicates that a circular hierarchy may have been detected. However, when the message includes a reference to UNDIRECTED, then multiple paths to an ancestor from a detail data cell have been detected. Some calculations require that a detail data cell use multiple paths to the same ancestor cell. When this is the case, then you must set the MULTIPATHHIER option to YES before you execute the AGGREGATE command. Otherwise, you must correct the error in the hierarchy structure. For more details about this error message and how to interpret it, see the MULTIPATHHIER option.

Examples

Combining Pre-calculation and Calculation on the Fly

This example describes the steps you can take to pre-calculate some data in your analytic workspace and specify that the rest should be calculated when users request it.

Suppose you define an analytic workspace named mydtb that has a units variable with the following definition.

DEFINE units INTEGER <time, SPARSE <product, geography>>

You now must create and add a specification to the aggmap, which specifies the data that should be aggregated. This example shows you how to use an input file, which contains OLAP DML statements that define the aggmap and add a specification to it:

  1. Identify the name of each dimension's hierarchy. When you have defined the hierarchies as self-relations, you use the names of the self-relations.

  2. Decide which data to aggregate.

    Suppose you want to calculate data for all levels of the time and product dimensions, but not for geography. The geography dimension's lowest level of data is at the city level. The second level of the hierarchy has three dimension values that represent regions: East, Central, and West. The third level of the hierarchy has one dimension value: Total.

    Suppose that you want to pre-calculate the data for East and store it in the analytic workspace. You want the data for Central, West, and Total to be calculated only when users request that data — that data is not stored in the analytic workspace. Therefore, you must specify this information in the specification that you add to your aggmap object.

  3. Create an ASCII text file named units.txt. Add the following OLAP DML statements to your text file.

    DEFINE units.agg AGGMAP <time, SPARSE <product, geography>>
    AGGMAP
    RELATION myti.parent
    RELATION mypr.parent
    RELATION myge.parent PRECOMPUTE ('East')
    END
    

    The preceding statements define an aggmap named units.agg, then add the three RELATION statements to the aggregation specification when you read the units.txt file into your analytic workspace.

  4. To read the units.txt file into your analytic workspace, execute the following statement.

    INFILE 'inf/units.txt'
    
  5. The units.agg aggmap should now exist in your analytic workspace. You can aggregate the units variable with the following statement.

    AGGREGATE units USING units.agg
    

    Now the data for East for all times and products has been calculated and stored in the analytic workspace.

  6. Set up the analytic workspace so that when a user requests data for Central, West, or Total, that data is calculated and displayed. It is generally a good idea to compile the aggmap object before using it with the AGGREGATE function, as shown by the following statement.

    COMPILE units.agg
    

    This is not an issue when you are just using the AGGREGATE command, because this statement compiles the aggmap object before it uses it. However, when you do not use the FUNCDATA keyword with the AGGREGATE command, the metadata that is needed to perform calculation on the fly has not been compiled yet. If you have performed all other necessary calculations (such as calculating models), then it is a good practice to compile the aggmap when you load data. When you fail to do so, that means that every time a user opens the analytic workspace, that user has to wait for the aggregation to be compiled automatically. In other words, when any data is calculated on the fly, you can improve query performance for all of your users by compiling the aggmap before making the analytic workspace available to your users.

  7. Add a property to the units variable.

    CONSIDER units
    PROPERTY '$NATRIGGER' 'AGGREGATE(units USING units.agg)'
    

    This property indicates that when a data cell contains an NA value, Oracle OLAP calls the AGGREGATE function to aggregate the data for that cell. Therefore, any units data that is requested by a user displayed. However, only the data for the East dimension value of the geography dimension has actually been aggregated and stored in the analytic workspace. All other data (for Central, West, and Total) is calculated only when users request it.

Performing Non-additive Aggregation

This example shows how to use operators and arguments to combine additive and non-additive aggregation.

Suppose that you have defined four variables: sales, debt, interest_rate, and inventory. The variables have been defined with the same dimensionality where cp is a composite that has been defined with the product and geography dimensions.

<time cp<product geography>>

Suppose you want to use one AGGREGATE command to aggregate all four variables. The debt variable requires additive aggregation. The sales variable requires a weighted sum aggregation, and interest_rate requires a hierarchical weighted average. Therefore, both sales and interest_rate require a weight object, which you must define and populate with weight values. inventory requires a result that represents the total inventory, which is the last value in the hierarchy.

You specify the aggregation operation for debt and inventory with the OPERATOR keyword. However, because sales and interest_rate have aggregation operations that require weight objects, you must use the ARGS keyword to specify their operations. You define an operator variable to use the OPERATOR keyword. Typically, the operator variable is dimensioned by a measure dimension or a line item dimension.

Here are the steps to define the aggregation you want to occur:

  1. Because you are also using a measure dimension to define an argument variable to use with the ARGS keyword, define that measure dimension, as illustrated by the following statements.

    DEFINE measure DIMENSION TEXT
    MAINTAIN measure 'sales', 'debt', 'interest_rate', 'inventory'
    

    Note:

    Whenever you use a measure dimension in a RELATION statement, you must include a MEASUREDIM statement in the same aggregation specification

  2. Define an operator variable named opvar and populate it.The statements specify that the aggregation for debt should use the SUM operator, and the aggregation for inventory should use the HLAST operator.

    DEFINE opvar TEXT <measure>
    opvar (measure 'sales') = 'WSUM'
    opvar (measure 'debt') = 'SUM'
    opvar (measure 'interest_rate') = 'HWAVERAGE'
    opvar (measure 'inventory') = 'HLAST'
    
  3. Because sales and interest_rate require weight objects, define and populate those weight objects. The following statement defines a weight object named currency (to be used by sales).

    DEFINE currency DECIMAL <time geography>
    

    Notice that the currency variable is dimensioned only by time and geography. The purpose of this variable is to provide weights that act as currency conversion information for foreign countries; therefore, it is unnecessary to include the product dimension.

  4. Populate currency with the weight values that you want to use.

  5. The interest_rate variable's nonaddictive aggregation (hierarchical weighted average) requires the sum of the variable debt. In other words, interest_rate cannot be aggregated without the results of the aggregation of debt.

    You can now define an argument variable, which you must specify the aggregation results of debt as a weight object for interest_rate. You use the same argument variable to specify currency as the weight object for the sales variable. The following statement defines an argument variable named argvar.

    DEFINE argvar TEXT <measure>
    
  6. The next few statements populate the argument variable.

    argvar (measure 'sales') = 'weightby currency'
    argvar (measure 'debt') = NA
    argvar (measure 'interest_rate') = 'weightby debt'
    argvar (measure 'inventory') = NA
    
  7. For the aggregation of product and geography, the data for the sales, debt, and interest_rate variables can simply be added. But the inventory variable requires a hierarchical weighted average. Consequently, it is necessary to define a second operator variable and a second argument variable, both of which are used in the RELATION statement for product and geography.

    The following statements define the second operator variable and populate it.

    DEFINE opvar2 TEXT <measure>
    opvar (measure 'sales') = 'Sum'
    opvar (measure 'debt') = 'Sum'
    opvar (measure 'interest_rate') = 'Sum'
    opvar (measure 'inventory') = 'HWAverage'
    

    The following statements define the second argument variable and populate it.

    DEFINE argvar2 TEXT <measure>
    argvar (measure 'sales') = NA
    argvar (measure 'debt') = NA
    argvar (measure 'interest_rate') = NA
    argvar (measure 'inventory') = 'weightby debt'
    
  8. Now create the aggmap, by issuing the following statements.

    DEFINE sales.agg AGGMAP <time, CP<product geography>>
    AGGMAP
    RELATION time.r OPERATOR opvar ARGS argvar
    RELATION product.r OPERATOR opvar2 ARGS argvar2
    RELATION geography.r OPERATOR opvar2 ARGS argvar2
    MEASUREDIM measure
    END
    
  9. Finally, use the following statement to aggregate all four variables.

    AGGREGATE sales debt interest_rate inventory USING sales.agg
    

Programmatically Defining an Aggmap

The following program uses the EXISTS function to test whether an AGGMAP exists, and defines the AGGMAP when it does not. It then uses an AGGMAP statement to define the specification for the aggmap.

DEFINE MAKEAGGMAP PROGRAM
LD Create dynamic aggmap
PROGRAM
IF NOT EXISTS ('test.agg')
   THEN DEFINE test.agg AGGMAP <geography product channel time>
   ELSE CONSIDER test.agg
AGGMAP JOINLINES(-
   'RELATION geography.parentrel PRECOMPUTE (geography.lvldim 2 4)' -
   'RELATION product.parentrel' -
   'RELATION channel.parentrel' -
   'RELATION time.parentrel' -
   'END')
END

Creating an Aggmap Using an Input File

Suppose that you have created a disk file called salesagg.txt, which contains the following aggmap definition and specification.

DEFINE sales.agg AGGMAP <time, product, geography>
AGGMAP
RELATION time.r PRECOMPUTE (time NE 'Year99')
RELATION product.r PRECOMPUTE (product NE 'ALL')
RELATION geography.r
CACHE STORE
END

To include the sales.agg aggmap in your analytic workspace, execute the following statement, where inf is the alias for the directory where the file is stored.

INFILE 'inf/salesagg.txt'

The sales.agg aggmap has now been defined and contains the three RELATION statements and the CACHE statement. In this example, you are specifying that all of the data for the hierarchy for the time dimension, time.r, should be aggregated, except for any data that has a time dimension value of Year99. All of the data for the hierarchy for the product dimension, product.r, should be aggregated, except for any data that has a product dimension value of All. All geography dimension values are aggregated. The CACHE STORE statement specifies that any data that are rolled up on the fly should be calculated just once and stored in the cache for other access requests during the same session.

You can now use the sales.agg aggmap with an AGGREGATE command, such as.

AGGREGATE sales USING sales.agg

In this example, any data value that dimensioned by a Year99 value of the time dimension or an All value of the product dimension is calculated on the fly. All other data is aggregated and stored in the analytic workspace.

Using Multiple Aggmaps

When you use a forecast, you must ensure that all of the input data that is required by that forecast has been pre-calculated. Otherwise, the forecast uses incorrect or nonexistent data. For example, suppose your forecast requires that all line items are aggregated. Using a budget variable that is dimensioned by time, line, and division, one approach would be to perform a complete aggregation of the line dimension, forecast the dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR, and then aggregate the remaining dimension, division.

You can support this processing by defining three aggmap objects:

  1. Define the first aggmap, named forecast.agg1, which aggregates the data needed by the forecast. It contains the following statement.

    RELATION line.parentrel
    
  2. Define the second aggmap, named forecast.agg2, which aggregates the data generated using the first aggmap and the forecast. It contains the following statement.

    RELATION division.parentrel PRECOMPUTE ('L3')
    
  3. Define the third aggmap, named forecast.agg3, which contains the RELATION statements in the specifications of the first two aggmaps.

    RELATION line.parentrel
    RELATION division.parentrel PRECOMPUTE ('L3')
    

When your forecast is in a program named fore.prg, then you would use the following statements to aggregate the data.

AGGREGATE budget USING forecast.agg1   "Aggregate over LINE 
CALL fore.prg                          "Forecast over TIME
AGGREGATE budget USING forecast.agg2   "Aggregate over DIVISION
"Compile the limit map for LINE and DIVISION
COMPILE forecast.agg3
"Use the combined aggmap for the AGGREGATE function
CONSIDER budget
PROPERTY 'NATRIGGER' 'AGGREGATE(budget USING forecast.agg3)'

Using an AGGINDEX Statement in an Aggregation Specification

Suppose you have two variables, sales1 and sales2, with the following definitions.

DEFINE sales1 DECIMAL <time, SPARSE<product, channel, customer>>
DEFINE sales2 DECIMAL <time, SPARSE<product, channel, customer>>

You do not want to precompute and commit all of the sales data to the database, because disk space is limited and you must improve performance. Therefore, you must create an aggmap, in which you specify which data should be pre-computed and which data should be calculated on the fly.

You define the aggmap, named sales.agg, with the following statement.

DEFINE sales.agg AGGMAP <time, SPARSE<product, channel, customer>>

Next, you use an AGGMAP statement to enter the following specification for sales.agg.

RELATION time.r PRECOMPUTE (time NE 'Year99')
RELATION product.r PRECOMPUTE (product NE 'All')
RELATION channel.r
RELATION customer.r
AGGINDEX NO

This aggregation specification tells Oracle OLAP that all sales data should be rolled up committed to the Database except for any data that has a time dimension value of Year99 or a product dimension value of All—the data for those cells is calculated the first time a user accesses them. The AGGINDEX value of NO tells Oracle OLAP not to create the indexes for data that should be calculated on the fly.

Now you execute the following statement.

sales2 = AGGREGATE(sales1 USING sales.agg) ACROSS SPARSE -
   <product, channel, customer>

sales2 now contains all of the data in sales1, plus any data that is aggregated for Year99—this is because time is not included in a composite.

On the other hand, the data that is aggregated for the product value of All is not computed and stored in sales2. This data is not computed or stored because the product dimension is included in a composite—the indexes that are required for dimensions that are included in composites were not created because the aggregation specification contains an AGGINDEX NO statement. Since the indexes did not exist, Oracle OLAP never called the AGGREGATE function to compute the data to be calculated on the fly.

Aggregating By Dimension Attributes

Assume that when your business makes a sales it keeps records of the customer's name, sex, age, and the amount of the sale. To hold this data, your analytic workspace contains a dimension named customer and three variables (named customer_sex, customer_age, and sales) that are dimensioned by customer.

 REPORT W 14 <customer_sex customer_age sales>

CUSTOMER        CUSTOMER_SEX   CUSTOMER_AGE      SALES
-------------- -------------- -------------- --------------
Clarke         M                          26      26,000.00
Smith          M                          47      15,000.00
Ilsa           F                          24      33,000.00
Rick           M                          33      22,000.00

You want to aggregate the detail sales data over sex and age to calculate the amount of sales you have made to males and females, and the amount of sales for different age ranges. To hold this data you need an INTEGER variable that is dimensioned by hierarchical dimensions for sex and age. You also need an aggmap object that specifies the calculations that Oracle OLAP performs to populate this variable from the data in the sales variable.

To create and populate the necessary objects, you take the following steps:

  1. Create and populate dimensions and self-relations for hierarchical dimensions named sex and age.

    DEFINE sex DIMENSION TEXT
    DEFINE sex.parentrel RELATION sex <sex>
    DEFINE age DIMENSION TEXT
    DEFINE age.parentrel RELATION age <age>
    
    AGE               AGE.PARENTREL
    -------------- --------------------
    0-20           All
    21-30          All
    31-50          All
    51-100         All
    No Response    All
    All            NA
    
    SEX               SEX.PARENTREL
    -------------- --------------------
    M              All
    F              All
    No Reponse     All
    All            NA
    
  2. Create and populate relations that map the age and sex dimensions to the customer dimension.

    DEFINE customer.age.rel RELATION age <customer>
    DEFINE customer.sex.rel RELATION sex <customer>
    
    CUSTOMER         CUSTOMER.AGE.REL     CUSTOMER.SEX.REL
    -------------- -------------------- --------------------
    Clarke         21-30                M
    Smith          31-50                M
    Ilsa           21-30                F
    Rick           31-50                M
    
  3. Create a variable named sales_by_sex_age to hold the aggregated data. Like the sales variable this variable is of type DECIMAL, but it is dimensioned by sex and age rather than by customer.

    DEFINE sales_by_sex_age VARIABLE DECIMAL <sex age>
    
  4. Define an AGGMAP type aggmap object named ssa_aggmap to calculate the values of the sales_by_sex_age variable.

    DEFINE SSA_AGGMAP AGGMAP
    AGGMAP
    RELATION sex.parentrel OPERATOR SUM
    RELATION age.parentrel OPERATOR SUM
    BREAKOUT DIMENSION customer -
    BY customer.sex.rel, customer.age.rel OPERATOR SUM
    END
    

    Notice that the specification for the ssa_aggmap includes the following statements:

    • A BREAKOUT DIMENSION statement that specifies how to map the customer dimension of the sales variable to the lowest-level values of the sales_by_sex_age variable. This statement specifies the name of the dimension of the variable that contains the detail values (that is, customer) and the names of the relations (customer.sex.rel and customer.age.rel) that define the relations between customer dimension and the sex and age dimensions.

    • Two RELATION statements that specify how to aggregate up the sex and age dimensions of the sales_by_sex_age variable. Each of these statements includes the name of the child-parent relation (sex.parentrel or age.parentrel) that define the self-relation for the hierarchal dimension (sex or age).

  5. Populate the sales_by_sex_age variable by issuing an AGGREGATE command that specifies that the detail data for the aggregation comes from the sales variable.

    AGGREGATE sales_by_sex_age USING ssa_aggmap FROM sales 
    

    After performing the aggregation, a report of sales_by_sex_age shows the calculated values.

                   ---------------------SALES_BY_SEX_AGE----------------------
                   ----------------------------SEX----------------------------
    AGE                  M              F          No Reponse        All
    -------------- -------------- -------------- -------------- --------------
    0-20                       NA             NA             NA             NA
    21-30               26,000.00      33,000.00             NA      59,000.00
    31-50               37,000.00             NA             NA      37,000.00
    51-100                     NA             NA             NA             NA
    No Response                NA             NA             NA             NA
    All                 63,000.00      33,000.00             NA      96,000.00
    

Using a CACHE Statement in an Aggregation Specification

Suppose you have a sales variable with the following definition.

DEFINE sales DECIMAL <time, SPARSE<product, channel, customer>>

You do not want to pre-compute and commit all of the sales data, because space is limited and you must improve performance. Therefore, you must create an aggmap, in which you specify which data should be pre-computed and which data should be calculated on the fly.

You define the aggmap, named sales.agg, with the following statement.

DEFINE sales.agg AGGMAP <time, SPARSE<product, channel, - customer>>

Next, you use the AGGMAP statement to enter the following aggregation specification forsales.agg.

AGGMAP
RELATION time.r PRECOMPUTE (time NE 'YEAR99')
RELATION product.r PRECOMPUTE (product NE 'ALL')
RELATION channel.r
RELATION customer.r
CACHE SESSION
END

This aggregation specification tells Oracle OLAP that all sales data should be rolled up and committed, except for any cells that have a time dimension value of Year99 or a product dimension value of ALL; the data for those cells is calculated the first time a user accesses them. Because the CACHE statement uses the SESSION keyword, that means that when those cells are calculated on the fly, the data is stored in the cache for the remainder of the Oracle OLAP session. That way, the next time a user accesses the same cell, the data does not have to be calculated again. Instead, the data is retrieved from the session cache.

Populating All Levels of a Hierarchy Except the Detail Level

Assume that your analytic workspace contains the relations and dimensions with the following definitions.

DEFINE geog.d TEXT DIMENSION
DEFINE geog.r RELATION geog.d <geog.d>
DEFINE sales_by_units   INTEGER VARIABLE <geog.d>
DEFINE sales_by_revenue DECIMAL VARIABLE <geog.d>
DEFINE price_per_unit   DECIMAL VARIABLE <geog.d>

Assume that you create two aggmap objects. One aggmap object, named units_aggmap, is the specification to aggregate data in the sales_by_units variable. The other aggmap object, revenue_aggmap, is the specification to calculate all of the data except the detail data in the sales_by_revenue variable.

DEFINE units_aggmap AGGMAP
AGGMAP
  RELATION geog.r OPERATOR SUM
END

DEFINE revenue_aggmap AGGMAP
AGGMAP
  RELATION geog.r OPERATOR WSUM ARGS WEIGHTBY price_per_unit
  CACHE NOLEAF
END

The following steps outline the aggregation process:

  1. Before either the sales_by_unit or sales_by_revenue variables are aggregated, they have the following values.

    GEOG.D    SALES_BY_UNIT SALES_BY_REVENUE
    --------- ------------- ----------------
    Boston                1               NA
    Medford               2               NA
    San Diego             3               NA
    Sunnydale             4               NA
    MA                    NA              NA             
    CA                    NA              NA
    USA                   NA              NA
    
  2. After the data for the sales_by_unit variable is aggregated, the sales_by_unit and sales_by_revenue variables have the following values.

    AGGREGATE sales_by_unit    USING units_aggmap
    
    GEOG.D    SALES_BY_UNIT SALES_BY_REVENUE
    --------- ------------- ----------------
    Boston                1               NA
    Medford               2               NA
    San Diego             3               NA
    Sunnydale             4               NA
    MA                    3               NA             
    CA                    7               NA
    USA                  10               NA
    
  3. After the data for the sales_by_revue variable is aggregated, the sales_by_unit and sales_by_revenue variables have the following values.

    AGGREGATE sales_by_revenue USING revenue_aggmap FROM units_aggmap
    
    GEOG.D    SALES_BY_UNIT SALES_BY_REVENUE
    --------- ------------- ----------------
    Boston                1               NA
    Medford               2               NA
    San Diego             3               NA
    Sunnydale             4               NA
    MA                    3             13.5             
    CA                    7             31.5
    USA                  10             45.0
    

Aggregating into a Different Variable

Assume that there is a variable named sales that is dimensioned by time, a hierarchical dimension, and district, a non-hierarchical dimension.

DEFINE time DIMENSION TEXT
DEFINE time.parentrel RELATION time <time>
DEFINE district DIMENSION TEXT
DEFINE sales VARIABLE DECIMAL <time district>

             -----------------------SALES-----------------------
             ---------------------DISTRICT----------------------
TIME            North        South         West         East
------------ ------------ ------------ ------------ ------------
1976Q1         168,776.81   362,367.87   219,667.47   149,815.65
1976Q2         330,062.49   293,392.29   237,128.26   167,808.03
1976Q3         304,953.04   354,240.51   170,892.80   298,737.70
1976Q4         252,757.33   206,189.01   139,954.56   175,063.51
1976                   NA           NA           NA           NA

Assume also that you want to calculate the total sales for each quarter and year for all districts except the North district. To perform this calculation using an aggmap object, you take the following steps:

  1. Create a valueset named not_north that represents the values of district for which you want to aggregate data.

    DEFINE not_north VALUESET district
    LIMIT not_north TO ALL
    LIMIT not_north REMOVE 'North'
    
  2. Define a variable named total_sales_exclud_north to hold the results of the calculation.

    DEFINE total_sales_exclud_north VARIABLE DECIMAL <time>
    

    Notice that, like sales, the total_sales_exclud_north variable is dimensioned by time. However, unlike sales, the total_sales_exclud_north variable is not dimensioned by district since it holds detail data for each district, but only the total (aggregated) values for the South, West, and East districts (that is, all districts except North).

  3. Define an aggmap object that specifies the calculation that you want performed.

    DEFINE agg_sales_exclud_north AGGMAP
    AGGMAP
    RELATION time.parentrel OPERATOR SUM
    DROP DIMENSION district OPERATOR SUM VALUES not_north
    END
    

    Notice that the aggregation specification consists of two statements that specify how to perform the aggregation:

    • A RELATION statement that specifies how to aggregate up the hierarchical time dimension

    • A DROP DIMENSION statement that specifies how to aggregate across the non-hierarchical district dimension. In this case, the DROP DIMENSION also uses the not_north valueset to specify that values for the North district are excluded when performing the aggregation

  4. Aggregate the data.

    AGGREGATE total_sales_exclud_north USING agg_sales_exclud_north FROM sales
    

    The report of the total_sales_exclud_north variable shows the aggregated values.

    TIME             ALL_SALES_EXCEPT_NORTH
    ------------ ------------------------------
    1976Q1                           731,850.99
    1976Q2                           698,328.58
    1976Q3                           823,871.02
    1976Q4                           521,207.09
    1976                           2,775,257.69
    

Using a MEASUREDIM Statement in an Aggregation Specification

Suppose you have defined a measure dimension named measure. You then define an operation variable named myopvar, which is dimensioned by measure. When you use myopvar in an aggregation specification, you must also include a MEASUREDIM statement that identifies measure as the dimension is included in the definition of myopvar.

The MEASUREDIM statement should follow the last RELATION statement in the aggregation specification, as shown in the following example.

DEFINE sales.agg AGGMAP <time, product, geography>
AGGMAP
RELATION time.r OPERATOR myopvar
RELATION product.r
RELATION geography.r
MEASUREDIM measure
END 

Solving a Model in an Aggregation

This example uses the budget variable.

DEFINE budget VARIABLE DECIMAL <line time>
LD Budgeted $ Financial

The time dimension has two hierarchies (Standard and YTD) and a parent relation named time.parentrel as follows.

               -----TIME.PARENTREL------
               ----TIME.HIERARCHIES-----
TIME             Standard       YTD
-------------- ------------ ------------
Last.YTD       NA           NA
Current.YTD    NA           NA
Jan01          Q1.01        Last.YTD
...
Dec01          Q4.01        Last.YTD
Jan02          Q1.02        Current.YTD
Feb02          Q1.02        Current.YTD
Mar02          Q1.02        Current.YTD
Apr02          Q2.02        Current.YTD
May02          Q2.02        Current.YTD
Q1.01          2001         NA
...
Q4.01          2001         NA
Q1.02          2002         NA
Q2.02          2002         NA
2001           NA           NA
2002           NA           NA

The relationships among line items are defined in the following model.

DEFINE income.budget MODEL
MODEL
DIMENSION line time
opr.income = gross.margin - marketing
gross.margin = revenue - cogs
revenue = LAG(revenue, 12, time) * 1.02
cogs = LAG(cogs, 1, time) * 1.01
marketing = LAG(opr.income, 1, time) * 0.20
END

The following aggregation specification pre-aggregates all of the data. Notice that all of the data must be pre-aggregated because the model includes both LAG functions and a simultaneous equation.

DEFINE budget.aggmap1 AGGMAP
AGGMAP
MODEL income.budget
RELATION time.parentrel
END

Aggregating Up a Hierarchy

Suppose you define a sales variable with the following statement.

DEFINE sales VARIABLE <time, SPARSE <product, geography>>

The aggregation specification for sales might include RELATION statements like the following.

AGGMAP
RELATION time.r PRECOMPUTE ('Yr98', 'Yr99')
RELATION product.r
RELATION geography.r PRECOMPUTE (geography NE 'Atlanta')
END

The AGGREGATE command aggregates values for Yr98 and Yr99, over all of products, and over all geographic areas except for Atlanta. All other aggregates are calculated on the fly.

Using Valuesets

Suppose you have a hierarchy dimension named time.type, whose dimension values are Fiscal and Calendar, in that order. These hierarchies are in conflict, and you want to precompute some time data but calculate the rest on the fly. Because the Calendar hierarchy is the last dimension value in the hierarchy dimension, consequently, you must define a valueset to get the correct results for the Fiscal hierarchy.

First, use the following statements to define and populate a valueset.

DEFINE time.vs VALUESET time
LIMIT time.vs TO 'Calendar' 'Fiscal'

You can then use the valueset in the following RELATION statement. Because the Fiscal hierarchy is the last hierarchy in the valueset, the data that is aggregated is accurate for the Fiscal hierarchy.

RELATION time.r(time.vs) PRECOMPUTE ('Yr99', 'Yr00')

Aggregating with a RELATION Statement That Uses an ARGS Keyword

You can list the arguments in a RELATION statement directly in the statement or as the value of a text variable. For example, the following statement specifies WEIGHTBY wobj as an argument.

RELATION time.r OPERATOR wsum ARGS WEIGHTBY wobj

Alternatively, you can define an variable for the argument whose value is the text of the WEIGHTBY clause.

DEFINE argvar TEXT
argvar = 'WEIGHTBY wobj'

Then the RELATION statement can specify the text variable that contains the WEIGHTBY clause.

RELATION time.r OPERATOR WSUM ARGS argvar

Aggregating Using a Measure Dimension

Suppose you want to use a single AGGREGATE command to aggregate the sales, units, price, and inventory variables. When you want to use the same operator for each variable, then you do not have to use a measure dimension. However, when you want to specify different aggregation operations, then you must use a measure dimension.

The following statement defines a dimension named measure.

DEFINE measure DIMENSION TEXT

You can then use a MAINTAIN statement to add dimension values to the measure dimension.

MAINTAIN measure ADD 'sales', 'units', 'quota', 'inventory'

Use the measure dimension to dimension a text variable named meas.opvar that you use as the operator variable.

DEFINE meas.opvar TEXT WIDTH 2 <measure>

The following statements add values to OPVAR

meas.opvar (measure 'sales') = 'SU'
meas.opvar (measure 'units') = 'SU'
meas.opvar (measure 'price') = 'HA'
meas.opvar (measure 'inventory') = 'HL'

The aggregation specification might look like the following. Note that when you specify an operator variable in a RELATION statement, you must include a MEASUREDIM statement that specifies the name of the measure dimension (measure in the following example) in the aggregation specification.

DEFINE opvar.aggmap AGGMAP
AGGMAP
RELATION geography.parentrel PRECOMPUTE (geography.lvldim 2 4)
RELATION product.parentrel OPERATOR opvar
RELATION channel.parentrel OPERATOR opvar
RELATION time.parentrel OPERATOR opvar
MEASUREDIM measure
END

Aggregating Using a Line Item Dimension

Suppose you have two variables, actual and budget, that have these dimensions.

<time line division>

You want to use different methods to calculate different line items. You create a text variable that you use as the operator variable.

DEFINE line.opvar TEXT WIDTH 2 <line>

You then populate line.opvar with the appropriate operator for each line item, for example.

line.opvar (line 'Net.Income') = 'SU'
line.opvar (line 'Tax.Rate') = 'AV'

 The aggregation specification might look like this.

DEFINE LINE.AGGMAP AGGMAP
AGGMAP
RELATION time.parentrel OPERATOR line.opvar
RELATION division.parentrel
END

Skip-Level Aggregation

Suppose you want to aggregate sales data. The sales variable is dimensioned by geography, product, channel, and time.

First, consider the hierarchy for each dimension. How many levels does each hierarchy have? What levels of data do users typically query? When you are designing a new workspace, what levels of data do your users plan to query?

Suppose you learn the information described in the following table about how users tend to query sales data for the time hierarchy.

Time Level Names Descriptive Level Name Examples of Dimension Values Do users query this level often?
L1 Year Year99, Year00 yes
L2 Quarter Q3.99, Q3.99, Q1.00 yes
L3 Month Jan99, Dec00 yes

While the next table shows how your users tend to query sales data for the geography hierarchy.

Geography Level Names Descriptive Level Name Examples of Dimension Values Do users query this level often?
L1 World World yes
L2 Continent Europe, Americas no
L3 Country Hungary, Spain yes
L4 City Budapest, Madrid yes

Finally, the next table shows how your users tend to query sales data for the product dimension hierarchy.

Product Level Names Descriptive Level Name Examples of Dimension Values Do users query this level often?
L1 All Products Totalprod yes
L2 Division Audiodiv, Videodiv yes
L3 Category TV, VCR yes
L4 Product Tuner, CDplayer yes

Using this information about how users query data, use the following strategy for aggregation:

The lowest level of data was loaded into the analytic workspace. The aggregate data is calculated from this source data.

Therefore, the aggregation specification might look like the following.

RELATION time.parentrel
RELATION geography.parentrel PRECOMPUTE (geog.leveldim 'L3' 'L1')
RELATION product.parentrel

Aggregation Specification with RELATION Statements That Include PRECOMPUTE Clauses

This aggregation specification uses PRECOMPUTE clauses in the RELATION statements to limit the data that is aggregated by the AGGREGATE command.

DEFINE gpct.aggmap AGGMAP
LD Aggmap for sales, units, quota, costs
AGGMAP
RELATION geography.parentrel PRECOMPUTE (geography.levelrel 'L3')
RELATION product.parentrel PRECOMPUTE (LIMIT(product complement 'TotalProd'))
RELATION channel.parentrel
RELATION time.parentrel PRECOMPUTE (time NE '2001')
END