Aggregate Persistence Wizard

Aggregate tables store precomputed results, which are measures that have been aggregated (typically summed) over a set of dimensional attributes. Using aggregate tables is a very popular technique for speeding up query response times in decision support systems. This eliminates the need for run-time calculations and delivers faster results to users. The calculations are done ahead of time and the results are stored in tables. Aggregate tables should have fewer rows than the nonaggregate tables and, therefore, processing should be quicker.

The aggregate navigation capability of Oracle BI Server allows queries to use the information stored in aggregate tables automatically, without query authors or tools having to specify aggregate tables in the queries. Oracle BI Server allows users to concentrate on asking the right business questions, because the server decides which tables provide the fastest answers. For Oracle BI Server to have enough information to navigate to aggregate tables, certain metadata in the repository must be correctly configured.

The traditional process of creating aggregates for Oracle BI Server queries is manual. It can be tedious, requiring complicated data definition language (DDL) and data manipulation language (DML) scripts to be written for creating tables in the databases involved. Additionally, these aggregated tables need to be mapped to the repository metadata to be available for queries. This is a time-consuming and, possibly, error-prone process.

The Aggregate Persistence Wizard enables you to automate the creation of physical aggregate tables and their corresponding objects in the repository. The Aggregate Persistence Wizard creates an Oracle BI Enterprise Edition SQL script, which is executed by the BI Server. The script specifies each aggregate table to be created, the facts from the business model that should be included in it, and its dimensions and grain. When the BI Server runs the aggregate persistence SQL script, it generates DDL to create the required tables in the target database, internal instructions to generate the corresponding physical and aggregate navigation metadata, and data manipulation language (DML) to aggregate and load data from the base tables into the aggregate tables. The aggregate persistence script is intended to be run after each extraction, transformation, and loading (ETL) of the base tables, typically nightly. This can be done by an Oracle BI EE Job Manager job, or it can be run as a .bat or any other script called by a custom program.

You can find step by step instructions to create aggregate tables using Aggregate Persistence Wizard on Oracle's site(Oracle By Examples) - http://www.oracle.com/webfolder/technetwork/tutorials/obe/fmw/bi/biee/r1013/aggpersist/aggpersist.htm .