2 Fact Tables

[assafp76]

Hello,
I Have 2 Fact Tables which i can't join:

paymentFact and debitFact

I have a dimension : SubDlaimDim:

SubClaimDim 1,1 <------>0,n DebitFact
SubClaimDim 1,1 <------>0,n PaymentFact

SubClaimDim:
claimyear subclaimnumber
2009       00011234567-01
   
DebitFact:   

receiptnumber subclaimnumber claimyear receiptdate receipt 218118          00011234567-01 2009      01/05/2009 2807

PaymentFact:   

orderpayment subclaimnumber claimyear paymentdate payment 10000           00011234567-01 2009       01/02/2009 2807 10001           00011234567-01 2009       01/03/2009 3757 10002           00011234567-01 2009       03/03/2009 104515 10003           00011234567-01 2009       31/03/2009 -104515 10004           00011234567-01 2009       31/03/2009 104515 10005           00011234567-01 2009       01/04/2009 2425 10006           00011234567-01 2009       01/05/2009 2807 10007           00011234567-01 2009       03/05/2009 5540

Most of owr users going to use the Query Studio.
How can i construct structure  that enable doing the following queries:
1.
select:
subClaimNumber,ClaimYear,sum(Payments),sum(Receipt)
where
SubClaimNumber=00011234567-01
the result should be:

00011300019-01 2009 121851 2807
2.
select:
subClaimNumber,ClaimYear,sum(Payments),sum(Receipt)
where
SubClaimNumber=00011234567-01 and
paymentdate >=01/06/2009
the result should be:
00011300019-01 2009 0 2807

thank you,
Assaf

[blom0344]

Facttables should be joined over the (conformed) dimension. If you are not interested in dimensional values where no fact exists you might reconsider and  use normal  1:1 -- 1:n cardinality.

Cognos is supposed to split the query , perform the aggregation and restitch the 2 set to one resultset. Most of the time this seems to work pretty well, but it sometimes seems to have a mind of its own..

[assafp76]

thank you for your replay. by writing 'restitch the 2 set', did you mean that cognos will generate full outer join?
if so, is there any other way to do it? 

[blom0344]

Yes, when you check on SQL generated you will notice the full outer join (combined with coalesce on the non facts that are not involved in the full join) If you think this through, this is in most cases desirable.

[assafp76]

Do you mean that by doing the following relationship, there is no other way than, cognos generats a full outer join?
SubClaimDim 1,1 <------>0,n DebitFact
SubClaimDim 1,1 <------>0,n PaymentFact

If so, it will put me on a problematic stuation, becuase this full outer join thakes 12 minites to run...

[blom0344]

The alternative is to define 2 seperate queries and define a third as inner join of 1 and 2 from within Report Studio.

But..  Your cardinality is such that an outer join is defined between dimension and fact. That is only relevant in case you want to see dimensional valus regardless whether a fact exists. This is normally not the case, so you might reconsider and use  1:1 -- 1:n which will be VERY beneficial to performance in ANY case