Previous
Previous
 
Next
Next


RELATION (for aggregation)

Within an aggregation specification, a RELATION statement specifies how data is aggregated across a hierarchical dimension. Frequently, an aggregation specification contains one RELATION statement for each of the hierarchical dimensions of a variable.


Note:

Do not confuse this RELATION statement which can only be used as part of an AGGMAP command with either the RELATION command that defines a default relation for a dimension or the RELATION statement that is used as part of an ALLOCMAP command.

Syntax

RELATION rel-name [(valueset...)] -

     [PRECOMPUTE (precompute-phrase)] -

     [OPERATOR {operation|opvar}] -

     [PARENTALIAS dimension-alias-name] -

     [ARGS {argument|argsvar}] -

[LOAD_STATUS(status-valueset-name)]

where:

Parameters

rel-name

A relation that defines a hierarchy by identifying the parent of every dimension value in a hierarchy.

valueset

Sets the status of one or more dimensions for the duration of the aggregation. It overrides the current status.

PRECOMPUTE

Indicates that some dimension values are populated only with the AGGREGATE command. The PRECOMPUTE clause of the RELATION statement limits the data that is aggregated by the AGGREGATE command. In its simplest form, you can think of the PRECOMPUTE clause as working like a LIMIT dimension TO statement. Notice that the default limit is on the dimension, which is not explicitly named in the RELATION statement.


Note:

An aggregation specification has PRECOMPUTE clauses in any of its RELATION statements cannot also have a PRECOMPUTE statement. Additionally, you cannot specify a PRECOMPUTE phrase for a RELATION statement for a compressed composite.

n%

Specifies an explicit percentage of the aggregate variable values that are aggregated as a database maintenance procedure using an AGGREGATE command. Oracle OLAP uses special functionality called the Aggregate Advisor to determine exactly which values are in the percentage.

AUTO

Specifies that Oracle OLAP uses the Aggregate Advisor to determine how many and which aggregate variable values to aggregate as a database maintenance procedure using an AGGREGATE command.

dimension-values

A list of one or more values of dimension.

positions-of-dim-values

For all dimensions except those with INTEGER or NUMBER values, the positions of the dimension values that you want precomputed. Specify the positions using INTEGER values, separated by commas.

valueset2

The name of a valueset. When you include this argument, only data that is dimensioned by the dimension values in the valueset should be precalculated with the AGGREGATE command. The rest of the values can be calculated on the fly.

Note that the current status of a dimension can also limit the data that is precalculated. See the AGGREGATE command for details.

ALL

Specifies that data should be precalculated for all dimension values.

NA
NONE

Specifies that all values should be calculated on the fly using the AGGREGATE function (that is, that no data should be precalculated with the AGGREGATE command).

level-relation-name level-name ...

Specifies the levels of the dimension to be precomputed. For level-relation-name, specify, as a TEXT value, the name of the relation object that relates the values of the dimension to the names of the levels of the dimension. For level-name, specify, as TEXT values, the name of one or more levels using the same level names used in level-relation-name.

OPERATOR  

Identifies the calculation method used to aggregate the data.

operation  

A keyword that describes the type of calculation to perform. The keywords are listed in Table: Aggregation Methods and can be retrieved by issuing a AGGROPS statement. You can specify a fixed-length three-character abbreviation for the keywords by specifying only the first three characters.

Aggregation Methods

Keyword Description

AND

When any child data value is FALSE, then the data value of its parent is FALSE. A parent is TRUE only when all of its children are TRUE. (BOOLEAN variables only)

AVERAGE

Adds data values, then divides the sum by the number of data values that were added. When you use AVERAGE, there are special considerations described in "Average Operators".

FIRST

The first non-NA data value.

HAVERAGE

(Hierarchical Average) Adds data values, then divides the sum by the number of the children in the dimension hierarchy. Unlike AVERAGE, which counts only non-NA children, HAVERAGE counts all of the logical children of a parent, regardless of whether each child does or does not have a value.

This keyword is not affected by the setting of the NASKIP option for argument.

HFIRST

(Hierarchical First) The first data value that is specified by the hierarchy, even when that value is NA.

This keyword is not affected by the setting of the NASKIP option for argument.

HLAST

(Hierarchical Last) The last data value that is specified by the hierarchy, even when that value is NA.

This keyword is not affected by the setting of the NASKIP option for argument.

HWAVERAGE

(Hierarchical Weighted Average) Multiplies non-NA child data values by their corresponding weight values then divides the result by the sum of the weight values. Unlike WAVERAGE, HWAVERAGE includes weight values in the denominator sum even when the corresponding child values are NA.

When you use this keyword, you must include the WEIGHTBY argument keyword with a variable, formula, or relation as the weight object.

This keyword is not affected by the setting of the NASKIP option for argument.

HWFIRST

(Hierarchical Weighted First) The first data value that is specified by the hierarchy multiplied by its corresponding weight value, even when that value is NA.

When you use this keyword, you must include the WEIGHTBY argument keyword with a variable, formula, or relation as the weight object.

This keyword is not affected by the setting of the NASKIP option for argument.

HWLAST

(Hierarchical Weighted Last) The last data value that is specified by the hierarchy multiplied by its corresponding weight value, even when that value is NA.

When you use this keyword, you must include the WEIGHTBY argument keyword with a variable, formula, or relation as the weight object.

This keyword is not affected by the setting of the NASKIP option for argument.

LAST

The last non-NA data value.

MAX

The largest data value among the children of any parent data value.

MIN

The smallest data value among the children of any parent data value.

NOAGG

Do not aggregate any data for this dimension.

OR

When any child data value is TRUE, then the data value of its parent is TRUE. A parent is FALSE only when all of its children are FALSE. (BOOLEAN variables only)

SSUM

(Scaled Sum) Adds the value of a weight object to each data value, then adds the data values.

When you use this keyword, you must include the WEIGHTBY argument keyword with a variable, formula, or relation as the weight object.

SUM

(Default) Adds data values.

WAVERAGE

(Weighted Average) Multiplies each data value by a weight factor, adds the data values, and then divides that result by the sum of the weight factors.

When you use this keyword, you must include the WEIGHTBY argument keyword with a variable, formula, or relation as the weight object.

WFIRST

(Weighted First) The first non-NA data value multiplied by its corresponding weight value.

When you use this keyword, you must include the WEIGHTBY argument keyword with a variable, formula, or relation as the weight object.

WLAST

(Weighted Last) The last non-NA data value multiplied by its corresponding weight value.

When you use this keyword, you must include the WEIGHTBY argument keyword with a variable, formula, or relation as the weight object.

WMAX

(Weighted Maximum) The largest data value among the children of any parent data value multiplied by its corresponding weight value.

When you use this keyword, you must include the WEIGHTBY argument keyword with a variable, formula, or relation as the weight object.

WMIN

(Weighted Minimum) The smallest data value among the children of any parent data value multiplied by its corresponding weight value.

When you use this keyword, you must include the WEIGHTBY argument keyword with a variable, formula, or relation as the weight object.

WSUM

(Weighted Sum) Multiplies each data value by a weight factor, then adds the data values.

When you use this keyword, you must include the WEIGHTBY argument keyword with a variable, formula, or relation as the weight object.


opvar

A TEXT variable that you define that specifies a different the operation for each of its dimension values.


Note:

Not valid for variables dimensioned by compressed composites.

The opvar argument is used in two ways:

The opvar argument cannot be dimensioned by the dimension it is used to aggregate. For example, when you want to specify different operations for the geography dimension, then opvar cannot be dimensioned by geography.

To minimize the amount of paging for the operator variable, define the operation variable as type of TEXT with a fixed width of 8.

PARENTALIAS

Specifies that an alias dimension for the dimension being aggregated is QDRd to the parent value currently being aggregated.

dimension-alias-name

The name of the alias dimension for the dimension of rel-name.

ARGS

Indicates optional handling of the aggregation.

DIVIDEBYZERO

Specifies whether to allow division by zero.

The default value is the current value of the DIVIDEBYZERO option.

DECIMALOVERFLOW

Specifies whether to allow decimal overflow, which occurs when the result of a calculation is very large and can no longer be represented by the exponent portion of the numeric representation.

The default value is the current value of the DECIMALOVERFLOW option.

NASKIP

Specifies whether NA values are input.

The default value is the current value of the NASKIP option.

The value that you specify for the NASKIP phrase does not effect calculation performed when you specify HAVERAGE, HFIRST, HLAST, HWAVERAGE, HWFIRST, HWLAST for operation.

WEIGHTBY

Indicates that weighted aggregation is to be performed. You must include a WEIGHTBY clause when you specify HWAVERAGE, HWFIRST, HWLAST, SSUM, WAVERAGE, WFIRST, WLAST, or WSUM for operation. The WEIGHTBY phrase always includes a wobj argument and, optionally, can include the WNAFILL keyword.

WNAFILL {number | NA}

Indicates handling for NA values. The default values for WNAFILL vary depending on the value of operation. The default value for HWAVERAGE and SSUM is 0.0. The default value for OR is NA. The default value for the other operators is 1.0. WNAFILL defaults for each operator in an aggregation specification. In other words, when one RELATION statement includes a WSUM OPERATOR, then WNAFILL defaults to 1.0. When the next RELATION statement includes an SSUM OPERATOR, then WNAFILL defaults to 0.0, and so on. See "Using WNAFILL".

wobj

A variable, formula, or relation that provides the weighted values. It can be numeric or BOOLEAN. When wobj is BOOLEAN, then TRUE has a weight of 1.0 and FALSE has a weight of 0.0. A formula is queried only when needed, depending on the dimensionality of the formula and the variable being aggregated. When wobj is a relation, it should be a one-dimensional self-relation. See Using Weighted Aggregation Methods for more information about specifying values for wobj.

COUNT {YES|NO}

YES specifies that when Oracle OLAP aggregates a variable using this relation that it also populates the Aggcount variable associated with that variable. For more information on Aggcount variables, see "Aggcount Variables".

NO specifies that when Oracle OLAP aggregates a variable using this relation that it does not populate the Aggcount variable associated with that variable. For more information on Aggcount variables, see "Aggcount Variables".

argsvar

A TEXT variable that contains the argument options for some or all dimension values.

LOAD_STATUS

Specifies that, for the aggregation, Oracle OLAP consider the values specified by status-valueset-name as the detail or lowest level of the hierarchy.

status-valueset-name

A previously-defined valueset that specifies the lowest-level values to have in status when performing the aggregation. When performing any aggregation using an aggmap with a RELATION statement with this clause, Oracle OLAP temporarily sets the status of the dimension to the values specified by status-valueset-name and their ancestors. The valueset specified by status-valueset-name must be a single dimensional valueset for the relation dimension (not the hierarchy dimension). Additionally, the valueset specified by status-valueset-name cannot contain both a value and an ancestor of that value.

Usage Notes

Ordering RELATION Statements with Non-Additive Operators

The order of RELATION statements that use non-additive operators effects the result of the calculation. For example the max of sum is not generally equal to the sum of max. Consequently, the order of RELATION statements within an aggregation specification must follow the logical requirements of the calculation. This logical necessity limits the use of dynamic models within an aggregation as discussed in "Dynamic Models and Non-Additive Operators".

RELATION Statements for Compressed Composites

When designing the aggregation specification, follow these guidelines when coding RELATION statements for compressed composites:

Two Ways to use Valuesets

You can use valuesets to:

When You Change a PRECOMPUTE or an OPERATOR Clause

Any time you make changes to a PRECOMPUTE or an OPERATOR clause, aggregate the variable data again and recompile the aggmap to produce accurate data.

Aggregating Data Loaded into Different Hierarchy Levels

When data is loaded into dimension values that are at different levels of a hierarchy, then you must be careful in how you set status in the PRECOMPUTE clause in a RELATION statement in your aggregation specification.

Suppose that a time dimension has a hierarchy with three levels: months aggregate into quarters, and quarters aggregate into years. Some data is loaded into month dimension values, while other data is loaded into quarter dimension values. For example, Q1 is the parent of January, February, and March. Data for March is loaded into the March dimension value. But the sum of data for January and February is loaded directly into the Q1 dimension value. In fact, the January and February dimension values contain NA values instead of data. Your goal is to add the data in March to the data in Q1.

When you attempt to aggregate January, February, and March into Q1, the data in March simply replaces the data in Q1. When this happens, Q1 contains only the March data instead of the sum of January, February, and March.

To aggregate data that is loaded into different levels of a hierarchy, create a valueset for only those dimension values that contain data.

DEFINE all_but_q4 VALUESET time
LIMIT all_but_q4 TO ALL
LIMIT all_but_q4 REMOVE 'Q4'

Within the aggregation specification, use that valueset to specify that the detail-level data should be added to the data that exists in its parent, Q1, as shown in the following statement.

RELATION time.r PRECOMPUTE (all_but_q4)

Average Operators

There are several issues involved in using the AVERAGE, HAVERAGE, WAVERAGE, and HWAVERAGE operators:

HAVERAGE, HFIRST, HLAST, AND HWAVERAGE Operators

The "hierarchical" operators (HAVERAGE, HFIRST, HLAST, AND HWAVERAGE) are intended to provide an alternative form of NA handling.

FIRST, HFIRST, LAST, AND HLAST Operators

These operators rely on the existing order of the dimension values, which are assumed to be the default logical order of that dimension. For example, in a month dimension, it is assumed that February follows January, March follows February, and so on.

When you must change the default order, use the MAINTAIN statement to do so. For example, suppose Q1 includes January, February, and March, but you must make February the last month in the Q1 instead of March. Use the following statement to do so.

MAINTAIN time MOVE 'Feb01' AFTER 'Mar01'

Now, the LAST operator assumes that FEB01 is the last month in Q1.

Read Permissions and Aggmaps

When you change the read permission to rel-name in a RELATION statement, then you must recompile the aggmap before using it with the AGGREGATE function. Compilation is not an issue when you use the AGGREGATE command, because the aggmap is recompiled automatically. However, when you do not have read access to every rel-name in the aggmap, then attempting to use that aggmap results in an error message.

Using Weighted Aggregation Methods

When you use a weighted method of aggregation, you must define and populate an object that contains the weights. You identify the aggregation method in the OPERATOR clause and the weight object in the ARGS clause.

The weight object can be a variable, a formula, or a relation. Special considerations apply depending on the type of object. the data type of the weight object, and whether or not you are performing a partial aggregation.

Weight Object Considerations Based on Type of Object The following considerations apply depending on the type of object that you use for the weight object:

Considerations Based on Data Type of the Weight Object The following considerations apply when the weight object is numeric or BOOLEAN:

Weight Object Considerations When Performing Partial Aggregations When you use any operators that require the WEIGHTBY phrase, and you are performing a partial aggregation, then do not change the values that are stored in the weight object between AGGREGATE commands.

Using WNAFILL

For example, suppose you use the WSUM operator to perform currency conversion. The currency conversation rates are applied at the detail data level. Only the detail data needs to be converted, because the variable data is aggregated after the conversion. To get the correct results, all of the non-detail level weight values in the weight object would have to be 1. Although this strategy produces correct results, it is inefficient. The best practice is to use the default WNAFILL value of 1 which specifies that all NA values in the weight object should be treated as if they have a weight of 1. In this case, because the operator is WSUM, you do not have to include WNAFILL in the AGGREGATE command, because the default values are correct.

For example, the following statement causes the value 0.7 to be substituted for every NA value in the salesw weight object.

AGGREGATE sales USING sales.agg WEIGHTBY WNAFILL 0.7 salesw

When you do not want to specify a number to replace NA values, then you can use NA instead of a number, as shown in the following statement.

AGGREGATE sales USING sales.agg WEIGHTBY WNAFILL NA salesw

Specifying NA after WNAFILL has the following effect:

Effects of Dimension Status on Aggregation

A RELATION statement only aggregates those source data values that are in status—whether you set the status using LIMI T statements or a LOAD STATUS clause on the RELATION statement. The parent values are calculated regardless of whether they are in status or not. For example, when only Jan01, Feb01, and Mar01 are in status for the time dimension, then Q1.01 is calculated (but no other quarters), and 2001 is calculated (but no other years) using only Q1.01 as input since the other quarters are NA. This functionality is useful when you want to aggregate just the new data in your analytic workspace.

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>

REPORT DOWN time sales

             -----------------------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

Examples

For examples of aggregation specifications that include RELATION statements, see the examples in the AGGMAP command.