Creating an Aggregation Map

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:

1. Define an aggmap object.
2. Add contents to the aggmap object.

How to Define an Aggmap Object

You can define an aggregation map with the DEFINE AGGMAP command. The syntax of the DEFINE AGGMAP command is as follows:

DEFINE name AGGMAP

Where:

name is the name of the aggregation map.

How to Add Contents to an Aggmap Object

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.

• Use the AGGMAP command to enter or replace the contents of the aggregation map.
• Use the EDIT AGGMAP command in OLAP Worksheet.
• Create a text file with the contents of the aggregation map, then use the INFILE command to read it into your workspace.

Example 12-2 Using the AGGMAP Command

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

Example 12-3 Using the EDIT AGGMAP command in OLAP Worksheet

To use the EDIT command in OLAP Worksheet to edit an aggmap object, take these steps:

1. Issue this DML command, where myaggmap is the name of an existing aggmap object.

   EDIT AGGMAP myaggmap
   
   

   The AGGMAP edit window will appear.

2. Enter the body of the aggregation map, or make whatever changes you wish to an existing aggregation map.
3. To save your changes, choose Save from the File menu.
4. To close the edit window, choose Quit from the File menu.

Example 12-4 Using the INFILE Command to Execute Commands in a Text File

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:

INFILE 'userfiles/aggmap.inf'

Contents of an Aggregation Map

An aggregation map contains the following commands:

• AGGMAP command 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.
• RELATION command 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 RELATION commands.
• MODEL command executes a predefined MODEL object. Models can be used to aggregate data over non-hierarchical dimensions, which do not have a parent relation.
• CACHE command describes how or if the AGGREGATE function 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.
• AGGINDEX command describes whether or not Oracle OLAP should create indexes (composite tuples) that are needed by the MODEL command and by commands that use the ACROSS phrase. This is an issue only when the variable has a composite dimension.
• END command 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.

  Note: Both the CACHE and AGGINDEX commands have default settings. If these default settings are appropriate for your application, then you can omit these commands from your aggregation map. Be sure to read the topics in the Oracle9i OLAP DML Reference help for each of these commands to determine whether or not you need to use them.

Example 12-5 Simple Aggregation Map

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

How to Compile an Aggregation Map

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.

AGGREGATE sales USING gpct.aggmap FUNCDATA
UPDATE
COMMIT

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.

COMPILE gpct.aggmap
UPDATE
COMMIT

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.

Aggregating Multiple Variables with a Single Command

You can use one AGGREGATE command to aggregate data for more than one variable, as long as the following conditions are true:

• All of the variables have identical dimensionality, which means that every variable definition has the same dimensions in the same order.
• You can use the same aggregation map for all of the variables. This means you will be pre-calculating the same levels of data for every variable. Therefore, you must be sure that your users tend to query the same levels of data for each variable.

Example 12-6 Variables That Can Be Aggregated with One Command

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:

AGGREGATE sales units projected_sales USING sales.agg

Example 12-7 Variables That Cannot Be Aggregated with One Command

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:

• The sales variable uses a named composite, prod.geog.chan, whose base dimensions are product, geography, and channel.
• The units variable uses an unnamed composite, whose base dimensions are product, geography, and 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, sales and units do not have the same dimensionality.
• The project_sales variable also has an unnamed composite, whose base dimensions are product and geography. However, it is not identical to the unnamed composite that the units variable uses, because it does not include the channel dimension.

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.