Release 2 (9.2)
Part Number A95298-01
 Home |
 Book List |
 Contents |
 Index |
 Master Index |
 Feedback |
| 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. |
|