Previous
Previous
 
Next
Next


AGGINDEX

Within an aggregation specification, an AGGINDEX statement tells Oracle OLAP whether the compilation of that aggmap should create indexes (meaning, composite tuples) for data cells that are calculated on the fly by the AGGREGATE function. Therefore, the AGGINDEX statement has an effect on a dimension that is included in a composite but it has no effect on a dimension that is not included in a composite.

These indexes are used by the MODEL statement in an AGGMAP and by statements that use the ACROSS phrase to help Oracle OLAP loop over variables that are dimensioned by composites. These statements expect all data to be calculated. When you specify calculating some data on the fly, that data appears to be missing. When you set AGGINDEX to YES, then the statements try to access the missing data whether or not you are using the AGGREGATE function to perform calculation on the fly (meaning, you have added to the variable whose data is being aggregated an NA trigger property that calls the AGGREGATE function).

When the indexes have been created and you use AGGREGATION with the AGGREGATE function, then when MODEL (or a statement that uses the ACROSS phrase) requests the missing data, that data is calculated on the fly. That means that the results of the MODEL (or other statement) are correct, because the statement has all of the data that it needs.

When these indexes have not been created, the missing data cannot be calculated. Consequently, the statements that need the indexes interpret the missing data as NA data, even when you use the AGGREGATE function.

Syntax

AGGINDEX {YES|NO}

Parameters

YES

(Default) Tells the AGGMAP compiler to ensure that all possible indexes are created whenever an aggmap is recompiled. In other words, indexes are created both for the data that is being pre-calculated and the data that is calculated on the fly. Data is recalculates on the fly happens when a COMPILE statement compiles the aggmap and when the AGGREGATE command executes an aggmap whose specification has changed since the last time it was compiled. The creation of all possible indexes results in a longer compilation time but faster execution of the AGGREGATE function. For a discussion of when AGGINDEX should be set to YES, see "When To Use an AGGINDEX Value of YES".

NO

Does not create the indexes for data that is calculated on the fly. Omitting the creation of these index values accelerates the compilation time, but causes Oracle OLAP to treat the uncomputed data as NA data whenever the MODEL statement in an AGGMAP or an ACROSS phrase is executed. For a discussion of when AGGINDEX should be set to NO, see "When To Use an AGGINDEX Value of NO".

Usage Notes

When To Use an AGGINDEX Value of YES

The primary advantage to using an AGGINDEX value of YES is that then Oracle OLAP always try to access data that you have specified to be calculated on the fly. When you have created an $NATRIGGER property for a variable that calls the AGGREGATE function, the variable appears to have been fully precomputed. That means that when any NA value is encountered, the NA trigger is called during the execution of an ACROSS phrase or the MODEL statement in an AGGMAP. When the NA trigger is called, the AGGREGATE function is executed, and the data is calculated on the fly.

When AGGINDEX has a value of NO, then the NA trigger is called only to aggregate data for dimensions that are not included in a composite. Data for dimensions that are included in composites is interpreted as NA values.

For example, suppose you have two variables called sales1 and sales2, which are defined with the following definitions.

DEFINE sales1 DECIMAL <time, SPARSE <product, geography>>
DEFINE sales2 DECIMAL <time, SPARSE <product, geography>>

Now suppose you have an aggmap object named sales.agg, which has the following definition.

DEFINE sales.agg AGGMAP <time, SPARSE <product, geography>>

When you add a specification to the sales.agg aggmap, you enter RELATION statements for time, product and geography with PRECOMPUTE clauses that specify NA which specifies that no data is aggregated—instead, all of the data for any variable that uses this aggmap is calculated on the fly.

RELATION time.r PRECOMPUTE (NA)
RELATION product.r PRECOMPUTE (NA)
RELATION geography.r PRECOMPUTE (NA)

Now attach the following $NATRIGGER property to the sales1 variable.

CONSIDER sales1
PROPERTY '$NATRIGGER' 'AGGREGATE(sales1 USING sales.agg)'

Consider the effect of AGGINDEX in the following statement. Because you did not enter an AGGINDEX statement in the sales.agg aggregation specification, the default of AGGINDEX YES is assumed.

sales2 = sales1 ACROSS SPARSE <product, geography>

This statement loops over the data in sales1 and copies the values into sales2. This statement causes the NA trigger to call the AGGREGATE function for all of the data that you have specified to be calculated on the fly in sales1. Consequently, after the aggregation that sales2 contains a copy of sales1 plus all the aggregate data cells (the cells that would have been calculated if the sales1 data had been completely precomputed, meaning, fully rolled up).

However, when you put an AGGINDEX NO statement in the sales.agg aggregation specification, then sales2 contains a copy of the data in sales1 and the aggregate data cells for the time dimension.

Note that in both cases, $NATRIGGER is called to aggregate time data, because the time dimension is not included in the composite, so the value of AGGINDEX has no effect on it.

When To Use an AGGINDEX Value of NO

You can use an AGGINDEX value of NO when you know that either of the following is true:

Each of the preceding cases ensures that the data that you have specified to be calculated on the fly is available at the appropriate time.

By setting AGGINDEX to NO, the size of the indexes is reduced, and overall application performance improves.

When Using an AGGINDEX Value Of NO Causes Problems

When you run a MODEL that assumes all data that should be aggregated has been aggregated, then you may get NA data where real data should occur. For instance, suppose you have a variable that has a composite that includes the time dimension. You perform a calculation that subtracts the fourth quarter from the total for the year. When the value of Year is to be calculated dynamically, and the AGGINDEX statement is set to NO, then the result of the calculation is NA. When the value of Year was precomputed or when AGGINDEX is set to YES, then the MODEL correctly calculates a result equal to the sum of the first three quarters.

Index Creation Is Based on Existing Data

Only the indexes that are needed to aggregate existing data are created when AGGINDEX has a value of YES. For example, suppose one dimension in your composite is a dimension named time. The lowest-level data for the time dimension is at the monthly level. Therefore, the dimension values that are associated with the lowest-level data are Jan99, Feb99, and so on. The monthly data aggregates to quarters and to years. Suppose you have data for the first six months of the year. When AGGINDEX has a value of YES, indexes are created for the Q1, Q2, and Yr99 dimension values, but not for Q3 and Q4.

Reducing Compilation Time When AGGINDEX is YES

One disadvantage of using the default of AGGINDEX YES is that the compilation of the aggmap takes a longer time to complete. You can eliminate the cost of this extra time by using the FUNCDATA keyword with the AGGREGATE command. When you use the FUNCDATA keyword, all possible indexes (regardless of how you have limited your data) are created. However, do not use the FUNCDATA keyword when you use a different aggmap to execute the AGGREGATE command and the AGGREGATE function.

Examples

For an example of using an AGGINDEX statement, see Example: Using an AGGINDEX Statement in an Aggregation Specification.