About Aggregate Persistence in Oracle Business Intelligence

Use the Aggregate Persistence feature to create aggregates for Oracle BI Server queries.

The Aggregate Persistence Wizard lets you automate the creation of the aggregate specification script. When you run this script against a live Oracle BI Server, aggregate tables are created by the aggregate persistence engine and are mapped into the metadata for navigation. When aggregates are persisted, indexes and statistics are created on relational tables for greater performance.

The Aggregate Persistence Wizard creates a SQL script that you can run on a scheduled basis against the Oracle BI Server. In the Aggregate Persistence Wizard, you specify the measures, dimensionality, and other parameters of each star or cube based on your performance design. The script should run after each load of the base-level tables, so that the aggregates are always synchronized with the detail-level data when the load window completes and users begin to run queries.

Aggregate creation runs against the master server in a cluster. It takes some time for the metadata changes to propagate to the slaves. The cluster refresh time is a user controlled option and you could get incorrect results if a query hits a dependent child server before it is refreshed. It is the administrator's responsibility to set an appropriate cluster refresh interval.

Aggregate persistence requires a dedicated connection pool to create tables or cubes in the target database that holds the aggregates. Because the Oracle BI Repository enables federation, the aggregated target can use the same database as the detailed source, or in a completely different database. You must create the dedicated connection pool before you run the Aggregate Persistence Wizard, so the correct connection pool is selected during the appropriate step of the wizard.

The default prefix SA_ is automatically added to dimension (level) aggregates. You can change this default prefix by updating the AGGREGATE_PREFIX parameter in the AGGREGATE_PERSISTENCE section of the NQSConfig.INI file:

AGGREGATE_PREFIX = "prefix_name" ;

You must appropriately secured and restrict access to the target schema used to store aggregates. The schema should have privileges to connect, create, and drop tables and indexes. By default, only users who have administrator privileges can manage aggregates.

Do not use aggregate persistence against tables with active Virtual Private Database (VPD) security filters. There is a possibility that the aggregate information could persist without the VPD filter, posing a security risk.