Oracle9i OLAP Developer's Guide to the OLAP DML Release 2 (9.2) Part Number A95298-01 |
|
Allocating Data, 5 of 5
With the OPERATOR
argument to a RELATION
command in an aggregation map for allocation, you must specify a method of operation for the allocation. The methods of operation fall into the following categories:
The copy operators are COPY
, HCOPY
, MIN
, MAX
, FIRST
, LAST
, HFIRST
, and HLAST
. The even distribution operators are EVEN
and HEVEN
, and the proportional distribution operator is PROPORTIONAL
.
The H versions of the operators are hierarchical operators that allocate data based on the hierarchical relationships specified in the relation object. The nonhierarchical operators, such as COPY
and EVEN
, do not assign a value to a target cell if the basis value for that cell is NA.
The hierarchical operators do not use basis values. Instead, they allocate data to all of the values in the dimension hierarchy specified by the relation even if the existing value of the target cell is NA. You must use the hierarchical operators carefully because they assign values to cells that have an NA basis and can therefore cause a huge increase in the detail level data.
With a RELATION
command, you can also use the ARGS
keyword to specify arguments that affect the allocation. With the arguments you can specify the following:
ALLOCATE
assigns NA to the target cell.PROTECT
argument protects the existing values of the cells and prevents them from being targets of the allocation. You can also specify whether the locked cell can be a source in the allocation. For example, if the valueset specifies a dimension value that is at an intermediate level in the dimension hierarchy and you use the WRITE
keyword, then ALLOCATE
uses the locked value as the source that it allocates down the hierarchy. If you use the READWRITE
keyword, then ALLOCATE
does not continue the allocation down that branch of the hierarchy. You can also specify whether to normalize the source value, which subtracts the locked value from the source before the allocation.ALLOCATE
adds to or multiplies by the allocated value before assigning the resulting value to the target cell. You can also specify whether to fill an NA value before applying the weighting factor.The HEVEN
operator allocates source data evenly to the target cells without considering a basis value. The MAX
operator allocates the source value to the target cell that corresponds to the highest basis value. Example 9-1 demonstrates the use of these operators and of the ADD
argument in a multidimensional allocation. The allocation path is directly from higher to lower values in the dimension hierarchies, with no allocation to intermediate hierarchy values.
The fcstunits
variable is dimensioned by the hierarchical dimensions time
, geog
, and product
. The dimensions are limited to one product, a few cities and regions, and the year 2002 and four months of 2002.
The cells of fcstunits
that are dimensioned by the lower hierarchical dimension values, which are the cities and the months, have values assigned to them. Those values are forecasts of the number of product units to ship to those cities in those months. In the cells dimensioned by the higher hierarchical dimension values, which are the YEAR02
and the region values, are additional product units to allocate to the cities and months.
A report of the fcstunits
variable produces the following.
PRODUCT: SHORTS - BOYS -----------------FCSTUNITS----------------- -------------------TIME-------------------- GEOG YEAR02 JUN02 JUL02 AUG02 -------------- ---------- ---------- ---------- ---------- EAST 755 NA NA NA WEST 515 NA NA NA CENTRAL 625 NA NA NA BOSTON NA 5,760 5,690 4,750 ATLANTA NA 7,600 8,520 7,300 CHICAGO NA 4,660 4,840 5,120 DALLAS NA 8,380 9,380 8,150 DENVER NA 5,400 6,080 5,170 SEATTLE NA 7,210 7,490 7,310
The geogcityreg
relation relates the city values to the regions. The timemonthyear
relation relates the month values to the year. Reports of the relations produce the following.
GEOG GEOGCITYREG --------- ----------- EAST NA WEST NA CENTRAL NA BOSTON EAST ATLANTA EAST CHICAGO CENTRAL DALLAS CENTRAL DENVER WEST SEATTLE WEST TIME TIMEMONTHYEAR ------ ------------- YEAR02 NA JUN02 YEAR02 JUL02 YEAR02 AUG02 YEAR02
The first RELATION
command in the xunitsalloc
aggregation map specifies that the first allocation occurs down the geog
dimension hierarchy specified by the geogcityreg
relation. The allocation evenly divides the values from the cells dimensioned by YEAR02
and the region values and assigns the results to the children of the regions. The REMOPERATOR LAST
keywords assign any remainder from the division to the last cell.
The values allocated to the regions in the first allocation do not appear in the report of the variable after the ALLOCATE
command completes because the SOURCEVAL
command in xunitsalloc
specifies that ALLOCATE
assign a zero value to cells that contained source values for the allocation. The region data allocated to the cities for YEAR02
is BOSTON 377
, ATLANTA 378
, CHICAGO 312
, DALLAS 313
, DENVER 257
, and SEATTLE 258
.
The second RELATION
command in xunitsalloc
specifies that a second allocation occur down the time
dimension hierarchy specified by the timemonthyear
relation. The source values of the allocation are the values of the cells dimensioned by YEAR02
and a city value. The allocation assigns each source value to the month that has the highest value for that city.
The ALLOCATE
command in the example specifies only the fcstunits
variable. Therefore, that variable is the source, the basis, and the target of the allocation. The command also specifies that the allocation use the xunitsalloc
aggregation map.
LIMIT product TO 'SHORTS - BOYS' LIMIT geog TO 'EAST' 'WEST' 'CENTRAL' - 'BOSTON' 'ATLANTA' 'CHICAGO' 'DALLAS' 'DENVER' 'SEATTLE' LIMIT time TO 'YEAR02' 'JUN02' TO 'AUG02' DEFINE xunitsalloc AGGMAP ALLOCMAP JOINLINES( - 'RELATION geogstcity OPERATOR HEVEN REMOPERATOR LAST' - 'RELATION timemonthyear OPERATOR MAX ARGS ADD' - 'SOURCEVAL ZERO') ALLOCATE fcstunits USING xunitsalloc REPORT fcstunits
The REPORT
of the fcstunits
variable after the allocation produces the following.
PRODUCT: SHORTS - BOYS -----------------FCSTUNITS----------------- -------------------TIME-------------------- GEOG YEAR02 JUN02 JUL02 AUG02 -------------- ---------- ---------- ---------- ---------- EAST 0 NA NA NA WEST 0 NA NA NA CENTRAL 0 NA NA NA BOSTON 0 6,137 5,690 4,750 ATLANTA 0 7,600 8,898 7,300 CHICAGO 0 4,660 4,840 5,432 DALLAS 0 8,380 9,693 8,150 DENVER 0 5,400 6,337 5,170 SEATTLE 0 7,210 7,748 7,310
Example 9-2 demonstrates the recursive copying of source data that is specified by a parent in a dimension hierarchy. The data is allocated to children of the parent and then that allocated data is the source of the allocation to the children of those children. It also demonstrates a second allocation in which different source data is copied to only one child and to its children.
The unitcost
variable is dimensioned by time
and prodid
. The prodid
dimension is a NUMBER
dimension that has product identification numbers as values. The first LIMIT
command sets the status of the prodid
dimension to one value. The next LIMIT
command sets the status of the time
dimension to the year 2002, the first two quarters of 2002, and the first six months of 2002.
The YEAR02
cell of unitcost
for the product is assigned the source value. A report of unitcost
produces the following.
-UNITCOST- --PRODID-- TIME 45285 -------------- ---------- YEAR02 34.25 Q1.02 NA Q2.02 NA JAN02 NA FEB02 NA MAR02 NA APR02 NA MAY02 NA JUN02 NA
Example 9-2 defines the costalloc
aggregation map and adds contents to it with the ALLOCMAP
command. The RELATION
command specifies the timeparent
relation as the path for the allocation and the HCOPY
operator as the method. The timeparent
relation relates the children in the time
dimension hierarchy to their parents.
The ALLOCATE
command uses the unitcost
variable as the source and the target of the allocation. Because the method is HCOPY
, the allocation does not use a basis object.
A report of unitcost
after the first allocation produces the following.
-UNITCOST- --PRODID-- TIME 45285 -------------- ---------- YEAR02 34.25 Q1.02 34.25 Q2.02 34.25 JAN02 34.25 FEB02 34.25 MAR02 34.25 APR02 34.25 MAY02 34.25 JUN02 34.25
The example then changes the source value for YEAR02
. It defines a valueset and limits the value of it to Q1.02
.
The second ALLOCMAP
command changes the contents of the aggregation map. The RELATION
command specifies the same relation and COPY
operation but it also specifies the PROTECT
argument. The SOURCEVAL
command specifies that the cells that contained source data are assigned a value of zero after the data is allocated.
The second allocation copies the value from the YEAR02
cell, but it locks the Q1.02
child and its children so that only the Q2.02
child and its children receive the allocated value.
A report of unitcost
after the second allocation produces the following.
-UNITCOST- --PRODID-- TIME 45285 -------------- ---------- YEAR02 0.00 Q1.02 34.25 Q2.02 35.00 JAN02 34.25 FEB02 34.25 MAR02 34.25 APR02 35.00 MAY02 35.00 JUN02 35.00
LIMIT prodid TO 45285 LIMIT time TO 'YEAR02' 'Q1.02' 'Q2.02' 'JAN02' TO 'JUN02' unitcost(time 'YEAR02' prodid 45285) = 34.25 DEFINE costalloc AGGMAP ALLOCMAP 'RELATION timeparent OPERATOR HCOPY' ALLOCATE unitcost USING costalloc unitcost(time 'YEAR02' prodid 45285) = 35.00 DEFINE lvset VALUESET time LIMIT lvset TO 'Q1.02' CONSIDER costalloc ALLOCMAP JOINLINES( - 'RELATION timeparent OPERATOR COPY ARGS PROTECT NONORMALIZE lvset' - 'SOURCEVAL ZERO') ALLOCATE unitcost USING costalloc
Use the HFIRST
and HLAST
operators when you want to allocate data to the first or last child of a parent without considering a basis value. Example 9-3 assigns cash balance forward and cash forward data from the actual
variable to the budget
variable and then allocates the data from the budget
cell specified by a parent time
value to one specified by a child time
value.
The actual
and budget
variables are dimensioned by the time
, line
, and product
dimensions. The timeparent
relation relates values of children in the time
dimension to their parents.
The first LIMIT
commands set the status of the time
and line
dimensions and limit the product
dimension to one value. A report of the actual
variable with that dimension status produces the following.
PRODUCT: DRESSES - WOMEN -----------------------ACTUAL------------------------- -------------------------TIME------------------------- LINE Q4.01 JAN02 FEB02 MAR02 Q1.02 --------- ---------- ---------- ---------- ---------- ---------- CASH B/F 1,000.00 NA NA NA NA CASH MVT 500.00 NA NA NA NA CASH C/F 1,500.00 NA NA NA NA
The source data for the allocation is assigned from the cash forward line of the actual
variable to the cash balance forward line of the budget
variable. The next LIMIT
command limits the line
dimension to CASH B/F
to restrict the allocation to that value. Example 9-3 then defines an aggregation map and adds contents to it with the ALLOCMAP
command. The contents are a single RELATION
command that specifies the HFIRST
operator. The ALLOCATE
command allocates the data from the Q1.02
parent to its first child, JAN02
.
Forecasting a fifty per cent increase in the cash forward amount by the end of the quarter, the example multiplies by 1.5 the value from the Q4.01
cash forward line of the actual
variable and assigns the result to the Q1.02
cash forward line of the budget
variable. The CONSIDER
and ALLOCMAP
commands change the contents of the aggregation map so that the RELATION
command specifies the HLAST
operator.
The line
dimension is limited to cash forward and then the ALLOCATE
command allocates the data from the Q1.02
parent to its last child, MAR02
. Finally, the last LIMIT
command resets the status of the line
dimension. A report of the budget
variable after the allocation produces the following.
PRODUCT: DRESSES - WOMEN ------------------------BUDGET------------------------ -------------------------TIME------------------------- LINE Q4.01 JAN02 FEB02 MAR02 Q1.02 --------- ---------- ---------- ---------- ---------- ---------- CASH B/F NA 1,500.00 NA NA 1,500.00 CASH MVT NA NA NA NA NA CASH C/F NA NA NA 2,250.00 2,250.00
LIMIT time TO 'Q4.01' 'JAN02' TO 'MAR02' 'Q1.02' LIMIT line TO 'CASH B/F' 'CASH MVT' 'CASH C/F' LIMIT product TO 'DRESSES - WOMEN' " Assign the value of actual Q4.01 CASH C/F to budget Q1.02 CASH B/F budget(time 'Q1.02' line 'CASH B/F') = actual(time 'Q4.01' line 'CASH C/F') LIMIT line TO 'CASH B/F' DEFINE qtomalloc AGGMAP ALLOCMAP 'RELATION timeparent OPERATOR HFIRST' " Allocate the Q1.02 value to the first month of the quarter ALLOCATE budget USING qtomalloc " Forecast a 50% increase in cash forward by the end of the quarter
budget(time 'Q1.02' line 'CASH C/F') = actual(time 'Q4.01' line 'CASH C/F') * 1.5
CONSIDER qtomalloc ALLOCMAP 'RELATION timeparent OPERATOR HLAST' LIMIT line TO 'CASH C/F' " Allocate the Q1.02 value to the last month of the quarter ALLOCATE budget USING qtomalloc LIMIT line TO 'CASH B/F' 'CASH MVT' 'CASH C/F'
The PROPORTIONAL
operator allocates source data proportionately to the target cells based on the values of the basis object. Example 9-4 demonstrates two proportional allocations of data recursively down the time
dimension hierarchy.
The actual
and budget
variables are dimensioned by the time
, line
, and product
dimensions. The timeparent
relation relates values of children in the time
dimension to their parents.
The first allocation allocates a forecasted revenue value from YEAR02
to the quarters and then to the months of that year. The allocation is based on the revenue from the same time periods for the previous year. Actual values for the first quarter of 2002 are then assigned to the cells of the budget
variables. The second allocation locks the budget
cells for the first quarter and its children, normalizes the source value by subtracting the locked quarter value from the source, and then allocates the remaining value to the other quarters and their children.
The first LIMIT
commands set the status of each of the line
and product
dimensions to one value and limit the product
dimension to the year, quarter, and month values for 2002.
The budget
variable for 2002 has values that were copied from the actual
variable for 2001. The example does not include that operation. The forecasted total revenue value for the product for the year 2002 is assigned to the budget
variable. That value is calculated to be ten per cent larger than the actual value for 2001.
The first REPORT
of the budget
variable produces the following.
PRODUCT: OUTERWEAR - MEN --BUDGET-- ---LINE--- TIME REVENUE -------------- ---------- YEAR02 1,100,000 Q1.02 275,000 Q2.02 225,000 Q3.02 200,000 Q4.02 300,000 JAN02 100,000 FEB02 90,000 MAR02 85,000 APR02 82,000 MAY02 70,000 JUN02 73,000 JUL02 64,000 AUG02 69,000 SEP02 67,000 OCT02 85,000 NOV02 105,000 DEC02 110,000
Example 9-4 then defines an aggregation map and adds contents to it with the ALLOCMAP
command. The contents are a single RELATION
command that specifies the PROPORTIONAL
operator. The ALLOCATE
command allocates the data from the YEAR02
parent down the hierarchy specified by the timeparent
relation.
The REPORT
of the budget
variable after the first allocation produces the following.
PRODUCT: OUTERWEAR - MEN --BUDGET-- ---LINE--- TIME REVENUE -------------- ---------- YEAR02 1,100,000 Q1.02 302,500 Q2.02 247,500 Q3.02 220,000 Q4.02 330,000 JAN02 110,000 FEB02 99,000 MAR02 93,500 APR02 90,200 MAY02 77,000 JUN02 80,300 JUL02 70,400 AUG02 75,900 SEP02 73,700 OCT02 93,500 NOV02 115,500 DEC02 121,000
The actual data for the first quarter of 2002 is assigned to the actual
variable and then copied to the budget
variable. The timelockvs
valueset is defined and limited to the single value Q1.02
.
A variable for a fileunit value is defined and is assigned the value returned by the FILEOPEN
function. The CONSIDER
and ALLOCMAP
commands change the contents of the aggregation map so that the RELATION
command includes the PROTECT
argument.
The second ALLOCATE
command allocates the data from the YEAR02
parent down the hierarchy specified by the timeparent
relation but this allocation first subtracts the locked value for Q1.02
from the source value before distributing the remaining value. The command also sends error or informational messages to the allocerrlog
file, which is specified by the errlogfunit
fileunit.
The contents of the allocerrlog
file are the following.
Dim Source Basis TIME BUDGET BUDGET Description -------- -------- -------- ----------- YEAR02 850000 1100000 Renormalizing data (6)
The source value for the allocation after normalization is 850,000 instead of the original value of 1,100,000. The REPORT
of the budget
variable after the second allocation, with the Q1.02
value protected, produces the following.
PRODUCT: OUTERWEAR - MEN --BUDGET-- ---LINE--- TIME REVENUE -------------- ---------- YEAR02 1,100,000 Q1.02 250,000 Q2.02 263,793 Q3.02 234,483 Q4.02 351,724 JAN02 90,000 FEB02 82,000 MAR02 78,000 APR02 96,138 MAY02 82,069 JUN02 85,586 JUL02 75,034 AUG02 80,897 SEP02 78,552 OCT02 99,655 NOV02 123,103 DEC02 128,966
LIMIT line TO 'REVENUE' LIMIT product TO 'OUTERWEAR - MEN' LIMIT time TO 'YEAR02' TO 'DEC02' " Specify no decimal places DECIMALS = 0 budget(time 'YEAR02') = actual(time 'YEAR01') * 1.1 REPORT DOWN time budget DEFINE budgalloc AGGMAP ALLOCMAP 'RELATION timeparent OPERATOR PROPORTIONAL' ALLOCATE budget USING budgalloc REPORT DOWN time budget " Assign actual values for first quarter of 2002. actual(time 'Q1.02' line 'REVENUE' product 'OUTERWEAR - MEN') = 250000 actual(time 'JAN02' line 'REVENUE' product 'OUTERWEAR - MEN') = 90000 actual(time 'FEB02' line 'REVENUE' product 'OUTERWEAR - MEN') = 82000 actual(time 'MAR02' line 'REVENUE' product 'OUTERWEAR - MEN') = 78000 LIMIT time TO 'Q1.02' 'JAN02' 'FEB02' 'MAR02' " Copy the actual values to the budget variable budget = actual LIMIT time TO 'Q4.01' 'JAN02' 'FEB02' 'MAR02' 'Q1.02' DEFINE timelockvs valueset time LIMIT timelockvs TO 'Q1.02' DEFINE errlogfunit VARIABLE INTEGER errlogfunit = FILEOPEN('allocerrlog' WRITE) CONSIDER budgalloc ALLOCMAP 'RELATION timeparent OPERATOR PROPORTIONAL ARGS PROTECT timelockvs' ALLOCATE budget USING budgalloc ERRORLOG errlogfunit REPORT DOWN time budget
|
Copyright © 2001, 2002 Oracle Corporation. All Rights Reserved. |
|