Skip Headers

Oracle® OLAP DML Reference
10g Release 1 (10.1)

Part Number B10339-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page
Previous
Go to next page
Next
View PDF

ALLOCMAP

The ALLOCMAP command identifies an aggmap object as an allocation specification and enters the contents of the 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
DEADLOCK
DIMENSION (for allocation)
ERRORLOG
ERRORMASK
MEASUREDIM (for allocation)
RELATION (for allocation)
SOURCEVAL
VALUESET

Each statement is a line of the multiline text expression. 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 (for allocation) 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 (for allocation) 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 the AGGMAP command has the type AGGMAP. When you have defined an aggmap but have not yet entered a specification in it, its type is NA.


Allocation Options

A number of options effect allocation. These options are listed in Table 7-4, "Allocation Options".

Table 7-4 Allocation Options

Statement Description
ALLOCERRLOGFORMAT
An option that determines the contents and the formatting of the error log that you specify with the ERRORLOG argument to the ALLOCATE command.
ALLOCERRLOGHEADER
An option that determines the column headings for the error log that you specify with the ERRORLOG argument to the ALLOCATE command.
POUTFILEUNIT An option that identifies a destination for status information about an allocation or aggregation operation. A file unit uniquely identifies the destination file.


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 7-20 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
  DEADLOCK SKIP
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 (for allocation) 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 7-21 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
DEADLOCK SKIP

 

Example 7-22 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 (for allocation) 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 7-23 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
MAINTAIN time.type add 'Fiscal'
MAINTAIN time.type add 'Calendar'
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 (for allocation) 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

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. (Default)


DEADLOCK

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 (for allocation) statement in the allocation specification or the cell has a basis value of NA.

Syntax

DEADLOCK [SKIP|NOSKIP]

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.


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 7-22, "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.


ERRORMASK

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

ERRORMASK <num...>

Arguments

num...

The number of the error that you do not want to appear in the error log. For example, to exclude CHILDLOCK error, you would enter the following statement in the aggmap.

ERRORMASK 10

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

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.

Notes

See MEASUREDIM (for aggregation)


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.

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.


Note:

Keep the following restrictions in mind:
  • 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.


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

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

The operator after the OPERATOR keyword indicates one of the operator types described in Table 7-5, "Allocation Operators". The operator type specifies the method of the allocation. 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 7-5 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 7-5, "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 7-5, "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 IGNORE

Valid only when the OPERATOR is MIN or MAX, specifies that ALLOCATE does not consider NA values in a MIN or MAX operation. (Default)

NAHANDLE CONSIDER

Valid only when the OPERATOR is MIN or MAX, 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.

NAHANDLE PREFER

Valid only when the OPERATOR is MIN or MAX, 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.

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

READWRITE

The READWRITE keyword 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

The WRITE keyword 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 weightobj

The weightobj argument with WEIGHTBY is 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. Using this clause allows for processes such as unit or currency conversion. 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.

ADD

ADD specifies that ALLOCATE adds the weight value to the existing data value of the target and assigns the sum to the target cell.

MULTIPLY

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

WNAFILL nafillval

Specifies a value with which to replace an NA value in a cell before applying the weight object to the nafillval value. The default NA fill value is 1 unless you specify the ADD option to the WEIGHTBY argument, which changes the default NA fill value to 0.

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 a direct allocation path, a recursive descent hierarchy path, a multidimensional allocation path, or a simultaneous multidimensional allocation path.


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 ALLOCMAP, especially Example 7-23, "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 (for allocation) 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[Boolean]| 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 7-5, "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 7-5, "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. This is useful when a single aggmap is used to allocate several variables that need to be allocated with different methods. Whether you preallocate all of the measures in a single ALLOCATE statement or in separate statements, allocate uses the operation variable to identify the calculation method. 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 aggmap must include a MEASUREDIM (for allocation) command 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 7-5, "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 7-5, "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 [Boolean]

Boolean with 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.

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.