| Oracle® OLAP DML Reference 10g Release 2 (10.2) Part Number B14346-01 |
|
|
View PDF |
| Oracle® OLAP DML Reference 10g Release 2 (10.2) Part Number B14346-01 |
|
|
View PDF |
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".
|
See also: A number of options effect allocation. These options are listed in Table A-6, "Allocation Options". |
Syntax
ALLOCMAP [specification]
Arguments
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:
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 (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:
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.
For non-hierarchical allocations, a VALUESET statement that specifies the values to be used when allocating.
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.
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.
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.
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.
An ERRORMASK statement that specifies which error conditions to exclude from the error log.
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 7-21 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-22 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-23 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-24 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
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
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.
(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 10-10, "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 (for allocation) statement in the allocation specification or the cell has a basis value of NA.
Syntax
DEADLOCK [SKIP|NOSKIP]
Arguments
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.
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 7-21, "Allocation Specification from an Input File" and Example 7-22, "Allocation Specification from a Text Expression".
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
the name of the dimension that you want to limit.
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-23, "Specifying a Single Dimension Value in an Allocation Specification".
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
Tells the ALLOCATE command to write an unlimited number of errors to the error log. This is the default setting.
Specifies a maximum number of errors that ALLOCATE can write to the error log.
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
ERRORMASK <num...>
Arguments
The number of the error that you do not want to appear in the error log.
Examples
Example 7-25 Excluding CHILDLOCK Errors
To exclude a CHILDLOCK error, you would enter the following statement in the allocation specification.
ERRORMASK 10
Example 7-26 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
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
The name of the measure dimension. A measure dimension is a dimension that you define. The dimension values are names of existing variables.
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.
Syntax
RELATION rel-name [(qdr. . .)] OPERATOR {operator|} -
[NAOPERATOR operator] [REMOPERATOR operator] -
[PARENTALIAS dimension-alias-name] -
[ARGS {[FLOOR floorval] [CEILING ceilval] [MIN minval] [MAX maxval] -
[NAHANDLE {IGNORE|CONSIDER|PREFER}] -
[ADD|ASSIGN] [PROTECT [NONORMALIZE] [READWRITE|WRITE] lockvalueset] -
[WEIGHTBY [ADD|MULTIPLY] [WNAFILL nafillval] weightobj]}]
Arguments
An Oracle OLAP self-relation that specifies the values of a dimension hierarchy that identify the path of allocation. The cells in the target variable identified by the values in rel-name receive the allocated data.
One or more qualified data references that specify a single dimension value for each dimension of the relation that is not part of the self-relation. When the self-relation has 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.
Specifies one of the allocation methods described in Table 7-4, "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 7-4 Allocation Operators
| Operator | Description |
|---|---|
|
COPY |
Copies the allocation source to all of the target cells that have a basis data value that is not |
|
HCOPY |
Copies the allocation source to all of the target cells specified by the hierarchy even when the data in any of those cells is |
|
MIN |
Copies the allocation source to the target that has the smallest basis data value. |
|
MAX |
Copies the allocation source to the target that has the largest basis data value. |
|
FIRST |
Copies the allocation source to the first target cell that has a non- |
|
HFIRST |
Copies the allocation source to the first target cell specified by the hierarchy even when the current data value of that cell is |
|
LAST |
Copies the allocation source to the last target cell that has a non- |
|
HLAST |
Copies the allocation source to the last target cell specified by the hierarchy even when the current data value of that cell is |
|
EVEN |
Divides the allocation source by the number of target cells that have non- |
|
HEVEN |
Divides the allocation source by the number of target cells, including the ones that have |
|
PROPORTIONAL |
Divides the allocation source by the sum of the data values of the target cells that have non- |
The operator after the NAOPERATOR keyword specifies the operator that the ALLOCATE operation uses when it encounters an NA or lock-based deadlock. Valid operators are HFIRST, HLAST, and HEVEN which are described in Table 7-4, "Allocation Operators".
The operator after the REMOPERATOR keyword specifies the operator that the ALLOCATE operation uses when storing a remainder produced by an allocation. For example, assume you allocate the INTEGER 10 to three cells at the same level in a hierarchy, there is a remainder of 1. The REMOPERATOR specifies where you want the allocation operation to store this remainder. Valid operators for REMOPERATOR are MIN, MAX, FIRST, HFIRST, LAST, and HLAST which are described in Table 7-4, "Allocation Operators".
Indicates additional arguments specify additional parameters for the allocation operation. All of these arguments apply uniformly to the dimension hierarchy specified by rel-name.
Specifies specialized allocation depending on the parent (for example, weighting by parent or child). For dimension-alias-name, specify the name of the alias for the dimension of rel-name.
One or more arguments after the ARGS keyword that specify additional parameters for the allocation operation. All of these arguments apply uniformly to the dimension hierarchy specified by rel-name.
Specifies that when an allocated target data value is less than floorval, the data allocated to the target cell is NA. This argument applies to the relation only when the PROPORTIONAL operator is specified.
Specifies that when an allocated target data value is greater than ceilval, the data allocated to the target cell is NA. This argument applies to the relation only when the PROPORTIONAL operator is specified.
Specifies that when an allocated target data value is less than minval, the data allocated to the target cell is minval.
Specifies that when an allocated target data value is greater than maxval, the value allocated to the target cell is maxval.
Specifies how ALLOCATE treats NA values. Valid only when the OPERATOR is MIN or MAX.
IGNORE specifies that ALLOCATE does not consider NA values in a MIN or MAX operation. (Default)
CONSIDER specifies that ALLOCATE treats an NA value as a zero; however, when the data value of a target cell is actually zero, the zero cell receives the allocated data value and not the NA cell.
PREFER specifies that ALLOCATE treats an NA value as a zero and the NA has priority over a zero value, so the NA cell receives the allocated data value and not the cell with the actual zero value.
Specifies that ALLOCATE adds the allocated data to the current data in the target cell.
Specifies that ALLOCATE replaces the data in the target cell with the allocated data, which is the default behavior.
Specifies a set of dimension values 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.
Specifies that the allocation operation does not normalize the source data. Using NONORMALIZE effectively removes from the allocation the values of the hierarchy at and below the dimension values specified by lockvalueset.
Specifies that the locked data values cannot be used as source data in a subsequent allocation, thereby locking the data of the hierarchy below the lockvalueset values.
Specifies that the allocation cannot store data values in the cells identified by the lockvalueset dimension values but the allocation can use the data in those cells as source data in its subsequent steps. However, when in the aggmap you include a SOURCEVAL statement that specifies NA or ZERO and the locked cell is the source of an allocation, then ALLOCATE sets the value of the locked cell to NA or zero after the allocation.
Specifies that the allocation uses a the value specified by weightobj. Using this clause allows for processes such as unit or currency conversion.
Specifies that ALLOCATE adds the value specified by weightobj to the existing data value of the target and assigns the sum to the target cell.
(Default) Specifies that ALLOCATE multiplies the value specified by weightobj by the data value of the target and assigning the product.
Specifies that ALLOCATE replaces NA values in a cell before applying the value specified by weightobj to the nafillval value.
The value that the ALLOCATE replaces NA values with. When you specify the ADD option to the WEIGHTBY clause, the default NA fill value is 0; in all other cases, the default NA fill value is 1.
The name of an variable, formula, or relation whose value or values are the weights that Oracle OLAP applies to the allocated data just before it is stored in the target cell. When a relation is used, the target variable is referenced based on the weight relation and the cell is applied to the allocation target cell.
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 ALLOCMAP, especially Example 7-24, "Entering RELATION Statements in an Allocation Specification".
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
Specifies that the value of a source cell after the allocation is the same as its value before the allocation.
Specifies that the value of a source cell after the allocation is zero.
Specifies that the value of a source cell after the allocation is NA. This is the default value.
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:
|
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
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.
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.
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.
The operator argument after the OPERATOR keyword is a text expression that is one of the operator types described in Table 7-4, "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.
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-4, "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 aggmap 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 ofTEXT with a fixed width of 8. |
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-4, "Allocation Operators".
The operator after the REMOPERATOR keyword specifies the operator that the ALLOCATE operation uses when storing a remainder produced by an allocation. For example, assume you allocate the INTEGER 10 to three cells at the same level in a hierarchy, there is a remainder of 1. The REMOPERATOR specifies where you want the allocation operation to store this remainder. Valid operators for REMOPERATOR are MIN, MAX, FIRST, HFIRST, LAST, and HLAST which are described in Table 7-4, "Allocation Operators".
Indicates that additional arguments specify additional parameters for the allocation operation. All of these arguments apply uniformly to the valueset.
Specifies that when an allocated target value falls below the value specified in floorval, Oracle OLAP stores the value as NA.
Specifies that when an allocated target value exceeds the value specified in ceilval, then Oracle OLAP stores the value as NA.
Specifies that when an allocated target value falls below the value specified minval, then Oracle OLAP stores the value of minval in the target.
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.
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.
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.
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
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.
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).
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.
