Modeling for correct aggregates

[RudiHendrix]

In our environment we have a Contract dimension with two levels:
Contract Type
  Contract Type
Contract
  Contract Name
  Contract Code
  Contract ID

A Contract Type can have one or more Contracts
A Contract Name can have one or more Contract Codes
A Contract Code can have one or more Contract IDs

Next to that we have a measure:
Volume

If I put it in a query and group by Contract Type it can look as follows:
Contract Type      Contract Name     Contract Code        Volume
A                       A_First                A_First_Code22       15
A                       A_First                A_First_Code23       5
A                       A_Second            A_Second_Code51   20
A                       A_Second            A_Second_Code54   10
A                                                                            50
B                       B_First                B_First_Code12       10
B                       B_First                B_First_Code13       30
B                                                                            40
Total                                                                       90

However, if I remove the Contract Type column and group by Contract Name I get the following:
Contract Name     Contract Code        Volume
A_First                A_First_Code22       15
A_First                A_First_Code23       5
A_First                                            5
A_Second            A_Second_Code51   20
A_Second            A_Second_Code54   10
A_Second                                        10
B_First                B_First_Code12       10
B_First                B_First_Code13       30
B_First                                             30
Total                                                                       90

Now the total per Contract Name is no typing mistake! It always takes the last value. How can I solve this in Framework Manager?

The thing I thought of is: taking the Contract Name, Contract Code and Contract ID and creating several levels out of it. With Contract ID being the lowest level. That can then have all the rest of the attributes. But is there no other solution?

[RudiHendrix]

Just checked it with the Cognos sample model. The same happens if you use the GO Datawarehouse (analysis). Go to the Sales and Marketing (Analysis) --> Inventory --> Product and drag Product color, Product brand and all of the facts to the working area.

If you then group by Product color...the total is not calculated correctly.

Am I using the system in an incorrect way? Of have I found a bug in Cognos?

[addpremkumar]

You try this:

total( [volume] for [contract_name])

this should work!!

[RudiHendrix]

Tried that as well and it doesn't work.

Further I believe the function should work :)

The thing is: we're doing this on a dimensionally modeled system. If I publish the business layer (which is relationally modeled) it DOES work as expected.

That's why I started fiddling around with the samples. Further I also created a service request out of it at Cognos. The guy overthere performed the same action and at his installation (8.4.1. whereas we have 8.4) it worked as expected.

Right now we're guessing it is a bug.

[MFGF]

Can you look at the SQL being generated by the incorrect report - what grouping is being done?

MF.

[RudiHendrix]

I have taken a look at the query.

Here it is:
select "T0"."C0" "Contractnaam", "T0"."C1" "Contractcode", "T0"."C2" "Hoeveelheid_MJ", sum("T0"."C2") over () "Total_Hoeveelheid_MJ_", first_value("T0"."C2") over (partition by "T0"."C0", "T0"."C1") "Total_Hoeveelheid_MJ_5"
from (
select "T_DIM_CONTRACT"."CNTRCT_NAME" "C0", "T_DIM_CONTRACT"."CNTRCT_CODE" "C1", sum(case  when "CONTRACT"."IS_QUANTITY"='Y' then "CONTRACT"."NUM_VAL" else NULL end ) "C2"
from "COG_DWH"."T_DIM_CONTRACT" "T_DIM_CONTRACT", "COG_DWH"."T_FT_CONTRACT" "CONTRACT"
where "T_DIM_CONTRACT"."CNTRCT_ID_SK"="CONTRACT"."CONTRACT_ID_SK"
group by "T_DIM_CONTRACT"."CNTRCT_NAME", "T_DIM_CONTRACT"."CNTRCT_CODE") "T0"
order by "Contractnaam" asc nulls last

If I run this query, I see the column TOTAL_HOEVEELHEID_MJ_ being calculated correctly and displayed in every single row of the query result.
I would expect to see the column TOTAL_HOEVEELHEID_MJ_5 to be filled with the total per CONTRACTNAAM. (And then the same value per group of CONTRACTNAAM)

[RudiHendrix]

Cognos support found the solution to the issue for me:
COGCQ00249307/PM10196 AGGREGATION ON GROUPED ATTRIBUTE OF A LEVEL PRODUCES INCORRECT
RESULTS.

Sub totals are incorrect when trying to total a grouped       
attribute of a level in a DMR model in Query Studio or Report 
Studio.                             
                                 
Further trying to change aggregation for summary rows does not
have effect in Query Studio.                   
                                 
An identical report in 8.3 SP3 or in 8.4.1 produces correct   
results.

Workaround:

1) Create a separate level with colour businesskey and         
membercaption.

2) Add 'ExpandAggrScopeBasedOnGroupBy' entry into           
'CQEConfig.xml' configuration file. (Technote 1424837)
As a workaround, it is advisable to change a setting in the CQEConfig.xml (make a copy of CQEConfig.xml.sample and rename to CQEConfig.xml in the  <installation directory>/configuration directory if it does not already exist).

Add a section called Transformations under the <component name="CQE"> section
---------------------------------------------------------------- as:

        <!-- Description: Transformation entries control the generation of SQL by CQE. -->
        <section name="Transformations">
            <!-- Description: When both _businessKey and _memberCaption of a level result in key projections, then these key projections will be

included in the FOR/AT clause of an aggregate.    (default(on)=1, off=0, choices=0,1)     -->
            <entry name="ExpandAggrScopeBasedOnGroupBy" value="0" />
            <!--    -->           
        </section>