22 IBM Cognos Dynamic Cubes
Figure 2-18 shows query decomposition with virtual cubes.
Figure 2-18 Query decomposition with virtual cubes
2.1.7 Aggregate Advisor
A key feature of Cognos Dynamic Cubes is its ability to take advantage of both in-database
and in-memory pre-computed summaries. These pre-computed summaries can improve the
performance of queries by orders of magnitude, providing the type of performance required
for interactive reporting and analysis.
If you have pre-existing summary tables in your data warehouse, you can model them as
aggregate cubes in the Cognos Cube Designer. When a cube is published and the cube is
restarted, it will automatically route SQL queries to the summary tables when possible. For
distributive measures (those whose aggregation rule is SUM, COUNT, MAX, or MIN),
summary tables can be employed to compute summary values at higher levels of aggregation
than that at which an aggregate cube is defined.
As useful as this capability is, one of the most difficult tasks when of pre-computed
summaries is trying to determine what it is that should be pre-aggregated, especially in a
large, multi-user environment that might involve hundreds or thousands of reports and
analyses. The Cognos Dynamic Cubes Aggregate Advisor, available as part of the Dynamic
Query Analyzer, performs this task.
The Aggregate Advisor can be used to suggest database aggregate tables, in-memory
aggregate cubes, or both. The Aggregate Advisor makes use of a cube’s model and statistics
it gathers from the underlying data warehouse to determine which summary tables to
suggest. However, it can also make use of workload log files that are generated from the
execution of reports and analyses to make more accurate suggestions of what will optimize
the performance of an application workload.
Virtual
Cube
[Sales] by Dates ([2011], [Jan 2012], [Feb 2012])
2012
Sales
Cube
Historic
Sales
Cube
[Sales] by [Jan 2012], [Feb 2012]
[Sales] by [2012]