Cognos generating SUM OVER() clause while aggregating fact

[shadowhunter]

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"

[raj_aries81]

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

[raj_aries81]

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

[shadowhunter]

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.

[shadowhunter]

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

[FabianGaussling]

Hi,

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

kind regards,

Fabian