Oracle^® Business Intelligence Server Administration Guide > Query Caching in the Oracle BI Server >

Creating Aggregates for Oracle BI Server Queries

Aggregate tables store precomputed results that are aggregated measures (typically summed) over a set of dimensional attributes. Using aggregate tables is a typical technique used to improve query response times in decision support systems.

If you write SQL queries or use a tool that only understands what physical tables exist and not their meaning, using aggregate tables becomes more complex as the number of aggregate tables increases. The aggregate navigation capability of the Oracle BI Server allows queries to use the information stored in aggregate tables automatically. The Oracle BI Server allows you to concentrate on asking the right business question, and then the server decides which tables provide the fastest answers.

The traditional process of creating aggregates for Oracle BI Server queries is manual. It requires writing complicated DDL and DML to create tables in the databases involved. Additionally, these tables need to be mapped into the repository metadata to be available for queries. This is a time consuming, and a potentially error-prone process. The Aggregate Persistence module allows the Oracle BI Administrator to automate the creation of aggregate tables and their mappings into the metadata.

Aggregate creation will run against the master server in a cluster. It will take some time for the metadata changes to propagate to the slaves. The cluster refresh time is a user-controlled option and results may be incorrect if a query hits a slave server before it is refreshed. It is the Oracle BI Administrator's responsibility to set an appropriate cluster refresh interval.

The NQSConfig.INI file contains the following optional parameter in the GENERAL section:

AGGREGATE_PREFIX = "user specified short prefix for dimension aggregates" ;

This parameter has a maximum of 8 characters and is specified if you want to add a prefix to automatically generated dimension (level) aggregates. If not specified, the default prefix SA_ will be used.

NOTE:  Only the Oracle BI Administrators group is allowed to manage aggregates.

This section contains the following topics:

• Identifying Query Candidates for Aggregation
• About Writing the Create Aggregates Specification
• Generating the SQL Script File
• About Setting the Logging Level
• Executing the SQL Script File to Create and Delete Aggregates
• Post Creation Activities