Oracle^® Business Intelligence Server Administration Guide > Query Caching in the Oracle BI Server > Creating Aggregates for Oracle BI Server Queries >

Identifying Query Candidates for Aggregation

When creating aggregates you need to identify which queries would benefit substantially from aggregated data. You will achieve the best results by aggregating to the highest level possible. To identify slow running queries, perform the following tasks:

• Enable usage tracking in the Oracle BI Server.

  Usage tracking statistics can be used in a variety of ways, for example, database optimization, aggregation strategies, and billing users or departments based on the resources they consume. The Oracle BI Server tracks usage at the detailed query level. When you enable usage tracking, statistics for every query are written to a usage tracking log file or inserted into a database table.

  NOTE:  It is strongly recommended that you use direct insertion into a database. For instructions, refer to Administering Usage Tracking.

• Analyze the query runtimes and identify the slowest running queries as candidates for aggregation.

  The run time for creating aggregates is dependent on the type of aggregates selected by the user. Creating aggregates from large fact tables will be slower than from smaller tables. The Oracle BI Administrator needs to carefully select the aggregates to be created.