Aggregation Issue!!!

[k2]

Hi there,

I am currently facing aggregation issue in my report/framework. I have tried a report which is suppose to bring back 4 values and display the total of those 4 values, but it is displaying only 1 value (which is minimum) out of the four. when i tried running the Native SQL against the DB, it is bringing back the sum of 4 values.

I am reporting the numeric value against Country field only. The numeric field is mentioned as Fact with Aggregation SUM in Framework.

Please help me out if am doing anything wrong. Am using Cognos 8.4 against SQL Server 2005.
Your responses will be much appreciated. Cheers

¬ K 2

[MFGF]

How many tables are being spanned in order to retrieve the four items - ie are they all in one table, or split across multiple tables?
If they are split across multiple tables, how are the tables joined in your FM model?  Are there any model query subjects based on top of these?  Are they joined too?

Regards,

MF.

[k2]

Hi MF,

Thanks for your response. The values are made available in one table which is Fact and which is joined to a Dimension called Country, which gives corresponding value for Country. Inner join is made between the two tables and the query subjects are made available in the model as well!

Please let know if it is not clear. Cheers

Regards,
K2

[MFGF]

Hi,

OK - so the data is split across two physical tables.  How are these joined in your data layer in FM? (ie are you using 1..1 -> 1..n cardinality, and if so, which table is at the 'n' end?

Do you have any abstract modelling layers on top of these (ie model query subjects)?  If so, are there any relationships defined there?

One other question - if you view the generated Cognos SQL for the query, what does it look like?

MF.

[Arsenal]

Agree with MFGF (am getting tired of saying that  ;D)
..look atthe Cognos SQL and specifically, look within the select statement for XMIN ...have had a similar issue before (but I was using multi fact query) and Cognos absolutely insisted on using XMIN with RSUM's and this XMIN was causing Cognos to spit out the minimum of the multiple values.

Also look at your cardinality..you need to decide the correct one between your fact and dimension

[k2]

Fact tables are in the 'n' end of the cardinality!

Please find the Cognos SQL of the query, guess which give a clear picture.

select
       XSUM(((T1.CURRENT_PRINCIPAL_BALANCE * case when (DWD_ROLE.PARTY_ROLE_ID = 500) then 1 else 0 end ) * case when (T3.FACILITY_TYPE_ID = 440) then 1 else 0 end )  at DWD_CURRENCY.CURRENCY_ISO_CODE,T5.COUNTRY_NAME,T1.DWD_PARTY_ROLE_ID,T1.DWD_PARTY_ID  for DWD_CURRENCY.CURRENCY_ISO_CODE,T5.COUNTRY_NAME )  as  Capitalised_Interest_ECGD,
       DWD_CURRENCY.CURRENCY_ISO_CODE  as  Tranche_Currency_ISO_Code,
       XSUM((T1.INTEREST_BALANCE * case when (DWD_ROLE.PARTY_ROLE_ID = 500) then 1 else 0 end )  at DWD_CURRENCY.CURRENCY_ISO_CODE,T5.COUNTRY_NAME,T1.DWD_PARTY_ROLE_ID,T1.DWD_PARTY_ID  for DWD_CURRENCY.CURRENCY_ISO_CODE,T5.COUNTRY_NAME )  as  c3,
       0  as  Late_Interest_Accrued_ECGD,
       XSUM((((T1.INTEREST_BALANCE * case when (DWD_ROLE.PARTY_ROLE_ID = 500) then 1 else 0 end ) * case when (T3.FACILITY_TYPE_ID = 430) then 1 else 0 end ) + ((T1.CURRENT_PRINCIPAL_BALANCE * case when (DWD_ROLE.PARTY_ROLE_ID = 500) then 1 else 0 end ) * case when (T3.FACILITY_TYPE_ID = 420) then 1 else 0 end ))  at DWD_CURRENCY.CURRENCY_ISO_CODE,T5.COUNTRY_NAME,T1.DWD_PARTY_ROLE_ID,T1.DWD_PARTY_ID  for DWD_CURRENCY.CURRENCY_ISO_CODE,T5.COUNTRY_NAME )  as  c5,
       T5.COUNTRY_NAME  as  Country
from
       (select
              T1.DWD_OBLIGATION_ID  as  DWD_OBLIGATION_ID,
              T1.DWD_CUSTOMER_ID  as  DWD_CUSTOMER_ID,
              T1.DWD_PARTY_ROLE_ID  as  DWD_PARTY_ROLE_ID,
              T1.DWD_PARTY_ID  as  DWD_PARTY_ID,
              T1.FACT_SSHOT_DATE_ID  as  FACT_SSHOT_DATE_ID,
              T1.DWD_MONTH_ID  as  DWD_MONTH_ID,
              T1.DWD_CURRENCY_ID  as  DWD_CURRENCY_ID,
              T1.CURRENT_PRINCIPAL_BALANCE  as  CURRENT_PRINCIPAL_BALANCE,
              T1.INTEREST_BALANCE  as  INTEREST_BALANCE,
              T1.DWD_FACILITY_ID  as  DWD_FACILITY_ID
        from
              CEDAR_UAT.CEDAR_UAT3.dbo.DWF_MONTHLY_OBLIGATION_DETAIL T1
        where
              (T1.PORTFOLIO_ID = 'P1')
       ) T1,
       CEDAR_UAT.CEDAR_UAT3.dbo.DWD_ROLE DWD_ROLE,
       (select
              T1.DWD_FACILITY_TYPE_ID  as  DWD_FACILITY_TYPE_ID,
              T1.FACILITY_TYPE_ID  as  FACILITY_TYPE_ID
        from
              CEDAR_UAT.CEDAR_UAT3.dbo.DWD_FACILITY_TYPE T1
        where
              (T1.FACILITY_TYPE_ID in (410,420,430,440))
       ) T3,
       CEDAR_UAT.CEDAR_UAT3.dbo.DWD_CURRENCY DWD_CURRENCY,
       CEDAR_UAT.CEDAR_UAT3.dbo.DWD_COUNTRY T5,
       CEDAR_UAT.CEDAR_UAT3.dbo.DWD_CALENDAR DWD_CALENDAR,
       CEDAR_UAT.CEDAR_UAT3.dbo.DWD_PARTY_LIMIT DWD_PARTY_LIMIT,
       CEDAR_UAT.CEDAR_UAT3.dbo.DWD_OBLIGATION DWD_OBLIGATION,
       CEDAR_UAT.CEDAR_UAT3.dbo.DWD_ACCRUAL T9,
       CEDAR_UAT.CEDAR_UAT3.dbo.DWD_FACILITY DWD_FACILITY,
       (select
              T1.DWD_FACILITY_ID  as  DWD_FACILITY_ID,
              T1.FACILITY_CUSTOMER_ID  as  FACILITY_CUSTOMER_ID,
              T1.DWD_PARTY_ID  as  DWD_PARTY_ID,
              T1.DWD_PARTY_ROLE_ID  as  DWD_PARTY_ROLE_ID,
              T1.DWD_PARTY_LIMIT_ID  as  DWD_PARTY_LIMIT_ID,
              T1.DWD_CURRENCY_ID  as  DWD_CURRENCY_ID,
              T1.FACT_SSHOT_DATE_ID  as  FACT_SSHOT_DATE_ID,
              T1.DWD_MONTH_ID  as  DWD_MONTH_ID
        from
              CEDAR_UAT.CEDAR_UAT3.dbo.DWF_MONTHLY_FACILITY_DETAIL T1
        where
              (T1.PORTFOLIO_ID = 'P1')
       ) T11,
       CEDAR_UAT.CEDAR_UAT3.dbo.DWF_MONTHLY_OBLIGATION_ACCRUAL T12
where
       (DWD_CALENDAR.CALENDAR_DATE = CAST(:PQ1 AS timestamp)) and
       ((DWD_PARTY_LIMIT.ARCHIVE_DATE is NULL) or (DWD_PARTY_LIMIT.ARCHIVE_DATE > CAST(:PQ1 AS timestamp))) and
       (DWD_ROLE.PARTY_ROLE_ID = 500) and
       (DWD_OBLIGATION.LOAN_STATUS_CODE in (1,2)) and
       ((((T1.INTEREST_BALANCE * case when (DWD_ROLE.PARTY_ROLE_ID = 500) then 1 else 0 end ) * case when (T3.FACILITY_TYPE_ID = 430) then 1 else 0 end ) + ((T1.CURRENT_PRINCIPAL_BALANCE * case when (DWD_ROLE.PARTY_ROLE_ID = 500) then 1 else 0 end ) * case when (T3.FACILITY_TYPE_ID = 420) then 1 else 0 end )) > 0) and
       (DWD_OBLIGATION.PORTFOLIO_ID = 'P1') and
       (T9.PORTFOLIO_ID = 'P1') and
       (DWD_FACILITY.PORTFOLIO_ID = 'P1') and
       (DWD_OBLIGATION.PORTFOLIO_ID = 'P1') and
       (T9.PORTFOLIO_ID = 'P1') and
       (((((((T11.DWD_FACILITY_ID = T1.DWD_FACILITY_ID) and (T11.FACILITY_CUSTOMER_ID = T1.DWD_CUSTOMER_ID)) and (T11.DWD_PARTY_ID = T1.DWD_PARTY_ID)) and (T11.DWD_PARTY_ROLE_ID = T1.DWD_PARTY_ROLE_ID)) and (T11.DWD_CURRENCY_ID = T1.DWD_CURRENCY_ID)) and (T11.FACT_SSHOT_DATE_ID = T1.FACT_SSHOT_DATE_ID)) and (T11.DWD_MONTH_ID = T1.DWD_MONTH_ID)) and
       (DWD_FACILITY.DWD_FACILITY_ID = T11.DWD_FACILITY_ID) and
       ((T5.DWD_COUNTRY_ID = DWD_FACILITY.DWD_COUNTRY_CODE_OF_EXPOSURE_RISK) and (DWD_FACILITY.PORTFOLIO_ID = 'P1')) and
       (T3.DWD_FACILITY_TYPE_ID = DWD_FACILITY.DWD_FACILITY_TYPE_ID) and
       (DWD_PARTY_LIMIT.DWD_PARTY_LIMIT_ID = T11.DWD_PARTY_LIMIT_ID) and
       ((((((T1.DWD_OBLIGATION_ID = T12.DWD_OBLIGATION_ID) and (T1.DWD_CUSTOMER_ID = T12.DWD_CUSTOMER_ID)) and (T1.DWD_PARTY_ROLE_ID = T12.DWD_PARTY_ROLE_ID)) and (T1.DWD_PARTY_ID = T12.DWD_PARTY_ID)) and (T1.FACT_SSHOT_DATE_ID = T12.FACT_SSHOT_DATE_ID)) and (T1.DWD_MONTH_ID = T12.DWD_MONTH_ID)) and
       (T9.DWD_ACCRUAL_ID = T12.DWD_ACCRUAL_ID) and
       (DWD_CURRENCY.CURRENCY_ID = T1.DWD_CURRENCY_ID) and
       (DWD_OBLIGATION.DWD_OBLIGATION_ID = T1.DWD_OBLIGATION_ID) and
       (DWD_ROLE.DWD_PARTY_ROLE_ID = T1.DWD_PARTY_ROLE_ID) and
       (DWD_CALENDAR.DATE_ID = T1.FACT_SSHOT_DATE_ID)
group by
       DWD_CURRENCY.CURRENCY_ISO_CODE,
       T5.COUNTRY_NAME
order by
       Tranche_Currency_ISO_Code asc