Previous
Previous
 
Next
Next


RELATION (for allocation)

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 nafillvalweightobj]}]

Parameters

rel-name

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.

qdr. . .

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.

OPERATOR operator

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

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 NA. When the source data is NA, then that NA value is not allocated to the target cells of that allocation.

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-NA basis data value.

HFIRST

Copies the allocation source to the first target cell specified by the hierarchy even when the current data value of that cell is NA

LAST

Copies the allocation source to the last target cell that has a non-NA basis data value.

HLAST

Copies the allocation source to the last target cell specified by the hierarchy even when the current data value of that cell is NA

EVEN

Divides the allocation source by the number of target cells that have non-NA basis data values and applies the quotient to each target cell.

HEVEN

Divides the allocation source by the number of target cells, including the ones that have NA values, and applies the quotient to each target cell.

PROPORTIONAL

Divides the allocation source by the sum of the data values of the target cells that have non-NA basis data values, multiplies the basis data value of each target cell by the quotient, and applies the resulting data to the target cell.


NAOPERATOR operator

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.

REMOPERATOR operator

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.

ARGS

Indicates additional arguments specify additional parameters for the allocation operation. All of these arguments apply uniformly to the dimension hierarchy specified by rel-name.

PARENTALIAS dimension-alias-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.

ARGS argument...

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.

FLOOR floorval

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.

CEILING ceilval

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.

MIN minval

Specifies that when an allocated target data value is less than minval, the data allocated to the target cell is minval.

MAX maxval

Specifies that when an allocated target data value is greater than maxval, the value allocated to the target cell is maxval.

NAHANDLE

Specifies how ALLOCATE treats NA values. Valid only when the OPERATOR is MIN or MAX.

ADD

Specifies that ALLOCATE adds the allocated data to the current data in the target cell.

ASSIGN

Specifies that ALLOCATE replaces the data in the target cell with the allocated data, which is the default behavior.

PROTECT lockvalueset

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.

NONORMALIZE

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.

READWRITE

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.

WRITE

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.

WEIGHTBY

Specifies that the allocation uses a the value specified by weightobj. Using this clause allows for processes such as unit or currency conversion.

ADD

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.

MULTIPLY

(Default) Specifies that ALLOCATE multiplies the value specified by weightobj by the data value of the target and assigning the product.

WNAFILL

Specifies that ALLOCATE replaces NA values in a cell before applying the value specified by weightobj to the nafillval value.

nafillval

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.

weightobj

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:

Restrictions When Designing a RELATION Statement for Allocation

Keep the following restrictions in mind when designing a RELATION statement:

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.