How to Generate Precalculated Data

Typically, you will general precalculated aggregates in a batch window as part of maintaining the data in your database. If you wish, you can use Job Manager to schedule batch jobs in Oracle Enterprise Manager, as described in the Oracle9i OLAP User's Guide.

The AGGREGATE command aggregates the data for one or more variables according to the specifications provided in the aggregation map. The basic syntax of the AGGREGATE command is:

AGGREGATE variables USING aggmap

Where:

variables is the name of one or more variables.

aggmap is the name of the aggregation map.

Example 12-13 Precalculating Data in a Batch Job

Your batch job should include commands like the following:

ALLSTAT
POUTFILEUNIT=FILEOPEN('userfiles/progress.txt' WRITE)
AGGREGATE sales units USING gpct.aggmap
UPDATE
COMMIT
FILECLOSE POUTFILEUNIT

Effects of Dimension Status

The RELATION command only aggregates those source data values (that is, those values that are loaded into the analytic workspace and used as the basis of aggregation) that are in status. The parent values are calculated regardless of whether they are in status or not. For example, if only JAN01, FEB01, and MAR01 are in status for the time dimension, then Q1.01 will be calculated (but no other quarters), and 2001 will be calculated (but no other years) using only Q1.01 as input since the other quarters are NA.

This can be useful when you want to aggregate just the new data in your analytic workspace. However, you must exercise some care, as described in "Performing Partial Aggregations".

Monitoring Progress

You can monitor the progress of an aggregation by setting the POUTFILEUNIT option. You can use the OUTFILEUNIT option or the OUTFILE function to set the value of POUTFILEUNIT.

This command sets POUTFILEUNIT to the file unit number of the current outfile, which is usually the screen:

POUTFILEUNIT=OUTFILEUNIT

This command opens a file named progress.txt in the userfiles directory alias, and sets POUTFILEUNIT to the file unit number of progress.txt:

POUTFILEUNIT=FILEOPEN('userfiles/progress.txt' WRITE)

When the aggregation is complete, you must close the file with a FILECLOSE command.