COGNOiSe.com - The IBM Cognos Community

IBM Cognos 8 Platform => COGNOS 8 => Framework Manager => Topic started by: ComplexQry on 15 Jan 2008 01:35:10 PM

Title: Auto Aggregation Issues
Post by: ComplexQry on 15 Jan 2008 01:35:10 PM
Problem:  Facts that reside in a Dimension are not aggregating correctly in reports.

I have a fact table that has a fact that is used in a report, it auto aggregates perfectly.  I have a dimension joined to that fact (1 to 1 relationship) with 4 facts in it.  Those facts do not auto aggregrate correctly.  They seem to total  for all the data in the report as if the SQL was sum(hours) for report.  If I change the properties for the 4 facts from the dim to unsupported for the auto aggregation and then use the aggregate function in report studio, the numbers then come back perfectly.  But I don't like that solution.  It should work the other way.

Any thoughts?  Let me know if I was not clear enough.
Title: Re: Auto Aggregation Issues
Post by: Suraj on 23 Jan 2008 01:30:33 PM
Not any help here, but if the performance is not bad, anything working is a solution.
Title: Re: Auto Aggregation Issues
Post by: blom0344 on 24 Jan 2008 10:13:58 AM
If you have a true 1:1 relation then remodel the objects into one model query subject.
Fact -Dimension should be N:1 or N:0
Title: Re: Auto Aggregation Issues
Post by: ComplexQry on 07 Feb 2008 08:20:49 AM
I actually did merge the two together and the values came back as expected.  But it just doesn't make sense to me that it wouldn't auto-aggregate correctly with the normal join.  I checked the SQL in both cases, fields from my fact table would have sum(measure) the fields from my dimension wouldn't have a sum before I merged the two together.  After I merged them, you saw that both fields were being SUM'd.