Skip Headers

Oracle® OLAP DML Reference
10g Release 1 (10.1)

Part Number B10339-02
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page

Go to previous page
Go to next page
View PDF

4.3 Allocations

Allocating data involves creating lower-level data from summary data. Allocating data using the OLAP DML involves creating an ALLOCMAP type aggmap object that specifies how the data should be allocated, and executing that object using the ALLOCATE command to actually distribute the data from a source object to the cells of a target. The target is a variable that is dimensioned by one or more hierarchical dimensions. The source data is specified by dimension values at a higher level in a hierarchical dimension than the values that specify the target cells.

ALLOCATE uses an aggmap to specify the dimensions and the values of the hierarchies to use in the allocation, the method of operation to use for a dimension, and other aspects of the allocation.

Some of the allocation operations are based on existing data. The object containing that data is the basis object for the allocation. In those operations, ALLOCATE distributes the data from the source based on the values of the basis object.

ALLOCATE has operations that are the inverse of the operations of the AGGREGATE command. The allocation operation methods range from simple allocations, such as copying the source data to the cells of the target variable, to very complex allocations, such as a proportional distribution of data from a source that is a formula, with the amount distributed being based on another formula, with multiple variables as targets, and with an aggmap that specifies different methods of allocation for different dimensions.

The Oracle OLAP allocation system is very flexible and has many features, including the following:

4.3.1 Allocating Data

To allocate data using an aggmap object, use the following OLAP DML statements in the order indicated:

  1. Issue a DEFINE AGGMAP statement to define the aggmap object and an ALLOCMAP statement to indicate that the aggmap object is of type ALLOCMAP and that it contains an allocation specification. For example:

    DEFINE myaggmap AGGMAP
  2. Add a specification to the aggmap object that specifies the allocation that you want performed.

  3. Save the aggmap object using an UPDATE command followed by COMMIT.

  4. (Optional) Set the POUTFILEUNIT option so that you can monitor the progress of the allocation.

  5. (Optional) Redesign the allocation error log by setting the ALLOCERRLOGFORMAT and ALLOCERRLOGHEADER options to nondefault values.

  6. (Optional) Set the $ALLOCMAP on one or more variables to specify that the aggmap is the default allocation specification for the variables.

  7. (Recommended, but optional) Limit the variable to the target cells (that is, the cells into which you want to allocate data).

  8. Issue an ALLOCATE statement to allocate the data.

For brief descriptions of all of the OLAP DML statements that relate to allocation, see "Allocation Statements".

4.3.2 Handling NA Values

Sometimes you want to overwrite existing data when allocating values to a target variable and at other times you want to write allocated values to target cells that have an NA basis before the allocation. For example, when you create a new product in your product dimension, then no basis exists for the new product in your budget variable. You want to allocate advertising costs for the entire product line, including the new product.

You can handle NA values using formulas and hierarchical operators in a RELATION (for allocation) statement in the following ways:

  • Handling NA data with formulas—One way to handle the NA values is to construct a basis that only describes the desired target cells. This is the preferred method. You can refine your choice of basis values by deriving the basis from a formula. The following statements define a formula that equates the values of the new product to twice the value of an existing product. You could use such a formula as the basis for allocating advertising costs to the new product.

    DEFINE formula_basis FORMULA DECIMAL <product>
    EQ IF product EQ 'NEWPRODUCT' -
       THEN 2 * product.budget(product 'EXISTINGPRODUCT') -
       ELSE product.budget
  • Handling NA data with hierarchical operators—To allocate data to target cells that currently have NA values, use a hierarchical operator in a RELATION (for allocation) statement in the allocation specification. The hierarchical operators use the hierarchy of the dimension rather than existing data as the allocation basis. A danger in using hierarchical operators is the possibility of densely populating your detail level data, which can result in a much larger analytic workspace and require much more time to aggregate the data.

    To continue the example of allocating the advertising cost for the new product, you could use the hierarchical last operator HLAST to specify allocating the cost to the new (and presumably the last) product in the product dimension hierarchy.