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 results might be incorrect if a query hits a slave 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 will hold the aggregates. Because the Oracle BI Repository enables federation, the aggregated target can be on the same database as the detailed source, or in a completely different database. This dedicated connection pool must be created before you run the Aggregate Persistence Wizard, so it can be 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" ;
The target schema used to store aggregates must be appropriately secured and should not allow public access. 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 might be persisted without the VPD filter, posing a security risk.