stitch queries

[assafp76]

Hello,
I Have 3 tables:
Claims (cliamnumber[= key],claimyear)
Payments (Payment_key,payment)
Debits (Debit_Key,debit)
each claim can have 0 or more payments
each claim can have 0 or more debits
In the business Layer :
I created ClaimAmount Object = count (Distinct Claim.claimnumber )
I created SumPayments object = sum(Payments.payment)
I created SumDebits object = sum(Debit.debit)

when creating the following report:
claimyear,ClaimAmount,SumPayments,SumDebits
"stitch queries" being made:
1.
select CLAIM.CLAIMYEAR  , sum(PAYMENT.PAYMENTS)  , min(CLAIM.CLAIMNUMBER)    from (
select CLAIM.CLAIMNUMBER CLAIMNUMBER , CLAIM.CLAIMYEAR CLAIMYEAR
from DWPRDCLM.CLAIM CLAIM
group by CLAIM.CLAIMNUMBER, CLAIM.CLAIMYEAR)
CLAIM
LEFT OUTER JOIN DWPRDCLM.PAYMENT PAYMENT
on CLAIM.CLAIMNUMBER = PAYMENT.CLAIMNUMBER
and CLAIM.CLAIMYEAR = PAYMENT.CLAIMYEAR
where CLAIM.CLAIMYEAR between 2009 and 2009
group by CLAIM.CLAIMYEAR

2.
select CLAIM.CLAIMYEAR  , sum(DEBIT.RECEIPT)  , min(CLAIM.CLAIMNUMBER) 
from (
select CLAIM.CLAIMNUMBER CLAIMNUMBER , CLAIM.CLAIMYEAR CLAIMYEAR
from DWPRDCLM.CLAIM CLAIM
group by CLAIM.CLAIMNUMBER, CLAIM.CLAIMYEAR)
CLAIM
LEFT OUTER JOIN DWPRDCLM.DEBIT DEBIT
on CLAIM.CLAIMNUMBER = DEBIT.CLAIMNUMBER
and CLAIM.CLAIMYEAR = DEBIT.CLAIMYEAR
where CLAIM.CLAIMYEAR between 2009 and 2009
group by CLAIM.CLAIMYEAR

I don't understand why cognos do min(CLAIM.CLAIMNUMBER).
By doing it, I get the amount of 1 in ClaimAmount instead of getting the amount of claims in 2009 in the report.

thanks in advance
Assaf

[blom0344]

First of all, you do not code aggregates. You use usage and regular aggregate type (to total for instance) as properties for the fact and let Cognos decide how to generate the SQL. You also need to check your determinants. If the grain is the same for both facts, then only use a unique determinant for the shared dimension (and non for the facts)

Check the recent post on determinants:

http://www.cognoise.com/community/index.php/topic,11557.0.html

[Arsenal]

had a similar issue and I was getting xmin in the Cognos SQL and the same infuriating minimum number instead of the proper aggregation. Had tried determinants etc. I never found out the root cause (support could not do it either) and since everything around here is due day before yesterday, I finally decided to have the DBA give me a view which did a Union All between the 2 fact tables and my issues went away.

If you do find out the root cause, please do post it here. Very curious to know what causes the XMIN

[Lynn]

My suspicion is that the underlying model has CLAIM modeled as a dimension while PAYMENT and DEBIT are modeled as facts. The stitch queries aggregate facts around conformed dimensions, but since Claim Number is part of a dimension, it does not treat it like a fact.

If the model had a fourth fact query subject, e.g., CLAIMFACT, where the count of the number of claims could be drawn than it would aggregate it appropriately since Cognos understands it to be a fact.

Well that's my guess anyway.

[Arsenal]

In my case, I did have a conformed dimension although one of the fact tables would have the conformed key occur just once but the other one could have it occuring multiple times