# ALLOCMAP

The ALLOCMAP command identifies an aggmap object as an allocation specification and enters the contents of the specification. In order to use AGGMAP to assign an allocation specification to n aggmap object, the definition must be the one most recently defined or considered during the current session. When it is not, you must first use a CONSIDER statement to make it the current definition.

An alternative to the AGGMAP command is the EDIT AGGMAP command, which is available only in OLAP Worksheet. The EDIT AGGMAP command opens an Edit window in which you can delete or change an allocation specification for an aggmap object. To use the OLAP Worksheet, to code an allocation specification follow the instructions given in "Editing a Newly Defined Aggmap to Code an Allocation Specification".

Syntax

ALLOCMAP [specification]

Arguments

specification

A multiline text expression that is the allocation specification for the current aggmap object. An allocation specification begins with an ALLOCMAP statement and ends with an `END` statement. Between these statements, you code one or more of the following statements depending on the calculation that you want to specify:

CHILDLOCK
DIMENSION (for allocation)
ERRORLOG
MEASUREDIM (for allocation)
RELATION (for allocation)
SOURCEVAL
VALUESET

Each statement is a line of the multiline text expression. When coding an ALLOCMAP statement at the command line level, separate statements with newline delimiters (`\n`), or use JOINLINES.

For a discussion of how to determine which statements to include, see "Designing an Allocation Specification".

Notes

Designing an Allocation Specification

Minimally, an allocation specification consists of a RELATION statement or a VALUESET statement However, you can create more complex allocation specifications and change the default settings for error handling by including additional OLAP DML statements in the specification, as follows:

1. For hierarchical allocations, a RELATION statement that specifies a self-relation that identifies the child-parent relationships of the hierarchy. List the statements in the order in which you want to perform the various operations; or if this is not important, list the RELATION statements in the same order as the dimensions appear in the variable definition.

2. For non-hierarchical allocations, a VALUESET statement that specifies the values to be used when allocating.

3. A CHILDLOCK statement that tells the ALLOCATE command whether to determine if RELATION statements in the aggmap specify lock on both a parent and a child element of a dimension hierarchy.

4. A DEADLOCK statement that tells the ALLOCATE command whether to continue an allocation when it encounters a deadlock, which occurs when the allocation cannot distribute a value because the targeted cell is locked or, for some operations, has a basis value of `NA.`

5. When a dimension is not shared by the target variable and the source or the basis objects, a DIMENSION (for allocation) statement that specifies a single value to set as the status of that dimension.

6. An ERRORLOG statement that specifies how many errors to allow in the error log specified by the ALLOCATE command and whether to continue the allocation when the maximum number of errors has occurred.

7. An ERRORMASK statement that specifies which error conditions to exclude from the error log.

8. When the source data comes from a variable, a SOURCEVAL statement that specifies whether ALLOCATE changes the source data value after the allocation.

Aggmap Type

You can use the AGGMAPINFO function to learn the type of an aggmap. An aggmap into which you have entered an allocation specification using the ALLOCMAP has the type ALLOCMAP and an aggmap into which you have entered an aggregation specification using an AGGMAP statement has the type AGGMAP. When you have defined an aggmap but have not yet entered a specification in it, its type is `NA`.

One RELATION for Each Dimension

An aggmap can have only one RELATION statement for any given dimension.

One Hierarchy For Each Dimension

An allocation operation proceeds down only one hierarchy in a dimension. When a dimension has more than one hierarchy, then you must limit the dimension to one of the hierarchies with a qualified data reference after the rel-name argument.

Examples

Example 9-36 Allocation Specification from an Input File

In this example an aggmap and its specification are defined in an ASCII disk file called `salesalloc.txt`. The statements in the file are then executed in the analytic workspace through the use of the INFILE statement. The statements in `salesalloc.txt` are the following.

```IF NOT EXISTS ('salesalloc')
THEN DEFINE salesalloc AGGMAP
ELSE CONSIDER salesalloc
ALLOCMAP
RELATION time.parent OPERATOR EVEN
RELATION product.parent OPERATOR EVEN
RELATION geography.parent OPERATOR EVEN
SOURCEVAL ZERO
END
```

To include the `salesalloc` aggmap in your analytic workspace, execute the following statement.

```INFILE 'salesalloc.txt'
```

The `sales.agg` aggmap has now been defined and contains three RELATION statements and the SOURCEVAL and DEADLOCK statements. In this example, the ALLOCATE statement allocates its source value evenly to all of the aggregate level cells and the detail level cells of the target variable because the relations `time.parent`, `product.parent`, and `geography.parent` relate each child dimension value to its parent in the dimension hierarchy. The DEADLOCK statement tells the ALLOCATE statement to log an error and continue the allocation when a branch of a target hierarchy is locked or has a value of `NA`. The SOURCEVAL statement tells ALLOCATE to assign a zero value to the source cells after allocating the source data.

You can now use the `salesalloc` aggmap with an ALLOCATE statement, such as.

```ALLOCATE sales USING salesalloc
```

Example 9-37 Allocation Specification from a Text Expression

In this example the `salesalloc` aggmap has already been defined. The specification is added to the aggmap as a text expression argument to the ALLOCMAP statement.

```CONSIDER salesalloc
ALLOCMAP
RELATION time.parent OPERATOR EVEN
RELATION product.parent OPERATOR EVEN
RELATION geography.parent OPERATOR EVEN
SOURCEVAL ZERO
```

Example 9-38 Specifying a Single Dimension Value in an Allocation Specification

This example proportionally allocates a value it calculates from the `sales` variable to cells in a `projectedsales` variable. The `sales` variable is dimensioned by the `time`, `product`, `customer`, and `channel` dimensions.

The example defines the `projectedsales` variable to use as the target of the allocation and the `increasefactor` formula to use as the source. The formula multiplies values from `sales` by ten percent. The example limits the `time` dimension and creates the `ytoq.rel` relation, which relates the year 2001 to the quarters of 2002. The next LIMIT commands limit the dimensions shared by `sales` and `projectedsales`.

The example creates an aggmap and uses the ALLOCMAP statement to enter a RELATION and a DIMENSION statement into the map. The RELATION statement specifies the `ytoq.rel` relation as the dimension hierarchy to use for the allocation and specifies that the allocation is proportional. The DIMENSION statement tells ALLOCATE to set the status of the `channel` dimension to `totalchannel` for the duration of the allocation.

```DEFINE projectedSales DECIMAL VARIABLE <time, SPARSE <product, customer>>
DEFINE increaseFactor DECIMAL FORMULA <product>
EQ sales * 1.1
LIMIT time TO '2001' 'Q1.02' TO 'Q4.02'
DEFINE YtoQ.rel RELATION time <time>
LIMIT time TO 'Q1.02' to 'Q4.02'
YtoQ.rel = '2001'
LIMIT time TO '2001' 'Q1.02' to 'Q4.02'
LIMIT product TO 'TotalProduct' 'Videodiv' 'Audiodiv' 'Accdiv'
LIMIT customers TO 'TotalCustomer'
DEFINE time.alloc AGGMAP
ALLOCMAP
RELATION YtoQ.rel OPERATOR PROPORTIONAL
DIMENSION channel 'TotalChannel'
END
ALLOCATE increaseFactor BASIS sales TARGET projectedSales USING time.alloc
```

The `sales` values that are the basis of the allocation are the following.

```CHANNEL: TOTALCHANNEL
CUSTOMERS: TOTALCUSTOMER
---------------PROJECTEDSALES---------------
--------------------TIME--------------------
PRODUCT           2001      Q1.02      Q2.02      Q3.02      Q4.02
------------     ------     ------     ------     ------     ------
TotalProduct      7000       1000       2000       3000       1000
Videodiv          4100        600       1100       1900        500
Audiodiv          1700        200        600        600        300
Accdiv            1200        200        300        500        200
```

The following shows a report of `projectedsales` for `totalchannel` after the allocation.

```CHANNEL: TOTALCHANNEL
CUSTOMERS: TOTALCUSTOMER
---------------PROJECTEDSALES---------------
--------------------TIME--------------------
PRODUCT           2001      Q1.02      Q2.02      Q3.02      Q4.02
------------     ------     ------     ------     ------     ------
TotalProduct       NA         NA         NA         NA         NA
Videodiv           NA        660       1210       2090        550
Audiodiv           NA        220        660        660        330
Accdiv             NA        220        330        550        220
```

Example 9-39 Entering RELATION Statements in an Allocation Specification

This example defines a `time.type` dimension and adds to it the two hierarchies of the `time` dimension. It defines the `time.time` relation that relates the hierarchy types (that is, `time.type`) to the `time` dimension. The example defines the `time.alloc` aggmap. With the ALLOCMAP command, it enters a RELATION statement in the aggmap. The RELATION statement specifies the values of the `time` dimension hierarchy to use in the allocation, limits the `time` dimension to one hierarchy with the QDR, and the specifies the EVEN operation for the allocation. The ALLOCATE command then allocates data from the source object to the target variable using the `time.alloc` aggmap. In the ALLOCATE command the source, basis, and target objects are the same `sales` variable.

```
DEFINE time.type TEXT DIMENSION
DEFINE time.time RELATION time <time, time.type>
DEFINE time.alloc AGGMAP

ALLOCMAP
RELATION time.time (time.type 'Fiscal') OPERATOR EVEN
END

ALLOCATE sales USING time.alloc
```

## CHILDLOCK

Within an allocation specification, a CHILDLOCK statement tells the ALLOCATE statement to determine if RELATION statements in the allocation specification have specified locks on both a parent and on a child of the parent in a dimension hierarchy. Locking both a parent and one of its children can cause incorrect allocation results.

Syntax

CHILDLOCK [DETECT|NODETECT]

Arguments

DETECT

Tells the ALLOCATE statement to detect that an allocation lock exists on a parent and also on one of its children in a dimension hierarchy. When it detects a locked parent and child, the ALLOCATE statement creates an entry in the error log for the allocation.

NODETECT

(Default) Tells the ALLOCATE statement to continue an allocation even when a lock exists on a parent and also on one of its children in a hierarchy.

Examples

For an example of using a CHILDLOCK statement in an allocation specification, see Example 9-78, "Creating an Aggmap for Allocation".

Within an allocation specification, a DEADLOCK statement tells the ALLOCATE statement what to do when it cannot distribute a source value to a target cell specified by a value in a dimension hierarchy because the target cell is either locked by a RELATION statement in the allocation specification or the cell has a basis value of `NA`.

Syntax

Arguments

SKIP

Tells the ALLOCATE statement to log the error and continue with the allocation even though it cannot distribute source values to cells specified by a branch of a dimension hierarchy because a target cell is locked or the basis value of the cell is `NA`.

NOSKIP

Tells the ALLOCATE statement to stop the allocation and to return an error when it cannot distribute source values to cells in a branch of a dimension hierarchy because a target cell is locked or the basis value is `NA`. This is the default action when you do not include a DEADLOCK statement in the aggmap used by the ALLOCATE command.

Examples

For examples of using a DEADLOCK statement in an allocation specification, see Example 9-36, "Allocation Specification from an Input File" and Example 9-37, "Allocation Specification from a Text Expression".

## DIMENSION (for allocation)

Within an allocation specification, a DIMENSION statement sets the status to a single value of a dimension. Within an allocation specification this dimension is a dimension that the source, basis, and target objects do not have in common. When an allocation specification does not specify such single values with DIMENSION statements, Oracle OLAP uses the current status values of the dimensions when performing the allocation.

You use a DIMENSION statement to ensure that the status of a dimension is set to the value that you want it to have for the allocation. You must use a separate DIMENSION statement for each dimension that is not shared by the source, basis, and target objects.

Syntax

DIMENSION dimension 'dimval'

Arguments

dimension

the name of the dimension that you want to limit.

dimval

The single value of the dimension to which you want the status of the dimension set for the duration of an allocation.

Examples

For an example of using a DIMENSION statement in an allocation specification, see Example 9-38, "Specifying a Single Dimension Value in an Allocation Specification".

## ERRORLOG

Within an allocation specification, an ERRORLOG statement specifies how many allocation error conditions to log and whether to continue or to stop the allocation when the specified maximum number of errors have been logged. You specify the error log with the ERRORLOG keyword to the ALLOCATE command.

Syntax

ERRORLOG [UNLIMITED|MAX <num>] [STOP|NOSTOP]

Arguments

UNLIMITED

Tells the ALLOCATE command to write an unlimited number of errors to the error log. This is the default setting.

MAX num

Specifies a maximum number of errors that ALLOCATE can write to the error log.

STOP
NOSTOP

Specifies whether to stop the allocation when ALLOCATE has written the maximum number of errors to the error log. When you specify STOP, the allocation stops. When you specify NOSTOP, the allocation continues but ALLOCATE does not write any more errors to the error log. When you have specified UNLIMITED, then the STOP and NOSTOP arguments have no effect and the allocation continues no matter how many errors occur.

Notes

Formatting the Error Log

The ALLOCERRLOGFORMAT option determines the contents and the formatting of the error log that you specify with the ERRORLOG argument to the ALLOCATE command. You can specify a header for the error log with the ALLOCERRLOGHEADER option.

Within an allocation specification, an ERRORMASK statement specifies the error conditions that you do not want to appear in the allocation error log. You specify the error log with the ERRORLOG keyword to the ALLOCATE command.

Syntax

Arguments

num...

The number of the error that you do not want to appear in the error log.

Examples

Example 9-40 Excluding CHILDLOCK Errors

To exclude a CHILDLOCK error, you would enter the following statement in the allocation specification.

```ERRORMASK 10
```

Example 9-41 Excluding All Allocation Errors

To exclude all errors, you would enter the following statement in the allocation specification.

```ERRORMASK 1 2 3 4 5 6 7 8 9 10
```

## MEASUREDIM (for allocation)

Within an allocation specification, a MEASUREDIM statement identifies the name of a measure dimension that is specified in the definition of an operator variable or an argument variable. However, you cannot specify a measure dimension when it is included in the definition of the aggmap object.

Syntax

MEASUREDIM name

Arguments

name

The name of the measure dimension. A measure dimension is a dimension that you define. The dimension values are names of existing variables.

MEASUREDIM (for aggregation) statement for the AGGMAP command

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

Arguments

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 more than one hierarchy, 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 one of the allocation methods described in Table 9-3, "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.

Table 9-3 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 9-3, "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 9-3, "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.

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

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 that you want 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.

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.

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.

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 more than one hierarchy, 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 more than one dimension 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 more than one hierarchy, 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 9-39, "Entering RELATION Statements in an Allocation Specification".

## SOURCEVAL

Within an allocation specification, a SOURCE VAL statement specifies the value that the ALLOCATE command assigns to a source cell in an allocation operation after it successfully allocates the value that the cell contained before the allocation.

The default value of SOURCEVAL is `NA`, which means that ALLOCATE sets the value of each of the allocated source cells to `NA` following the allocation. When you specify CURRENT as the SOURCEVAL, then the allocated source cells retain the values that they had before the allocation. When you specify `ZERO` as the SOURCEVAL, then ALLOCATE assigns a zero value to each source cell that is allocated.

Syntax

SOURCEVAL [CURRENT|ZERO|NA]

Arguments

CURRENT

Specifies that the value of a source cell after the allocation is the same as its value before the allocation.

ZERO

Specifies that the value of a source cell after the allocation is zero.

NA

Specifies that the value of a source cell after the allocation is `NA`. This is the default value.

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

Arguments

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 one of the operator types described in Table 9-3, "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 9-3, "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:

• Measure dimension -- Changes the allocation method depending upon the variable being allocated. The values of the measure dimension are the names of the variables to be allocated. It dimensions a text variable whose values identify the operation to be used to allocate each measure. The allocation specification must include a MEASUREDIM (for allocation) statement that identifies the measure dimension.

• Line item dimension -- Changes the allocation method depending upon the line item being allocated. 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 allocate each item. The operation variable is typically used to allocate line items over time.

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 9-3, "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 9-3, "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

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

• `TRUE` specifies that the results of the allocation are added to the target. (Default)

• `FALSE` specifies that the results of the allocation are subtracted from the target cell.

PROTECTRW lockvalueset

Specifies that the dimension members specified by lockvalueset cannot be the targets or source values of allocation. This lets users 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 will be 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 will probably not contain the original allocated amount

WEIGHTBY weightobj

Specifies a weight that should be applied to the target cell just before it is stored. This 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.

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.