| Oracle® OLAP DML Reference 11g Release 1 (11.1) Part Number B28126-01 |
|
|
View PDF |
| Oracle® OLAP DML Reference 11g Release 1 (11.1) Part Number B28126-01 |
|
|
View PDF |
The ALLOCATE command calculates lower-level data from upper-level data by allocating variable data down a hierarchical dimension. Frequently you allocate data for budgeting, forecasting, and profitability analysis.
Syntax
ALLOCATE source [SOURCE conjoint] [BASIS basisname [ACROSS dimname]] - [TARGET targetname [TARGETLOG targetlogname]] - [USING aggmap] [ERRORLOG errorlogfileunit]
Arguments
A variable or formula that provides the values to allocate. When the source object is a formula, you must also specify a variable with the TARGET keyword. When you specify a variable as source and you do not specify a target variable or a basisname variable, then ALLOCATE uses source as the basis and the target.
Specifies a conjoint dimension that contains a list of cells the user has changed. The ALLOCATE command uses this list to produce the smallest target status needed to allocate all of the changed source cells.
Specifies a variable, relation, or formula that provides the data on which the allocation is based. That data determines which cells of the target receive allocated values and, in an even or proportional operation, the amount of the source allocated to a target cell.
When the OPERATOR specified by a RELATION (for allocation) statement in aggmap is a COPY operator (COPY, MIN, MAX, FIRST, LAST), the basis tells the ALLOCATE command which target cells to update. When the OPERATOR specified is EVEN, then ALLOCATE derives the counts that it uses for allocation from the basis. When the OPERATOR specified is the PROPORTIONAL, then ALLOCATE uses the basis data to determine the amount to allocate to each target cell. When the OPERATOR is HCOPY, HFIRST, HLAST, or HEVEN, then ALLOCATE does not use a BASIS object. Instead, it allocates the source data to all of the target cells in the dimension hierarchy that is specified by the relation named in the RELATION statement.
When you specify the same variable as both the basis and the target, the current values of the target cells determine the allocation. When you do not specify a basis, then the ALLOCATE command uses the source as the basis.
Specifies a dimension, which can be a named composite, that the ALLOCATE command loops over to discover the cells in a basis. Because a basis can be a formula, you can realize a significant performance advantage by supplying a looping dimension that eliminates the sparsity from the basis loop.
Specifies a variable to hold the allocated values. When the source object is a formula, then you must specify a target. When the source object is a variable and you do not specify a target, then ALLOCATE uses the source variable as the target.
Specifies a variable (identically dimensioned to the targetname variable), or a relation that specifies such a variable, to which ALLOCATE assigns a copy of the allocation. For instance, when ALLOCATE assigns the value of 100 to the cell of the costs variable that is specified by the time and product dimension values Jan01 and TV, and the targetlog relation specifies the cell of the costacct variable that is specified by the same dimension values, then ALLOCATE assigns the value of 100 to the specified costacct cell, also.
Specifies the name of a previously-defined aggmap to use for the allocation. When you do not include this phrase, the command uses the default allocation specification for the variable as previously specified using the $ALLOCMAP property.
Specifies a file unit that ALLOCATE uses for logging allocation deadlocks, errors, or other information. When the allocation does not generate any deadlocks or errors, ALLOCATE sets errorlogname to NA. When the allocation produces one or more deadlocks or errors, the events are sent to the specified file. ALLOCATE writes one line in the file for each allocation source that remains unallocated.
When you do not specify a file unit with ERRORLOG, ALLOCATE sends the information to the standard output device.
Notes
Preserving Original Basis Values
Often the source, basis, and target objects are the same variable and therefore the original values in the cells of the target variable determine the proportions of the allocation. The allocation overwrites those original values in the target cells with the allocated values. To preserve original values in a variable, specify the original variable as the basis object and save the allocated values to a new variable as the target object. Using different basis and target objects makes it possible for you to preview the allocated data. When you then want to store the allocated values in the same variable as the basis, you can perform the allocation again with the same object as the basis and the target. Another example of using different basis and target objects is using an actuals variable as the basis of the allocation and a budget variable as the target.
Using a Formula as a Source or Basis
Source and basis objects can be formulas, which makes it possible for you to make complex computations and have the results be the source or basis object. For example, when you want to see the sales of individual products that would be necessary to produce a thirty percent increase in sales for the next year, you could express the increase in the following formula.
DEFINE actualsWanted DECIMAL FORMULA <time, product> EQ LAG(actuals, 1, time) * 1.3
You would then use ACTUALSWANTED as the source object with the ALLOCATE command. In this example, you would use the ACTUALS variable as the basis.
Tracking Multiple Allocations
When you specify a variable with the TARGETLOG argument, you can store an allocated value in that variable as well as in the target variable. This double entry allocation makes it possible for you to track multiple allocations to the same target cell. For example, when you allocate a series of different costs to the same costs centers, then each allocation increases the values in the target cells. You can keep track of the individual allocations by specifying a different targetlogname variable for each allocation.
Logging Allocation Errors
When you specify a file with the ERRORLOG argument, you can record errors that result from locks and NA basis values. The log can provide feedback to an application about which source values remain unallocated. You can use the information to modify the allocation, for example by using a hierarchical operator such as HEVEN in a RELATION statement in the aggmap. You can use the ALLOCERRLOGHEADER and ALLOCERRLOGFORMAT options to format the error log. Within an allocation specification, you can specify other aspects of the error log using the ERRORLOG and ERRORMASK statements.
Logging the Progress of an Allocation
When you specify a file with the POUTFILEUNIT option, then you can record and monitor the progress of an allocation. You can use the file to get feedback during the course of a lengthy allocation and to gain information that might be useful for optimizing the allocation in the future.
Examples
Example 9-33 Direct Even Allocation
This example allocates a value specified at one level of the time dimension hierarchy directly to the variable target cells that are specified by lower level values in the hierarchy without allocating values to an intermediate level. The timemonthyear relation specifies the hierarchical relationship of the time values. The source, basis, and target of the allocation are all the same variable, PROJBUDGET, which is dimensioned by division, time, and line. The time dimension is a nonunique concat dimension that has as its base dimensions year, quarter, and month. The time dimension is limited to <year: Yr02>, <quarter: Q1.02>, <quarter: q1.02>, and <month: Jan02> to <month: Jun02>. The following statements define the projbudget variable, set the value of a cell in to 6000 and then report the variable.
DEFINE projbudget VARIABLE DECIMAL <division time line> projbudget(division 'CAMPING' time '<YEAR: YR02>' line 'MARKETING') = 6000 REPORT projbudget
The preceding statement produces the following results.
LINE: MARKETING
-PROJBUDGET--
--DIVISION---
TIME CAMPING
---------------- -------------
<year: Yr02> 6,000.00
<quarter: Q1.02> NA
<quarter: Q2.02> NA
<month: Jan02> NA
<month: Feb02> NA
<month: Mar02> NA
<month: Apr02> NA
<month: May02> NA
<month: Jun02> NA
The following statements define a self-relation on the time dimension, relate the month values directly to the year values, and report the values of the relation.
DEFINE timemonthyear RELATION time <time> LIMIT month TO 'JAN02' TO 'JUN02' timemonthyear(time month) = '<YEAR: YR02>' REPORT timemonthyear
The preceding statement produces the following results.
TIME TIMEMONTHYEAR ---------------- ------------- <year: Yr02> NA <quarter: Q1.02> NA <quarter: Q2.02> NA <month: Jan02> <year: Yr02> <month: Feb02> <year: Yr02> <month: Mar02> <year: Yr02> <month: Apr02> <year: Yr02> <month: May02> <year: Yr02> <month: Jun02> <year: Yr02>
The following statements define an aggmap and enter statements into the allocation specification. They allocate the value that is specified by <year: Yr02> from projbudget to the cells of the same variable that are specified by the month dimension values, and then report projbudget. The target cells of the variable have NA values so the RELATION statement in the allocation specification specifies the HEVEN operator. The ALLOCATE command specifies only one variable, projbudget, so that variable is the source and target of the allocation. No basis object is required because the allocation is an HEVEN operation. The allocation is directly from the year source value to the month target values because that is the hierarchy specified by the relation in the allocation specification.
DEFINE projbudgmap AGGMAP ALLOCMAP RELATION timemonthyear OPERATOR HEVEN END ALLOCATE projbudget USING projbudgmap REPORT projbudget
The preceding statement produces the following results.
LINE: MARKETING
-PROJBUDGET--
--DIVISION---
TIME CAMPING
---------------- -------------
<YEAR: YR02> 6,000.00
<QUARTER: Q1.02> NA
<QUARTER: Q2.02> NA
<MONTH: JAN02> 1,000.00
...
<MONTH: JUN02> 1,000.00
Example 9-34 Recursive Even Allocation with a Lock
This example allocates a value specified at one level of the time dimension hierarchy first to the target cells at an intermediate level in a variable and then to the cells that are specified by the lowest level values in the hierarchy. The timeparent relation specifies the hierarchical relationship of the time values. The source, basis, and target of the allocation are projbudget. The status of the division, time, and line dimensions are the same as the direct allocation example. At the beginning of this example, the projbudget variable again has just the single value, 6000, in the cell specified by <year: Yr02>.
DEFINE timeparent RELATION time <time> LIMIT quarter TO 'Q1.02' 'Q2.02' timeparent(time quarter) = '<YEAR: YR02>' LIMIT month TO 'JAN02' TO 'MAR02' timeparent(time month) = '<QUARTER: Q1.02>' LIMIT month TO 'APR02' TO 'JUN02' timeparent(time month) = '<QUARTER: Q1.02>' REPORT timeparent
The preceding statement produces the following results.
TIME TIMEPARENT ---------------- ------------- <year: Yr02> NA <quarter: Q1.02> <year: Yr02> <quarter: Q2.02> <year: Yr02> <month: Jan02> <quarter: Q1.02> <month: Feb02> <quarter: Q1.02> <month: Mar02> <quarter: Q1.02> <month: Apr02> <quarter: Q2.02> <month: May02> <quarter: Q2.02> <month: Jun02> <quarter: Q2.02>
This example demonstrates locking a cell so that it does not participate in the allocation. Locking a cell requires a valueset, so the following statements define one, limit the time dimension to the desired value, assign a value to the valueset, and then reset the status of the time dimension.
DEFINE timeval TO '<QUARTER: Q2.02>' LIMIT time TO '<Year: YR02>' '<Quarter: Q1.02>' '<Quarter: Q2.02>' - '<month: Jan02>' '<month: Feb02>' '<month: Mar02>' - '<month: Apr02>' '<month: May02>' '<month: Jun02>
The following statements revise the specification of the aggmap named projbudgmap. This time the RELATION statement in the allocation specification specifies the timeparent relation, the HEVEN operator, and the PROTECT argument. The READWRITE keyword specifies that the children of the locked cell also do not participate in the allocation. The NONORMALIZE keyword specifies that the value of the locked cell is not subtracted from the source value before it is allocated to the target cells. The statements then allocate the source value and report the results.
CONSIDER projbudgmap ALLOCMAP RELATION timeparent OPERATOR HEVEN ARGS PROTECT NONORMALIZE READWRITE timeval END ALLOCATE projbudget USING projbudgmap REPORT projbudget
The preceding statement produces the following results.
LINE: MARKETING
-PROJBUDGET--
--DIVISION---
TIME CAMPING
---------------- -------------
<year: Yr02> 6,000.00
<quarter: Q1.02> 6,000.00
<quarter: Q2.02> NA
<month: Jan02> 2,000.00
<month: Feb02> 2,000.00
<month: Mar02> 2,000.00
<month: Apr02> NA
<month: May02> NA
<month: Jun02> NA
Example 9-35 Recursive Proportional Allocation
This example uses the same relation as the recursive even allocation but it uses the PROPORTIONAL operator and it does not lock any cells. Because a proportional allocation uses the values of the basis object to calculate the values to assign to the target cells, the projbudget variable has values assigned to each of its cells. The value of the <year: Yr02> cell is 6000., which was assigned to that cell. It is not the value an aggregation of the lower levels. A report of projbudget before the allocation produces the following results.
LINE: MARKETING
-PROJBUDGET--
--DIVISION---
TIME CAMPING
---------------- -------------
<year: Yr02> 6,000.00
<quarter: Q1.02> 1,000.00
<quarter: Q2.02> 2,000.00
<month: Jan02> 300.00
<month: Feb02> 100.00
<month: Mar02> 600.00
<month: Apr02> 400.00
<month: May02> 800.00
<month: Jun02> 800.00
The following statements replace the previous specification of the aggmap with the new RELATION statement, which specifies the PROPORTIONAL operator. The allocation specification includes a SOURCEVAL ZERO statement, which specifies that the source value is replace with a zero value after the allocation (see the SOURCEVAL statement of the ALLOCMAP command for more information). The statements then allocate the source value and report the result.
CONSIDER projbudgmap
ALLOCMAP JOINLINES('RELATION timeparent OPERATOR PROPORTIONAL timeval' -
'SOURCEVAL ZERO' -
'END')
ALLOCATE projbudget USING projbudgmap
REPORT projbudget
The preceding statement produces the following results.
TIME TIMEPARENT
LINE: MARKETING
-PROJBUDGET--
--DIVISION---
TIME CAMPING
---------------- -------------
<year: Yr02> 0
<quarter: Q1.02> 2,000.00
<quarter: Q2.02> 4,000.00
<month: Jan02> 600.00
<month: Feb02> 200.00
<month: Mar02> 1,200.00
<month: Apr02> 800.00
<month: May02> 1,600.00
<month: Jun02> 1,600.00
