Aggregated Facts

[mrcool]

Hi All,

If we have aggregated facts like at monthly and yearly level..Is it possible to switch between the facts during the run time drill up and drill down in analysis studio?Do we have any concept like Aggregate awareness in cognos?

[MFGF]

Hi,

Currently, IBM Cognos seems to view this as "SEP" (Someone Else's Problem) :)  In other words, they see it as a requirement to be handled by the database query optimizer rather than by the Cognos query generation engine.

Their recommendation is that your database has aggregate-awareness and a query optimizer which will interrogate the appropriate table or view, depending on the context of the query.

MF.

[blom0344]

Hi All,

If we have aggregated facts like at monthly and yearly level..Is it possible to switch between the facts during the run time drill up and drill down in analysis studio?Do we have any concept like Aggregate awareness in cognos?

From a performance perspective, do you expect much benefit switching between year and month level? This is just a factor 12. May not have much impact. I would expect true benefit if you would go from finest grain to the year level..

[mrcool]

Thank you All,

Hi MFGF,

But how can database identify which table to hit unless we specify in FM or at report level. If the user drill up from month to year it should Hit Year aggregate fact and vice versa. Considering we have time dimension and single fact at lowest granular level I can roll up the data.
Just want to know what if we have aggregate facts already in database.
Using parameter maps is it possible?

Blom0344: I agree with what you said. But I want to know whether it is possible from analysis studio or not.

Once Again thank you so much. I will be waiting for your reply.

Thanks,
Mrcool

[MFGF]

But how can database identify which table to hit unless we specify in FM or at report level. If the user drill up from month to year it should Hit Year aggregate fact and vice versa. Considering we have time dimension and single fact at lowest granular level I can roll up the data.
Just want to know what if we have aggregate facts already in database.
Using parameter maps is it possible?

The key to this is that the database query optimizer is aggregate-aware.  In theory, some databases are intelligent enough to recognize when using a materialized view would be more appropriate than using the underlying detail table, and re-write the query to take this into account.

MF.

[blom0344]

My 'educated'  guess is that Cognos had to choose somewhere along the way. The competition uses agregate awareness since a long time, but in BO's case a universe is build against one type of RDBMS (at least when you want to use any functions at all, that is) Cognos offers it's own SQL flavor and allows for a model to communicate with various RDBMS (as long as no native functions are used) This makes it more flexible, but it does need to translate the Cognos dialect to the proper syntax before sending it to the database.
Materialized views, indexed views, automatic summary tables. Each vendor has it's own solution and sometimes you need an enterprise version to implement them