I have numerous finance reports that use calculated data items to determine groupings, which all work perfectly in Cognos 10.1. In my Cognos 10.2 upgrade testing I see that these calculated groupings no longer work. Instead, totals are determined by "sum over partition" in the generated SQL code. This gives me grand totals for fiscal year or fiscal period, but ignores the calculated grouping data items completely.
You can see in the following code that "Grouping3" is ignored in the total (this is Cognos 10.2 generated SQL). This same query works as expected in 10.1, without the "sum over partition" function.
SELECT "T0"."C0" "Accounting_Year__As_Nbr__",
SUM ("T0"."C1") OVER (PARTITION BY "T0"."C0", "T0"."C2", "T0"."C3")
"Transaction_Amount____",
"T0"."C4" "Grouping3",
"T0"."C2" "Accounting_Mth__As_Nbr__",
"T0"."C3" "Accounting_Mth__As_Abr__"
FROM ...
This is a show stopper for my Cognos 10.2 upgrade. Is there any way to prevent Cognos from using this "sum over partition" function? If I remove it from the SQL code I get the proper results again, but I don't see any way to stop Cognos from sticking it in the code.
Thanks for any insight or assitance!
Quote from: scott_a on 26 Aug 2014 10:34:56 AM
I have numerous finance reports that use calculated data items to determine groupings, which all work perfectly in Cognos 10.1. In my Cognos 10.2 upgrade testing I see that these calculated groupings no longer work. Instead, totals are determined by "sum over partition" in the generated SQL code. This gives me grand totals for fiscal year or fiscal period, but ignores the calculated grouping data items completely.
You can see in the following code that "Grouping3" is ignored in the total (this is Cognos 10.2 generated SQL). This same query works as expected in 10.1, without the "sum over partition" function.
SELECT "T0"."C0" "Accounting_Year__As_Nbr__",
SUM ("T0"."C1") OVER (PARTITION BY "T0"."C0", "T0"."C2", "T0"."C3")
"Transaction_Amount____",
"T0"."C4" "Grouping3",
"T0"."C2" "Accounting_Mth__As_Nbr__",
"T0"."C3" "Accounting_Mth__As_Abr__"
FROM ...
This is a show stopper for my Cognos 10.2 upgrade. Is there any way to prevent Cognos from using this "sum over partition" function? If I remove it from the SQL code I get the proper results again, but I don't see any way to stop Cognos from sticking it in the code.
Thanks for any insight or assitance!
Hmmmm. Strange that the generated SQL would change like that.
One obvious place to check initially is in the properties of the query in Report Studio - are they all set the same in 10.2 as in 10.1? Is the package the same or has it been recreated?
MF.
Hi MF,
The query properties are the same, and so is the FM package. All I have done up to this point is taken a copy of the 10.1 content store and configured my 10.2 environment to use it. 10.2 ran an upgrade on the content store as would be expected, but nothing else.
Scott
My guess is this is a result of a performance optimization by the Cognos developers to move some of the processing between local and database. But it's just a guess. You might be able to override it using one of the query property settings, but I would have difficulty reproducing your situation enough to figure out which one. Taking a quick look through them, maybe flip "Processing" (Database only / Limited Local) and see if it helps. Just a guess though, kind of grasping at straws.
I would open a ticket with support, who can work with you interactively, as there could be a lot of variables involved.
Thanks for your suggestions so far everyone. I am still working my way through this, but I have opened a ticket with IBM.
By the way, I have noticed the following:
- This problem only occurs when using if statements (case statements with identical logic are OK)
- This problem appears to only happen in Oracle (can't get it to replicate in SQL Server)
On the advice of IBM support, I am attempting to replicate the problem against the GO Sales database (on Oracle), but it is not doing it. I will have to investigate my model further.
Quote from: scott_a on 29 Aug 2014 08:00:37 AM
On the advice of IBM support, I am attempting to replicate the problem against the GO Sales database (on Oracle), but it is not doing it. I will have to investigate my model further.
Oh dear. Not a simple case, then. It does tally with what I'm seeing too - I was unable to get anything similar to occur. Are there any query items in your model that still have "Unknown" as the usage? The icons make them appear like Attributes, so they can be hard to spot.
Failing that, are there any funky aggregation types set anywhere in the RegularAggregate property?
Good luck!!
MF.
I backed out of FP3 this morning and the problem went away. It is related to FP3, and I let IBM know.
Quote from: scott_a on 02 Sep 2014 08:27:48 AM
I backed out of FP3 this morning and the problem went away. It is related to FP3, and I let IBM know.
Interesting!! Thanks for the update! I wonder if FP4 would have fixed it again?
MF.
Just tried out FP4 for IBM support. FP4 does not resolve the issue.
Quote from: scott_a on 03 Sep 2014 07:40:49 AM
Just tried out FP4 for IBM support. FP4 does not resolve the issue.
Good to know! Thanks for the update!! :)
MF.
IBM reports that in FP3 this is a "feature" that performs better most of the time. To get my reports to stop using sum over partition, they say I can turn off the OLAP_Sum feature in the cogdmor.ini file.