![]() Previous |
![]() Next |
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:
precompute-phrase is one or more of the following:
argument is one or more of the following:
argsvar is a text variable that contains argument phrases for some or all dimension values.
Parameters
A relation that defines a hierarchy by identifying the parent of every dimension value in a hierarchy.
Sets the status of one or more dimensions for the duration of the aggregation. It overrides the current status.
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. |
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.
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.
A list of one or more values of dimension.
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.
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.
Specifies that data should be precalculated for all dimension values.
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).
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.
Identifies the calculation method used to aggregate the data.
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 |
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- |
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- 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 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 This keyword is not affected by the setting of the NASKIP option for argument. |
HWAVERAGE |
(Hierarchical Weighted Average) Multiplies non- 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 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 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- |
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 |
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- 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- 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. |
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:
Measure dimension -- Changes the aggregation method depending upon the variable being aggregated. Changing the aggregation method based on the variable being aggregated is useful when a single aggmap is used to aggregate several variables that must be aggregated with different methods. Whether you pre-aggregate all of the measures in a single AGGREGATE command or in separate statements, AGGREGATE uses the operation variable to identify the calculation method. The values of the measure dimension are the names of the variables to be aggregated. It dimensions a text variable whose values identify the operation to be used to aggregate each measure. The aggregation specification must include a MEASUREDIM statement that identifies the measure dimension. See Example: Aggregating Using a Measure Dimension.
Line item dimension -- Changes the aggregation method depending upon the line item being aggregated. The line item dimension is typically non-hierarchical and identifies financial allocations. The line item dimension is used both to dimension the data variable and to dimension a text variable that identifies the operation to be used to aggregate each item. The operation variable is typically used to aggregate line items over time. You do not use the MEASUREDIM statement in the aggmap. See Example: Aggregating Using a Line Item Dimension.
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
.
Specifies that an alias dimension for the dimension being aggregated is QDRd to the parent value currently being aggregated.
The name of the alias dimension for the dimension of rel-name.
Indicates optional handling of the aggregation.
Specifies whether to allow division by zero.
YES allows division by zero; a statement involving division by zero executes without error but produces NA
results.
NO disallows division by zero; a statement involving division by zero stops executing and produces an error message.
The default value is the current value of the DIVIDEBYZERO option.
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.
YES allows overflow; a calculation that generates overflow executes without error and produces NA
results.
NO disallows overflow; a calculation involving overflow stops executing and generates an error message.
The default value is the current value of the DECIMALOVERFLOW option.
Specifies whether NA
values are input.
YES specifies that NA
values are ignored when aggregating. Only actual values are used in calculations.
NO specifies that NA
values are considered when aggregating. When any of the values being considered are NA
, the calculation returns NA
.
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.
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.
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".
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.
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".
A TEXT
variable that contains the argument options for some or all dimension values.
Specifies that, for the aggregation, Oracle OLAP consider the values specified by status-valueset-name as the detail or lowest level of the hierarchy.
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:
The HAVERAGE, HWAVERAGE, HWFIRST, HWLAST, SSUM, WAVERAGE, WFIRST, WLAST, WMAX, WMIN, and WSUM operators cause data values to change with each level of aggregation, regardless of sparsity. When possible, to insure the largest amount of overall compression, place RELATION statements with these operators at the beginning of your aggregation specification before RELATION statements that use an AND, AVERAGE, FIRST, HFIRST, HLAST, LAST, MAX, MIN, NOAGG, OR, or SUM operator.
To optimize the compression of a compressed composite, list similar operators contiguously if the calculation logic allows. For example, specifying MAX for the first dimension and then SUM for all the other dimensions results in better compression, and thus provides better calculation performance, than specifying SUM, MAX, and then SUM over the remaining dimensions.
SUM is the fastest and most compressible operator. Changing the aggregation operator for one or more dimensions from SUM to some other operator results in less compression, and therefore a larger variable, and the AGGREGATE command for that variable takes longer to complete.
When an AGGMAP contains a RELATION statement that specifies the AVERAGE operator, any variable using that aggregation specification must be defined using a DEFINE VARIABLE statement with a WITH AGGCOUNT phrase.
You can only specify a single aggregation operation. You cannot specify aggregation operations using an opvar variable.
Two Ways to use Valuesets
You can use valuesets to:
Limit hierarchy dimensions. You can limit which hierarchies are used by the AGGREGATE command and AGGREGATE function and the order in which these hierarchies should be used. The valueset that you use specifies the names of a dimension's hierarchies. To use a valueset in this way, use the following syntax.
RELATION rel-name (valueset)
In this case, using valuesets provides a way to manage hierarchies that are in conflict with each other, meaning, when the same dimension value stores data for different children in different hierarchies (such as, Q1
stores data for Jan
, Feb
, and Mar
in the Calendar
hierarchy, but Q1
stores data for May
, Jun
, and Jul
in the Fiscal
hierarchy).
Specify which values should be calculated on the fly by the AGGREGATE function and which values should be pre-calculated by the AGGREGATE command. The valueset that you use specifies the names of dimension values. To use a valueset in this way, use the following syntax.
RELATION rel-name PRECOMPUTE (valueset)
In this case, you use the valueset that follows the PRECOMPUTE keyword.
When you use valuesets to limit hierarchy dimensions and when using multiple aggmaps and the hierarchies are inconsistent, you must also use the FORCECALC keyword in the AGGREGATE function or have set an $AGGREGATE_FORCECALC property on the variable to be aggregated.
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:
Oracle OLAP needs a separate INTEGER
variable in which it stores the non-NA
counts of the number of leaf nodes that contributed to aggregate values to calculate average values. When you want to aggregate a variable using one the average operators, include the WITH AGGCOUNT phrase in the DEFINE VARIABLE statement for the variable.
Accuracy when averaging—All decimal data is converted to floating point format, both for storing and for calculations, consequently, in some cases, an average aggregation computed on a DECIMAL
or SHORTDECIMAL
variable can differ in the least significant digits from a result you compute by hand. For this reason, you might want to use the NUMBER
data type when accuracy is more important than computational speed, such as variables that contain currency amounts. See "Numeric Expressions" for more information.
Using Average operators when aggregating using an AGGREGATE command—When you use an average operator with the PRECOMPUTE keyword, the best practice is to use variables that have a decimal or NUMBER
data type to ensure the accuracy of the results.
Using Average operators for partial aggregations—When you use an average operator in a partial aggregation, then you must always aggregate using the same INTEGER
variable (that is, Aggcount or Countvar variable). Do not change the values that are stored in this INTEGER
variable between aggregations. Finally, the number of INTEGER
variables must match the number of variables that are being aggregated.
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 Februar
y 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:
When the weight object is a variable, you can define it with a numeric or BOOLEAN data type. Use a variable as your weight object when you want to pre-calculate weight values and commit them to the Database. You can use a variable weight object with any weight option.
When the weight object is a relation, define it as a one-dimensional self-relation. You can use the weight object to specify that the weight for a specific cell is contained in the current variable at a different location. Use a relation as your weight object when you use a line item or a measure dimension. In this case, one line item is used as the weight to calculate the aggregate value of another line item. Using a relation enables you to specify another set of cells in the variable being aggregated as the weight values for a weighted operation.
When the weight object is a formula, that formula is queried only as often as needed, depending on the dimensionality of the formula and the dimensionality of the variable whose data is being aggregated. You can define the formula with a numeric or BOOLEAN data type. Use a formula as your weight object when you want to calculate weight values on the fly. A formula weight object is similar to a variable weight object, except that it cannot be aggregated. The value of a formula weight object is executed dynamically. Therefore, you cannot use a formula weight object with many of the weight options.
Considerations Based on Data Type of the Weight Object The following considerations apply when the weight object is numeric or BOOLEAN
:
When the weight object has a numeric data type, It is good practice for the weight object variable to have the same dimensionality (or a subset thereof) as the variable to which it corresponds, but it is not required. When you use Oracle numbers or decimals to define your data variable, then always use the same data type to define the corresponding weight object. Otherwise, use the same data type for the weight object and the data variable unless you use WAVERAGE or HWAVERAGE; in this case, use a decimal or NUMBER
data type to define the weight object.
When the weight object variable, formula, or relation that you define has a BOOLEAN
data type, then TRUE
represents a weight of 1.0
and FALSE
represents a weight of 0.0
. Furthermore, when an NA
value is multiplied by any value, the result is NA
.
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:
When the aggregation specification contains a WAVERAGE or a WSUM OPERATOR, then any child cell in the weight object that has an NA
value is treated as an NA
cell.
When the aggregation specification contains an SSUM OPERATOR, then the results depend on how the Oracle OLAP option NASKIP is set. When NASKIP is set to YES
, then any NA
value is treated as 0.0. However, when NASKIP is set to NO
, then any NA
value is treated as an NA
cell.
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.