Since Analysis Studio does not allow for calculating cumulative figures we fetch them from a database table. To prevent cumulative figures from normal aggregate behavior an aggregate rule is set against the time dimension. This seems to work okay, but for the way Cognos seems to generate the underlying SQL.
The SQL generated does not seems to make any sense with a very complex where clause. Query performance drops by at least 100-fold.
I can understand that the server needs to execute the rule and analysis will a lot slower, but for the first time we witness that the database becomes the bottleneck. Analysis is at least 10 times slower compared with a Powercube.
Any suggestions?
Well, here is the work-around:
For quarter and year level we defined seperate measures:
CASE WHEN extract(month,xxx.yyy.somedate) in (3,6,9,12) then xxx.yyy.cumvalue ELSE 0 END
CASE WHEN extract(month,xxx.yyy.somedate) =12 then xxx.yyy.cumvalue ELSE 0 END
The nitty-gritty is that swapping measures becomes a necessity.
Every analysis should not include (sub)totals as these are rubbish
The benefit is clean and fast SQL , aggregation within the database is enabled yielding much smaller datasets.
Once again a kewpie doll for a nicer solution.
Cognos 10 should have a better query engine when it comes to adding aggregation rules. Anyone there yet?
I have Cognos 10 but the problem is I dont have much data so the test would not be real.
Have you tried setting determinants ? They are supposed to improve performance for DMR. I presume it will re-write the SQL in a more efficient way !
Yes, determinants are set for the dimension tables (as they have a primary key) The generated SQL has been very clean and fast in all instances including multifact analysis. Things really went overboard when aggregation rules where introduced in the model.
Our testdatasets are not that large (about 300.000 for the fact), but the performance drop was huge..
The following items were found in Cognos tech :
PK89125
PK89801
PM03886
PM07501
Some are not accesible, but the general picture is that issues exists with aggregation rules :(