14 Create and Persist Aggregates for Oracle BI Server Queries

Learn how to set up and use aggregate persistence in Oracle Analytics Server.

Most data warehouse practitioners create aggregated data tables to improve the performance of highly summarized queries. The aggregate tables store precomputed results that are combined measures, usually summed, over a set of dimensional attributes. Use aggregate tables 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, then using aggregate tables becomes more complex as the number of aggregate tables increases. The Oracle BI Server’s aggregate navigation capability enabled queries to use the information stored in aggregate tables automatically. The Oracle BI Server lets you concentrate on asking the right business question, and then the server decides which tables provide the fastest answers.

Oracle Analytics Server takes advantage of the aggregates in source databases. See Manage Logical Table Sources (Mappings). The aggregate persistence automates the creation and loading of the aggregate tables and their corresponding metadata mappings to minimize the time required to create and maintain the data aggregation, as well as load database scripts and the corresponding metadata mappings.

This chapter contains the following topics:

• About Aggregate Persistence

• Aggregate Persistence Improvements

• About Aggregate Persistence Errors

• Identify Query Candidates for Aggregation

• Use Oracle BI Summary Advisor to Identify Query Candidates for Aggregation

• Use the Aggregate Persistence Wizard to Generate the Aggregate Specification

• Use Model Check Manager to Check for Modeling Problems

• Write the Create Aggregates Specification

• Run the Aggregate Specification Script

• Life Cycle Use Cases for Aggregate Persistence

• Use Double Buffering to Refresh Highly Available Aggregates

• Create Aggregates on TimesTen Sources