OK - If I understand correctly, you have a ClaimTransaction Fact table that is FK'd to a Claim Dimension, and you have a Repair Fact table that is FK'd to a Repair Dimension and to the Claim Dimension. You have a Repair Date in the Repair Fact table that you want to include in the ClaimTransaction Facts. You are trying to join the two Fact tables using the Claim_Key (which I assume is the PK of the Claim Dimension). Am I following so far?
The simple solution seems to be to join the two Fact tables on the Claim_Key field, like:
Code:
SELECT
*
FROM
dbo.SNA_ACC_CLAIMTRANSACTION_FACT CF
JOIN dbo.SNA_ACC_REPAIR_FACT RF
ON RF.CLAIM_KEY = CF.CLAIM_KEY
But you also said "...the surrogate key in the claim fact may differ from the surrogate key in the repair fact". Does this mean that Claim_Key 123 in the Claim Fact table refers to a different claim than Claim_Key 123 in the Repair Fact table? If so, then obviously the above join won't work. In fact, in this case I can't fathom any way of joining the two, even through the Dimension tables.
If the surrogate keys are generated differently, then I think your only answer is to go back to the ETL process and make one of two changes:
1. Add the Repair Date to the Claim Fact table. This would simplify any queries that you need to do down the road, but may add some complexity to your ETL process. You would also need to think about how to deal with NULL Repair Dates.
2. Generate a new surrogate key that relates the two Fact tables and join on the new key.
Hope this helps a bit....
- Steve