Print Page - Cognos generating SUM OVER() clause while aggregating fact

Title: Cognos generating SUM OVER() clause while aggregating fact
Post by: shadowhunter on 27 Nov 2014 02:49:30 AM

Hi..
In report studio, when i pull an attribute from a dimension and a measure from fact, cognos somehow applies the SUM with over ( ) clause. As a result, all the rows have the same sum repeated. Below is a sample example of how the things are summing up: I am using relational model and the facts and dimensions are connected through factless-fact. Also, the factless fact stores daily data and the fact also stores the daily data.

DIMENSION FACT
-------------- --------
ID VALUE

ID -> below are the properties set in FM
Usage -> Attribute (this is not a surrogate key. This is an attribute associated with a transaction)
Regular Aggregate -> Unsupported
Semi Aggregate -> Unsupported

VALUE -> below are the properties set in FM
Usage -> FACT
Regular Aggregate -> SUM
Semi Aggregate -> SUM

Now in report studio, I create a query subject and drag and drop ID and VALUE as query items. In report studio, VALUE has the aggregate type of TOTAL set. Below is the sample SQL generated. Please note that i have modified the SQL to just mention the relevant table and column with the issue at focus (Rest joins with other tables are proper as expected)

SELECT "T0"."C0" "ID",
SUM("T0"."C1") over () "VALUE"
FROM
(SELECT "T_DIM_TAB8"."ID" "C0",
SUM("T_FACT_TAB9"."VALUE") "C1"
FROM "DIM_TAB" "T_DIM_TAB8",
"FACT_TAB" "T_FACT_TAB9"

WHERE (some join conditions)
GROUP BY "T_DIM_TAB8"."ID"
) "T0"

Title: Re: Cognos generating SUM OVER() clause while aggregating fact
Post by: raj_aries81 on 27 Nov 2014 05:32:46 AM

Hi Shadowhunter,

Can you change the 1..N to 1..1 on fact side and check if you still see this issue. Just a thought.

Regards
Raj

Title: Re: Cognos generating SUM OVER() clause while aggregating fact
Post by: raj_aries81 on 27 Nov 2014 05:39:22 AM

Hope there are no determinants & changing 1..N to 1..1 only if there are no multi-facts :)

Title: Re: Cognos generating SUM OVER() clause while aggregating fact
Post by: shadowhunter on 27 Nov 2014 10:46:50 AM

Hi Raj,
First of all thanks for the reply and suggestion..
Regarding determinants, i do not have any determinants set on any of the tables explicitly.
Second, as i mentioned, the fact and the dimension are joined through factless fact (bridge) and there is no direct join.
The cardinality is 1..1 for the fact side and 1..n for the factless fact.
I have prepared a powerpoint ppt for the relationship modelling..Not able to get how to attach it here..if you can guide me i will attach it in my next post.

Title: Re: Cognos generating SUM OVER() clause while aggregating fact
Post by: shadowhunter on 27 Nov 2014 10:49:44 AM

Hi Raj,
Please find attached the ppt in which i have mentioned the modelling.
Any thoughts?

Title: Re: Cognos generating SUM OVER() clause while aggregating fact
Post by: FabianGaussling on 03 Dec 2014 10:04:19 AM

Hi,

can you post the whole SQL with all join-conditions, etc. Are there further tables included in your query?

kind regards,

Fabian

COGNOiSe.com - The IBM Cognos Community

IBM Cognos 10 Platform => Cognos 10 BI => Framework Manager => Topic started by: shadowhunter on 27 Nov 2014 02:49:30 AM