Using The Oracle 9i OLAP ALLOCATE Command
Thanks to Simon Dunn for this Oracle 9i OLAP Technical Article.
"There is a new feature in Oracle 9i OLAP which allows the allocation of numbers. Previously, using Express, any allocations had to be hand-crafted using the Express SPL whilst Express-based applications such as Oracle Financial Analyzer had the ability built in to allocate downwards through hierarchies on a number of different basis. In Oracle 9i OLAP the ability to do allocations has been made more mainstream and a suite of commands and functions have been introduced so that programming solutions are more easily achieved. No doubt this new function will play a large part in the forthcoming releases or EPB (Enterprise Planning & Budgeting). These new functions build on the introduction of Aggregation Maps introduced in later releases of Oracle Express and the purpose of this document is to introduce the allocation methodology employed by these new commands and act as a reference. It will become clear that this is a large and complex area of functionality and so whilst examples have been included they are not exhaustive but exist as to give an insight into how the commands work.
Aggregation Maps & the AGGREGATE command
Let's start with a reminder on aggregation maps. These were introduced along with the AGGREGATE command as a process for consolidating data, an alternative to the ROLLUP command. Whilst the AGGREGATE command actually initiated the process of consolidation the Aggregation Map was a new database object that specified how the data should be rolled up. So the first step was to define the AGGMAP, then, treating it like a program, enter commands into it that AGGREGATE command would interpret at run time. With hierarchical based rollups the normal method would be to specify a self-relating relation for each dimension that required consolidation. Let's have a look at an example. We have a very simple database with three dimensions:
DEFINE APP.TIME DIMENSION MONTH
DEFINE PRODUCT DIMENSION TEXT
DEFINE STORE DIMENSION TEXT
and a variables SALES
DEFINE SALES VARIABLE INTEGER <APP.TIME PRODUCT STORE>
The dimension STORE has the following values
STORE
--------------
BRIGHTON
WORTHING
EASTBOURNE
GUILDFORD
REDHILL
WEYBRIDGE
SURREY
SUSSEX
TOTAL
The dimension PRODUCT has the following values
PRODUCT
--------------
TOTAL
FRUIT
VEGETABLES
APPLES
ORANGES
PEARS
TURNIP
ONION
CAULIFLOWER
The dimension APP.TIME has the following values
APP.TIME
--------------
JAN03
FEB03
MAR03
APR03
MAY03
JUN03
JUL03
AUG03
SEP03
OCT03
NOV03
DEC03
With data being entered at product and store level the requirement is to consolidate this over PRODUCT and STORE to give totals. To assist in this self-relations are specified like so:
DEFINE R.STORE RELATION STORE <STORE>
| STORE | R.STORE |
| BRIGHTON | SUSSEX |
| WORTHING | SUSSEX |
| EASTBOURNE | SUSSEX |
| GUILDFORD | SURREY |
| REDHILL | SURREY |
| WEYBRIDGE | SURREY |
| SURREY | TOTAL |
| SUSSEX | TOTAL |
| TOTAL | NA |
and,
DEFINE R.PRODUCT RELATION PRODUCT <PRODUCT>=
PRODUCT R.PRODUCT
TOTAL NA
FRUIT TOTAL
VEGETABLES TOTAL
APPLES FRUIT
ORANGES FRUIT
PEARS FRUIT
TURNIP VEGETABLES
ONION VEGETABLES
CAULIFLOWER VEGETABLES
Then the AGGMAP is defined like this
DEFINE ADDSALES AGGMAP <APP.TIME PRODUCT STORE>
and then the definition is entered using the AGGMAP command like this:
AGGMAP JOINLINES('RELATION R.PRODUCT' 'RELATION R.STORE' 'END')
then the data can be consolidated by using the AGGREGATE command like this:
AGGREGATE SALES USING ADDSALES
This uses the two self-relations specified in the AGGMAP to consolidate over PRODUCT and STORE.
Using ALLOCATE
Like the AGGREGATE command the ALLOCATION process also uses AGGMAPs to see how it should handle the data when performing an allocation. They key difference though is that the AGGMAP is specified as an ALLOCMAP. Let's work our way through an example.
Using the same database from above we create a new variable called BUDGET.SALES
DEFINE BUDGET.SALES VARIABLE INTEGER <APP.TIME PRODUCT STORE>
As you can see this shares the same dimensionality as the previous SALES variable and we copy the values from SALES into BUDGET.SALES by entering
BUDGET.SALES = SALES
then by using a series of LIMITS we narrow the selection down the to the top node in the PRODUCT and STORE hierarchies and issue the command
BUDGET.SALES = SALES*1.1
indicating that we require a 10% increase in sales at the top level.
Next we specify the AGGMAP. Although an AGGMAP already exists another one needs to be defined as this will be used for ALLOCATION rather then AGGREGATION.
DEFINE ALLOCSALES AGGMAP <APP.TIME PRODUCT STORE>
and then enter the definition like this:
ALLOCMAP JOINLINES('RELATION R.PRODUCT OPERATOR PROPORTIONAL' 'RELATION R.STORE OPERATOR PROPORTIONAL' 'DEADLOCK SKIP' END')
Notice the difference here of using ALLOCMAP rather than AGGMAP as the command. This specifies that this AGGMAP is to be used for allocations. The RELATION command inside the brackets specifies the self-relation and also how the data is to be apportioned. In this example 'PROPORTIONAL' is used to pro-rata the data at the lower levels based upon the new top level number, i.e. 10% more in this case. The DEADLOCK SKIP clause is included in case ALLOCATE stumbles across any NA values that it cannot apportion. Rather than producing an error and stopping, an error is logged and the calculations continue.
Now we can allocate the numbers by issuing the command
ALLOCATE BUDGET.SALES USING ALLOCSALES
and then a final AGGREGATE to rollup the budget numbers from the bottom.
with the result that BUDGET.SALES are now pro-rata at all levels based upon the 10% increase at the top node.
APP.TIME: JAN03
-------------------------------------------BUDGET.SALES-------------------------------------------
----------------------------------------------STORE-----------------------------------------------
PRODUCT BRIGHTON WORTHING EASTBOURNE GUILDFORD REDHILL WEYBRIDGE SURREY SUSSEX TOTAL
-------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
TOTAL 3,573 4,204 3,994 3,895 3,178 3,350 10,423 11,771 22,194
FRUIT 2,462 2,286 1,513 2,273 1,511 1,395 5,179 6,261 11,440
VEGETABLES 1,111 1,918 2,481 1,622 1,667 1,955 5,244 5,510 10,754
APPLES 1,097 973 156 763 313 385 1,463 2,227 3,691
ORANGES 274 797 831 966 440 173 1,581 1,903 3,485
PEARS 1,091 516 526 544 758 837 2,141 2,135 4,277
TURNIP 653 335 796 460 137 278 876 1,786 2,663
ONION 313 971 1,047 972 438 1,028 2,439 2,333 4,773
CAULIFLOWER 145 612 638 190 1,092 649 1,933 1,396 3,330
APP.TIME: JAN03
----------------------------------------------SALES-----------------------------------------------
----------------------------------------------STORE-----------------------------------------------
PRODUCT BRIGHTON WORTHING EASTBOURNE GUILDFORD REDHILL WEYBRIDGE SURREY SUSSEX TOTAL
-------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
TOTAL 3,253 3,826 3,636 3,545 2,894 3,050 9,489 10,715 20,204
FRUIT 2,241 2,080 1,377 2,069 1,376 1,271 4,716 5,698 10,414
VEGETABLES 1,012 1,746 2,259 1,476 1,518 1,779 4,773 5,017 9,790
APPLES 998 885 142 695 285 351 1,331 2,025 3,356
ORANGES 250 725 756 879 401 158 1,438 1,731 3,169
PEARS 993 470 479 495 690 762 1,947 1,942 3,889
TURNIP 595 305 725 419 125 253 797 1,625 2,422
ONION 285 884 953 884 399 935 2,218 2,122 4,340
CAULIFLOWER 132 557 581 173 994 591 1,758 1,270 3,028
This example is of a proportional allocation but there are other examples available including even, maximum, minimum and status dependant ones. Examples are available in the OLAP DML help guide.
Summary of commands & functions
ALLOCATE - The ALLOCATE command allows you to allocate a source value to the specified cells of a target variable
ALLOCERRLOGFORMAT - This option determines the contents and the formatting of the error log that you specify with the ERRORLOG argument to the ALLOCATE command.
ALLOCERRHEADER - This option determines the column headings for the error log that you specify with the ERRORLOG argument to the ALLOCATE command.
ALLOCMAP - The ALLOCMAP command allows you to enter contents into a new aggregation map or replace the contents of an existing aggregation map. ALLOCMAP marks the aggregation map as an ALLOCMAP type of aggregation map, which you can use only with the ALLOCATE command. The ALLOCMAP command assigns contents to the most recently defined or considered aggregation map (see the DEFINE and CONSIDER commands), so you must have defined or considered an aggregation map before typing the ALLOCMAP command. You can use the ALLOCMAP command with text expression that contains the commands of the aggregation map that you want to use with the ALLOCATE command. You can also use the ALLOCMAP command without an argument in an input file where the DEFINE AGGMAP or CONSIDER AGGMAP command is followed by an ALLOCMAP command, the commands for the aggregation map, and END. You can enter contents into an aggregation map by using the OLAP Worksheet editor, which you start by entering the EDIT command and specifying an existing aggregation map that you have initialized with the ALLOCMAP command.
DEADLOCK - This command in an ALLOCMAP type aggregation map tells the ALLOCATE command what to do if 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 the RELATION command in the aggregation map or the cell has a basis value of NA. Use this command only in an ALLOCMAP type aggregation map. You create an ALLOCMAP type aggregation map by adding contents to the aggregation map with the ALLOCMAP command.
RELATION - This command that you enter in an ALLOCMAP type aggregation map allocation identifies a relation that specifies the path through a dimension hierarchy and the method of the allocation. You use an ALLOCMAP type the aggregation map with the ALLOCATE command. To allocate a source data down a hierarchy of a dimension, you must specify with a RELATION command the values of the hierarchy that identify the cells of the variable that are the targets of the allocation. If the target of the allocation is a multidimensional variable, then you must include a separate RELATION command for each dimension down which you want to allocate the source data. The order of the RELATION commands in an aggregation map determines the order of the allocation. The allocation proceeds down the dimension hierarchy in the first RELATION command, 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. If a dimension has more than one hierarchy, then you must supply a QDR argument to limit the relation to only one hierarchy.
In short what we have now is a powerful and useful function that to all intents and purposes is the opposite of the AGGREGATE command that will prove an important addition to the OLAP DML particularly for those applications connected with budgeting, forecasting and planning.
The example given above was performed on Oracle 9i OLAP version 9.2.0.1."
Simon Dunn
October 2003.