When creating aggregates, you must identify which queries would benefit substantially from aggregated data.
You can 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, such as 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.
It is strongly recommended that you use the direct insertion into a database method for usage tracking. See Managing Usage Tracking for in System Administrator's Guide for Oracle Business Intelligence Enterprise Edition information about usage tracking.
Analyze the query run times 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 is slower than from smaller tables. You should carefully select the aggregates to create.