ROLAP and Last - reports are very slow

[krzpik]

Dear Community Members,

I am struggling right now with a following situation in my project (we are working on architecture project right now), where we want to provide Cognos reporting based on a RDB table in Oracle. What seems for me to be a problem is, that we have some measures that have different aggregations:
- "Sum" - regular and most common way of presenting data on aggregations, where Q1 = Jan+Feb+Mar and where Feb_YTD = Jan+Feb,
- "Last period" - different way of calculating aggregations, where Q1 = Mar and where Feb_YTD = Feb.

Two problems I am facing are:
1. Although reporting of "Last period" type of measures is possible in Cognos (which queries a table in relational database) it seems it is VERY inefficient (works for an acceptable amount of time if there are not more than 10 thousand records in the source table). I expect to have 5-10 million records monthly.
2. It is impossible to build ONE query in Cognos that would return a set of measures on aggregations that aggregate differently (there is at least one "Sum" and one "Last period" type among reported measures).

Can you confirm my hypos here (so at least I could know there are some limits I will not be able to defeat) or maybe there is something I do not know?

Looking forward to hearing from you,