|Oracle9i OLAP Developer's Guide to the OLAP DML
Release 2 (9.2)
Part Number A95298-01
Aggregating Data, 4 of 12
An aggregation map is a workspace object. You first define the object and then add its contents, similar to creating a model or program. The contents of an aggregation map are commands that specify the data that should be aggregated for each dimension in the variable definition. It also identifies which data should be pre-calculated and which data should be calculated on the fly. Therefore, both the
AGGREGATE command and the
AGGREGATE function require an aggregation map
To create an aggregation map, you must:
You can define an aggregation map with the
DEFINE AGGMAP command. The syntax of the
DEFINE AGGMAP command is as follows:
name is the name of the aggregation map.
After you have defined an aggmap object, you must add contents to it. You can use the following ways to edit an aggregation map. See the examples that follow this list for details.
AGGMAPcommand to enter or replace the contents of the aggregation map.
EDIT AGGMAPcommand in OLAP Worksheet.
INFILEcommand to read it into your workspace.
The following program uses the
JOINLINES function with the
AGGMAP command to add
RELATION commands to an aggmap object.
DEFINE AGGTEST PROGRAM LD Create an aggregation map PROGRAM IF NOT EXISTS('test.agg') THEN DEFINE test.agg AGGMAP ELSE CONSIDER test.agg AGGMAP JOINLINES(- 'RELATION geography.parentrel' - 'RELATION product.parentrel' - 'RELATION channel.parentrel' - 'RELATION time.parentrel' - 'END') END
To use the
EDIT command in OLAP Worksheet to edit an aggmap object, take these steps:
myaggmapis the name of an existing aggmap object.
AGGMAP edit window will appear.
You can create a text file that contains the contents of the aggregation map. You can use this text file to create or modify the aggregation map.
Suppose that you have defined an aggmap object named
gpct.aggmap. You can create a file with these contents:
CONSIDER gpct.aggmap AGGMAP RELATION geography.parentrel RELATION product.parentrel RELATION channel.parentrel RELATION time.parentrel END
If the file is named
aggmap.inf in the
userfiles directory alias, then you can use the following
INFILE command to execute these commands in your session:
An aggregation map contains the following commands:
AGGMAPcommand indicates the beginning of an aggregation map. Depending upon how you add contents to an aggmap object, you may not need to include this command explicitly.
RELATIONcommand identifies a parent relation or self-relation (which acts as a hierarchy) of a dimension, which will be used to aggregate data. It can also identify the type of aggregation and the selection of data to be aggregated. By default, all of the data is summed. All aggregation maps contain one or more
MODELcommand executes a predefined
MODELobject. Models can be used to aggregate data over non-hierarchical dimensions, which do not have a parent relation.
CACHEcommand describes how or if the
AGGREGATEfunction stores any data that is calculated on the fly. This decision controls how quickly all of a data of a variable will reflect run-time changes that users make to the variable data.
AGGINDEXcommand describes whether or not Oracle OLAP should create indexes (composite tuples) that are needed by the
MODELcommand and by commands that use the
ACROSSphrase. This is an issue only when the variable has a composite dimension.
ENDcommand indicates the end of an aggregation map. Depending upon how you add contents to an aggmap object, you may not need to include this command explicitly.
The following is a simple aggregation map in which the data across all dimensions is precalculated using the
SUM operator. Note that the body of the aggregation map begins with an
AGGMAP command and ends with an
END command. The
RELATION commands are listed in the order the dimensions appear in the aggmap object definition.
DEFINE GPCT.AGGMAP AGGMAP LD Aggregation map for sales, units, quota, costs AGGMAP RELATION geography.parentrel RELATION product.parentrel RELATION geography.parentrel RELATION time.parentrel END
After you have created the aggregation map, you should compile and save it. This step is important for aggregation performed at run-time using the
AGGREGATE function. Unless the compiled version of the aggregation map has been saved, the aggregation map will be recompiled by each session that uses it.
If you use the
FUNCDATA argument to the
AGGREGATE command, then the aggregation map is automatically compiled. For example, these commands will precalculate aggregate data and save a compiled copy of the aggregation map for runtime aggregation.
Alternatively, you can compile the aggregation map explicitly with the
COMPILE command. Explicitly compiling an aggregation map is also useful for finding syntax errors in the aggregation map before attempting to use it to generate data.
The following commands create and save the compiled version of the
sales.agg aggregation map.
If some of the data will be calculated on the fly, then you must compile and save the aggregation map after executing the
Compiling an aggregation map can take a significant amount of time. If you fail to compile the aggregation map, the
AGGREGATE function will automatically compile it in order to get the information that is needed to perform calculation on the fly. If this happens, query performance will suffer. Every time a user queries the workspace for the first time, the
AGGREGATE function must compile the aggregation map before it can calculate the data. If 100 users query the same workspace, the aggregation map will be compiled 100 times. If you precompile the aggregation map and save it in the analytic workspace, then it is a task that is done once as part of the build process. If you leave the compilation to be done as a result of user queries, then it is a task that will be repeated for every user.
You can use one
AGGREGATE command to aggregate data for more than one variable, as long as the following conditions are true:
Suppose your workspace contains the following named composite and variable definitions:
DEFINE PROD.GEOG.CHAN COMPOSITE <PRODUCT GEOGRAPHY CHANNEL> DEFINE SALES DECIMAL <TIME PROD.GEOG.CHAN <PRODUCT GEOGRAPHY CHANNEL>> DEFINE UNITS INTEGER <TIME PROD.GEOG.CHAN <PRODUCT GEOGRAPHY CHANNEL>> DEFINE PROJECTED_SALES DECIMAL <TIME PROD.GEOG.CHAN <PRODUCT GEOGRAPHY CHANNEL>>
Because these variables have identical dimensionality, you can use one
AGGREGATE command to aggregate the data for all three variables.
Suppose you have defined an aggregation map named
sales.agg. You would use the following command to aggregate data for all three variables:
Suppose your workspace contains the following definitions for a named composite and three variables:
DEFINE PROD.GEOG.CHAN COMPOSITE <PRODUCT, GEOGRAPHY, CHANNEL> DEFINE SALES DECIMAL <TIME PROD.GEOG.CHAN <PRODUCT, GEOGRAPHY, CHANNEL>> DEFINE UNITS INTEGER <TIME SPARSE <PRODUCT, GEOGRAPHY, CHANNEL>> DEFINE PROJECTED_SALES DECIMAL <TIME SPARSE <PRODUCT, GEOGRAPHY>>
The following comparisons explain how the dimensionality is different for each variable:
salesvariable uses a named composite,
prod.geog.chan, whose base dimensions are
unitsvariable uses an unnamed composite, whose base dimensions are
channel. Even though the unnamed composite has the same dimensions in the same order as the named composite, Oracle OLAP considers the named composite and the unnamed composite to be two different workspace objects. Therefore,
unitsdo not have the same dimensionality.
project_salesvariable also has an unnamed composite, whose base dimensions are
geography. However, it is not identical to the unnamed composite that the
unitsvariable uses, because it does not include the
Because the dimensionality for each variable is different, you will have to define a different aggregation map to aggregate data for each variable. Therefore, you will have to use a different
AGGREGATE command for each variable.