Specifying Tuning and Performance Options in Cube Aggregations

Overview

When you build cubes, you can set various options that improve and optimize cube creation and query performance. These options can be set for all aggregations in a cube or for a specific aggregation. Moreover, these options can be set by using the PROC OLAP options or in SAS OLAP Cube Studio. These options are stored with the cube metadata in the SAS metadata.

Setting Options on the Aggregation Tuning Dialog Box

Overview

In the Cube Designer – Aggregation Tuning dialog box, the Options button is provided for access to tuning options. Select the Options button to open the Performance Options dialog box. There are two tabs for setting tuning options, the Default tab and the Aggregation tab.

Default Tab

The default performance options are applied to all aggregations for the cube. These performance options include the following:

amount of memory (in megabytes) that is available for aggregation creation
maximum number of threads that are used to create an aggregation index
number of aggregations to create in parallel
partition size (in megabytes) of aggregation table partitions
number of observations (in kilobytes) to include in the index component file segment
location of index component files
location of partitions in which to place aggregation table data
aggregation tables that are stored in compressed format.

For specific information about these functions, see the Performance Options - Default tab Help page in SAS OLAP Cube Studio Help.

Aggregation Tab

The aggregation-specific performance options are applied to an individual aggregation for the cube and override the global option settings for that aggregation. You can define and modify performance options for an aggregation or delete options for an aggregation. The aggregation-specific performance options include the following:

partition size (in megabytes) of aggregation table partitions
number of observations (in kilobytes) to include in the index component file
location of index component files
location of partitions in which to place aggregation table data
aggregation tables stored in compressed format
aggregations created with indexes

For specific information about these functions, see the Performance Options - Define tab Help page in SAS OLAP Cube Studio Help.

Setting Options with PROC OLAP

You can set options for all aggregations in a cube or for a specific aggregation. To set options for all aggregations, set the options in the PROC OLAP statement. To set options for a single aggregation, set the options in the PROC OLAP AGGREGATION statement. The options include the following:

ASYNCINDEXLIMIT= n

specifies a limit on the number of indices that will be created in parallel during the cube build process.

COMPRESS | NOCOMPRESS

specifies whether to store the aggregation tables in a compressed format on disk.

CONCURRENT=n

specifies the maximum number of aggregations to create in parallel.

DATAPATH=('pathname1' ...'pathnameN')

specifies the location of one or more partitions in which to place aggregation table data.

INDEXPATH=('pathname1' ...'pathnameN')

specifies the locations of the index component files that correspond to each aggregation table partition as specified by the DATAPATH= option.

INDEXSORTSIZE=n

specifies the amount of memory in megabytes that is available when aggregations are created. The default is the system's available memory.

MAXTHREADS=n

specifies the maximum number of threads that are used to asynchronously create the aggregation indexes.

INDEX | NOINDEX

specifies whether to create the aggregations with indexes.

PARTSIZE=partition-size

specifies the partition size in megabytes of the aggregation table partitions and their corresponding index components.

SEGSIZE=rows-per-segment

specifies the number of observations (table rows) in kilobytes to include in the index component file segment.

WORKPATH=pathname

specifies one or more locations for temporary work files.

Note: ASYNCINDEXLIMIT=, CONCURRENT=, INDEXSORTSIZE=, and MAXTHREADS= are available only in the PROC OLAP statement.

For more information about these options, see The OLAP Procedure.