Previous
Previous
 
Next
Next


VALUESET

Within an allocation specification, a VALUESET statement specifies the target dimension values of an allocation. A dimensioned valueset can be used to specify the allocation targets for an entire non-hierarchical dimension such as a measure or line dimension.


Note:

Keep the following restrictions in mind:
  • An allocation specification must include at least one RELATION statement or a VALUESET statement.

  • You can only specify one RELATION statement or VALUESET statement for each dimension specified in the allocation specification.


Syntax

VALUSET vs-name[(nondimvalueset)| qdr... ] OPERATOR operator | opvar

     [NAOPERATOR text -exp] [REMOPERATOR text -exp] -

     [ARGS [FLOOR floorval] [CEILING ceilval] –

     [MIN minval] [MAX maxval] –

     [ADDT [ {TRUE|FALSE} | ASSIGN] –

     [{PROTECTRW| PROTECTW} [NONORMALIZE] lockvalueset] –

     [WEIGHTBY [ADD] weightobj [WNAFILL nafillval]] | -

     [WEIGHTBY WEIGHTVAR wobjr]]

Parameters

vs-name

Specifies the name of a valueset object that specifies the values of a dimension which are the path of allocation. The cells in the target variable identified by the values in vs-name receive the allocated data.

nondimvalueset

When vs-name is a dimensioned valueset, specifies a nondimensioned valueset that is the status used to loop the valueset dimension. When you do not include nondimvalueset or qdr, Oracle OLAP uses the default logical order of the dimensions, not its current status.

qdr

When vs-name is a a non-dimensioned valueset, one or more qualified data references that specify the dimension values to use when allocating data.

OPERATOR operator

The operator argument after the OPERATOR keyword is a text expression that is an operator type described in Table: Allocation Operators. The operator type specifies the method of the allocation. The method determines the cells of the target variable for the vs-name relation to which ALLOCATE assigns a value. Unless you have specified a different status using dimorder valueset, for the FIRST, LAST, HFIRST, and HLAST operators, ALLOCATE uses the default logical order of the allocation dimension to determine the cell. There is no default operator for allocation.

OPERATOR opvar

The opvar argument after OPERATOR keyword specifies a TEXT variable that specifies different the operation for each of the values of a dimension. The values of the variable are the allocation operators described in Table: Allocation Operators. An operator variable is used to change the allocation operator with the values of one dimension. The opvar argument is used with the following types of dimensions:

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


Tip:

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

NAOPERATOR text-exp

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

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 that additional arguments specify additional parameters for the allocation operation. All of these arguments apply uniformly to the valueset.

FLOOR floorval

Specifies that when an allocated target value falls below the value specified in floorval, Oracle OLAP stores the value as NA.

CEILING ceilval

Specifies that when an allocated target value exceeds the value specified in ceilval, then Oracle OLAP stores the value as NA.

MIN minval

Specifies that when an allocated target value falls below the value specified minval, then Oracle OLAP stores the value of minval in the target.

MAX maxval

Specifies that when an allocated target value exceeds the value specified maxval, then Oracle OLAP stores the value of maxval in the target

ADDT {TRUE|FALSE}

The ADDT phrase specifies the sign of the addition when Oracle OLAP adds target cells to the existing contents of the target cell:

PROTECTRW lockvalueset

Specifies that the dimension members specified by lockvalueset cannot be the targets or source values of allocation. Using this phrase allows users to specify an allocation "lock" on a hierarchical subtree. The current contents of the target cell are subtracted from the source and the source and basis is renormalized.

PROTECTW lockvalueset

Specifies that the dimension members specified by lockvalueset cannot be the targets of an allocation. However, these target cells are used as the source values for subsequent steps in the allocation process. When the SOURCEVAL statement is set to 0 (zero) or NA and these values are reallocated, they are set appropriately.

NONORMALIZE

Specifies that Oracle OLAP should not renormalize the source and basis based on the protected cells. Specifying this keyword has an effect similar to removing a sub-branch from a hierarchy. Frequently, when you use this keyword, if, after allocation, data is aggregated from the allocation level, the source cell probably does not contain the original allocated amount

WEIGHTBY weightobj

Specifies a weight that should be applied to the target cell just before it is stored. Using this phrase allows for processes such and unit or currency conversion. Value weight objects are variables, formulas and relations. When a relation is used, the target variable is referenced based on the weight relation, and the cell is applied the allocation target cell.

ADD

Specifies that Oracle OLAP adds the value of the weight to the allocation target rather than using multiplication.

WNAFILL nafillval

Specifies the default value of the weight variable that should be used. When you do not include an ADD clause, the default value of nafillval is 1. When you include the ADD clause, the default value of nafillval is 0 (zero).

WEIGHTBY WEIGHTVAR wobj

Specifies that the allocated data should be weighted. The wobj argument is the name of a variable, relation, or formula whose values are the weights that Oracle OLAP applies to the allocated data just before it is stored in the target cell. Using this clause allows for processes such as unit or currency conversion and enables you to use different weight objects with the different operators specified in the operator variable you created for the OPERATOR opvar clause.