Preliminary Steps Prior to Aggregation

There are several pre-aggregation steps that you should perform to achieve the best performance:

• Get the names of self-relations or the names of parent and hierarchy relations defined within the DML.
• Check all composite dimensions to make sure that they have BTREE indexes.

Identifying the Parent and Level Relations

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 geography dimension:

REPORT OBJ(PROPERTY 'leveldim' 'geography')

If 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.

Example 12-1 Identifying the Parent and Level Relations

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 geography.

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

The geography dimension contains values at all levels of the hierarchy, such as WORLD, AMERICAS, CANADA, TORONTO, MONTREAL, NEWYORK, CHICAGO, SEATTLE, MEXICO, and so forth.

The geography.hierarchies dimension identifies the names of the hierarchies. For example, geography might have two hierarchies, STANDARD and CONSOLIDATED.

The geography.leveldim dimension identifies the names of the levels, such as CITY, STATE, COUNTRY, REGION, WORLD.

The 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 KYOTO is JAPAN, and the parent of JAPAN is ASIA.

The geography.levelrel relation identifies the level for each dimension value in each hierarchy. For example, in the STANDARD hierarchy, KYOTO is at the CITY level, JAPAN is at the COUNTRY level, and ASIA is at the REGION level.

Verifying That All Composites Use BTREE Indexes

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 BTREE or 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 HASH index:

DEFINE MARKET.PROD COMPOSITE <MARKET PRODUCT> HASH

To change to a BTREE index, use the CHGDFN command:

CHGDFN market.prod BTREE

The composite definition looks like this with a BTREE index:

DEFINE MARKET.PROD COMPOSITE <MARKET PRODUCT>