![]() Previous |
![]() Next |
Within an allocation specification, a RELATION statement identifies a relation that specifies the path through a dimension hierarchy and the method of the allocation. To allocate a source data down a hierarchy of a dimension, you must specify with a RELATION statement the values of the hierarchy that identify the cells of the variable that are the targets of the allocation. When the target of the allocation is a multidimensional variable, then you must include a separate RELATION statement for each dimension down which you want to allocate the source data. The order of the RELATION statements in an aggmap determines the order of the allocation. The allocation proceeds down the dimension hierarchy in the first RELATION statement, then down the second, and so on.
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 AGGMAP command. |
Syntax
RELATION rel-name [(qdr. . .)] OPERATOR {operator|} -
[NAOPERATOR operator] [REMOPERATOR operator] -
[PARENTALIAS dimension-alias-name] -
[ARGS {[FLOOR floorval] [CEILING ceilval] [MIN minval] [MAX maxval] -
[NAHANDLE {IGNORE|CONSIDER|PREFER}] -
[ADD|ASSIGN] [PROTECT [NONORMALIZE] [READWRITE|WRITE] lockvalueset] -
[WEIGHTBY [ADD|MULTIPLY] [WNAFILL nafillval] weightobj]}]
Parameters
An Oracle OLAP self-relation that specifies the values of a dimension hierarchy that identify the path of allocation. The cells in the target variable identified by the values in rel-name receive the allocated data.
One or more qualified data references that specify a single dimension value for each dimension of the relation that is not part of the self-relation. When the self-relation has multiple hierarchies, you must provide a qdr for the hierarchy dimension of the self-relation dimension that limits to single values any hierarchies not involved in the allocation.
Specifies an allocation method described in Table: Allocation Operators or returned by ALLOCOPS. The method determines the cells of the target variable for the rel-name relation to which ALLOCATE assigns a value. For the FIRST, LAST, HFIRST, and HLAST operators, ALLOCATE uses the order of the value in the dimension to determine the cell. The dimension order is the default logical order of the allocation dimension. There is no default operator for allocation.
Allocation Operators
Operator | Description |
---|---|
COPY |
Copies the allocation source to all of the target cells that have a basis data value that is not |
HCOPY |
Copies the allocation source to all of the target cells specified by the hierarchy even when the data in any of those cells is |
MIN |
Copies the allocation source to the target that has the smallest basis data value. |
MAX |
Copies the allocation source to the target that has the largest basis data value. |
FIRST |
Copies the allocation source to the first target cell that has a non- |
HFIRST |
Copies the allocation source to the first target cell specified by the hierarchy even when the current data value of that cell is |
LAST |
Copies the allocation source to the last target cell that has a non- |
HLAST |
Copies the allocation source to the last target cell specified by the hierarchy even when the current data value of that cell is |
EVEN |
Divides the allocation source by the number of target cells that have non- |
HEVEN |
Divides the allocation source by the number of target cells, including the ones that have |
PROPORTIONAL |
Divides the allocation source by the sum of the data values of the target cells that have non- |
The operator after the NAOPERATOR keyword specifies the operator that the ALLOCATE operation uses when it encounters an NA
or lock-based deadlock. Valid operators are HFIRST, HLAST, and HEVEN which are described in Table: Allocation Operators.
The operator after the REMOPERATOR keyword specifies the operator that the ALLOCATE operation uses when storing a remainder produced by an allocation. For example, assume you allocate the INTEGER
10 to three cells at the same level in a hierarchy, there is a remainder of 1. The REMOPERATOR specifies where you want the allocation operation to store this remainder. Valid operators for REMOPERATOR are MIN, MAX, FIRST, HFIRST, LAST, and HLAST which are described in Table: Allocation Operators.
Indicates additional arguments specify additional parameters for the allocation operation. All of these arguments apply uniformly to the dimension hierarchy specified by rel-name.
Specifies specialized allocation depending on the parent (for example, weighting by parent or child). For dimension-alias-name, specify the name of the alias for the dimension of rel-name.
One or more arguments after the ARGS keyword that specify additional parameters for the allocation operation. All of these arguments apply uniformly to the dimension hierarchy specified by rel-name.
Specifies that when an allocated target data value is less than floorval, the data allocated to the target cell is NA
. This argument applies to the relation only when the PROPORTIONAL operator is specified.
Specifies that when an allocated target data value is greater than ceilval, the data allocated to the target cell is NA
. This argument applies to the relation only when the PROPORTIONAL operator is specified.
Specifies that when an allocated target data value is less than minval, the data allocated to the target cell is minval.
Specifies that when an allocated target data value is greater than maxval, the value allocated to the target cell is maxval.
Specifies how ALLOCATE treats NA
values. Valid only when the OPERATOR is MIN or MAX.
IGNORE specifies that ALLOCATE does not consider NA
values in a MIN or MAX operation. (Default)
CONSIDER specifies that ALLOCATE treats an NA
value as a zero; however, when the data value of a target cell is actually zero, the zero cell receives the allocated data value and not the NA
cell.
PREFER specifies that ALLOCATE treats an NA
value as a zero and the NA
has priority over a zero value, so the NA
cell receives the allocated data value and not the cell with the actual zero value.
Specifies that ALLOCATE adds the allocated data to the current data in the target cell.
Specifies that ALLOCATE replaces the data in the target cell with the allocated data, which is the default behavior.
Specifies a set of dimension values to lock so that they cannot be targets of the allocation. Before allocating the source data, the allocation operation normalizes the sources by subtracting the data values of the specified locked cells from the source data.
Specifies that the allocation operation does not normalize the source data. Using NONORMALIZE effectively removes from the allocation the values of the hierarchy at and below the dimension values specified by lockvalueset.
Specifies that the locked data values cannot be used as source data in a subsequent allocation, thereby locking the data of the hierarchy below the lockvalueset values.
Specifies that the allocation cannot store data values in the cells identified by the lockvalueset dimension values but the allocation can use the data in those cells as source data in its subsequent steps. However, when in the aggmap you include a SOURCEVAL statement that specifies NA
or ZERO
and the locked cell is the source of an allocation, then ALLOCATE sets the value of the locked cell to NA
or zero after the allocation.
Specifies that the allocation uses a the value specified by weightobj. Using this clause allows for processes such as unit or currency conversion.
Specifies that ALLOCATE adds the value specified by weightobj to the existing data value of the target and assigns the sum to the target cell.
(Default) Specifies that ALLOCATE multiplies the value specified by weightobj by the data value of the target and assigning the product.
Specifies that ALLOCATE replaces NA
values in a cell before applying the value specified by weightobj to the nafillval value.
The value that the ALLOCATE replaces NA
values with. When you specify the ADD option to the WEIGHTBY clause, the default NA
fill value is 0
; in all other cases, the default NA
fill value is 1
.
The name of an variable, formula, or relation whose value or values are the weights that Oracle OLAP applies to the allocated data just before it is stored in the target cell. When a relation is used, the target variable is referenced based on the weight relation and the cell is applied to the allocation target cell.
Usage Notes
Specifying the Path of the Allocation
The path of the allocation is the route the allocation system takes to go from the source data to the target data. Very different results derive from different allocation paths. You specify the path with the RELATION statements that you enter in the aggmap. The relation objects in the RELATION statements and the order of those statements specify the path and the method of allocation.
The allocation path goes from any level in the hierarchy of a dimension to any lower level of the hierarchy. You use a relation object that relates the members of the hierarchy to each other (a self-relation) to identify the elements of the hierarchy that you want to participate in the allocation. The allocation proceeds down the hierarchy of the dimension in the first RELATION statement in the aggmap, then down the hierarchy of the second RELATION statement, and so on.
When the dimension has multiple hierarchies, you must use the qdr argument in the RELATION statement to specify which hierarchy to use for the allocation. The hierarchy that you specify with a relation must not contain a circular relation (for example, one in which dimension value A
relates to dimension value B
which relates to dimension value C
which relates to dimension value A
).
Types of Allocation Paths
You can allocate values from a source to a target with any one of the following types of paths:
Direct allocation path — You can allocate values directly from a source to the final target cells with no allocations to intermediate nodes of the hierarchy. For example, you can allocate source data values specified by dimension values at the Quarter
level of a hierarchical time
dimension to those at the Month
level or those specified by dimension values at the Year
level to those at the Month
level.
Recursive descent hierarchy path — You can allocate values to intermediate nodes of the hierarchy and then to final target cells. For example, you can allocate source data values specified by dimension values at the Category
level of a product
dimension to those at the Subcategory
level and then to those at the ProductID
level.
Multidimensional allocation path — You can allocate values first down one dimension and then down another dimension. The allocations can be direct or recursive or a combination of both. The results might vary depending on the order of the allocation.
Simultaneous multidimensional allocation path — You can do a direct allocation of values simultaneously to variable cells specified by multiple dimensions by creating a composite dimension that specifies the non-NA
cells of the variable to which you want to allocate values. You then use that composite as the basis of the allocation.
Restrictions When Designing a RELATION Statement for Allocation
Keep the following restrictions in mind when designing a RELATION statement:
Oracle OLAP can perform allocations on only one hierarchy in a dimension in one execution of the ALLOCATE command. When a dimension has multiple hierarchies, then you must supply a qdr argument to limit the relation to only one hierarchy.
An allocation specification must include either a RELATION statement or a VALUESET statement.
Only one RELATION statement or VALUESET statement may be used for each dimension in the allocation specification.
Locking Cells in the Allocation Path
Sometimes you want a cell to retain its existing value and to not be affected by an allocation. You can lock a value of the hierarchy of the dimension and thereby remove that value from the allocation path.When you lock a value above the detail level in a hierarchy, then you remove the branch of the hierarchy below that value from the allocation. To lock a value, use the PROTECT argument to the RELATION statement.
For example, when you want to allocate a yearly budget that you revise monthly, then you would set the value of the budget
at the Year
level of the time
dimension hierarchy. You would allocate data to the elements that are at the Month
level. As the year progresses, you would enter the actual data for a month and then lock that element and reallocate the remaining yearly budget value to see the new monthly targets that are required to meet the annual goal.
When you lock an element, you can specify whether the source value is renormalized. By default, when you lock an element of the hierarchy, the value of the cell of the target variable specified by that element is subtracted from the source value and the remainder is allocated to the target cells. When you do not want the source renormalized during the allocation, specify NONORMALIZE after the PROTECT argument.
Examples
For an example of using RELATION statements in an allocation statement, see the examples in the ALLOCMAP command, especially Example: Entering RELATION Statements in an Allocation Specification.