Aggregation awareness

[platinne]

Hello,

In my data mart, let's say I have a big fact table and a small aggregated table.
I would like my model in FM to be designed so that for a query with "compatible" data items, the generated SQL uses the aggregated table and for a query with at least one "incompatible" data item, the generated SQL automatically uses the fact table.

Do you have any idea on how to do this in FM? Macros, DMR, ... built-in functionality?

Many thanks in advance for all your replies.

Kind regards,

Patrice

[blom0344]

Hello,

In my data mart, let's say I have a big fact table and a small aggregated table.
I would like my model in FM to be designed so that for a query with "compatible" data items, the generated SQL uses the aggregated table and for a query with at least one "incompatible" data item, the generated SQL automatically uses the fact table.

Do you have any idea on how to do this in FM? Macros, DMR, ... built-in functionality?

Many thanks in advance for all your replies.

Kind regards,

Patrice

As oppossed to BO Cognos has not been able to incorporate Aggregate awareness into the product.
Depending on the database at hand you may be able to fix this by using RDBMS technology. Many of the big vendors offer some sort of aggregation tables (AST) that are synched with larger fact tables. When a query is going to be processed the optimizer may than use a summary table instead of the detail-table..

[platinne]

Thank you for your reply, blom0344.
I heard and feared about that indeed...
When you say "big vendors", is there any solution provided with Sqlserver 2000 or 2005?

Patrice

[blom0344]

From sql server 2000 onwards there is a concept called 'indexed views'.
However, there are many restrictions here. You cannot reference a view in the view, use outer joins , use non-deterministic functions , use the top predicate and so on.
The database optimizer is not perfect as well, so you cannot always expect it to chose the indexed view over the base tables.

[arickard]

Ralph Kimball in his influential book "The Datawarehouse Toolkit" referred to this as the aggregate navigator. Just google the term and you'll find plently of information on the theory behind it. Its a shame that the business intelligence vendors don't include this kind of query optimization in their products. I suppose they think its the type of problem that the database vendors must be responsible for handling.

---
Andrew
http://metatheory.wordpress.com

[blom0344]

Its a shame that the business intelligence vendors don't include this kind of query optimization in their products. I suppose they think its the type of problem that the database vendors must be responsible for handling.

That is not entirely true. Business Objects has had aggregate awareness as a universe option for as long as I can remember. In recent years they have also added index-awareness. Both of these functionalities are used sparsely. You need some cooperation between DBA and designer to reap the benefit.
I think this is one of the areas where BO 'outguns' Cognos