Aggregate-aware query routing for relational Data Modules

Please add aggregate awareness to IBM Cognos Analytics Data Modules for relational sources and if possible for Framework Manager models.

Modelers should be able to register physical aggregate tables or views and map them to a logical detail fact object. The mapping should include grain, dimensions, measures, aggregation rules, supported filters, priority, and data freshness.

At runtime, Cognos should transparently select the most suitable aggregate table for dashboard and report queries when it can answer the request with full semantic correctness. If no suitable aggregate exists, Cognos must automatically and safely fall back to the detail source.

Report authors and end users should work only with the logical Data Module. They should not need to select, understand, or even be aware of technical aggregate tables.

This is particularly important for Trino-based architectures. We can create pre-aggregated tables in Trino for common dashboard workloads, but Cognos currently cannot centrally and transparently use those aggregates based on query grain and required measures. As a result, dashboard queries repeatedly access large detail tables, increasing load on the shared Trino cluster and causing unacceptable response times.

Our target is dashboard response times below 10 seconds, ideally below 5 seconds.

Please include diagnostic capabilities that show:

• whether an aggregate table was selected,

• which table was selected,

• why it was selected,

• why a fallback to the detail source occurred,

• the generated SQL or relevant query-plan details.

The feature must preserve row-level security, tenant isolation, data permissions, filters, freshness requirements, and semantic correctness at all times.