|Oracle9i OLAP Developer's Guide to the OLAP DML
Release 2 (9.2)
Part Number A95298-01
Aggregating Data, 3 of 12
There are several pre-aggregation steps that you should perform to achieve the best performance:
All aggregation maps require the identity of the parent relation for each dimension that is being aggregated. The parent relation is a self-relation that defines the hierarchy by identifying the parent of each dimension value.
If some of the data will be aggregated at runtime, then you may want to use a level relation to distinguish levels that will be omitted from the pre-calculation. The level relation identifies the level of the hierarchy for each dimension value. This relation is needed to identify which levels are precalculated and which ones are calculated at run-time. Skip-level aggregation is a recommended technique, described in "Balancing Precalculated and Runtime Aggregation", which uses level relations.
Example 12-1 describes the parent and level relations.
You may be able to use the
OBJ function to find out information about a workspace object. For example, the following command may display the name of the level dimension for the
This information may or may not be available through the
OBJ function does not yield results, then you must look at the contents of the variables in your analytic worksheet to see if these relations exist, and if not, then create them.
The following are the object definitions for three dimensions and two relations. These objects provide the information that the aggregation map needs to aggregate data dimensioned by
DEFINE GEOGRAPHY DIMENSION TEXT WIDTH 12 LD Geography dimension values DEFINE GEOGRAPHY.HIERARCHIES DIMENSION TEXT LD Hierarchy dimension for Geography DEFINE GEOGRAPHY.LEVELDIM DIMENSION TEXT LD List of hierarchy levels for GEOGRAPHY DEFINE GEOGRAPHY.PARENTREL RELATION GEOGRAPHY <GEOGRAPHY GEOGRAPHY.HIERARCHIES> LD Parent-child relation for Geography DEFINE GEOGRAPHY.LEVELREL RELATION GEOGRAPHY.LEVELDIM <GEOGRAPHY GEOGRAPHY.HIERARCHIES> LD Level of each member in each Geography hierarchy
geography dimension contains values at all levels of the hierarchy, such as
MEXICO, and so forth.
geography.hierarchies dimension identifies the names of the hierarchies. For example, geography might have two hierarchies,
geography.leveldim dimension identifies the names of the levels, such as
geography.parentrel relation is a self-relation. For each hierarchy and each dimension value, it identifies the parent value. For example, in the
STANDARD hierarchy, the parent of
JAPAN, and the parent of
geography.levelrel relation identifies the level for each dimension value in each hierarchy. For example, in the
KYOTO is at the
JAPAN is at the
COUNTRY level, and
ASIA is at the
You will achieve the best performance results with
AGGREGATE when all of the variable's composites use the
BTREE index algorithm. You can use the
DESCRIBE command to find out if a composite uses
HASH. If a composite uses
HASH, it will be displayed in the composite definition. If a composite uses
BTREE, no index algorithm will be displayed in the composite definition, because
BTREE is the default algorithm for composites.
The following object definition for the
market.prod composite shows that it uses a
To change to a BTREE index, use the
The composite definition looks like this with a