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
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..
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?
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.
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...
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