Insurance Claim Model

Hi

I have a Claims fact table that contains the total claim value paid with a time dimension based on the date the claim was first raised. This works fine but a claim can be ongoing and span over more than one year with many payments being made. The business want to know how much is paid each year(and drill down to quarter/month product etc) on claims regardless of the year the claim was first raised we do keep the payment date and amount paid in the source database but this is obviously in a different context and grain to the Claim facts we already have. is it acceptable to have another fact table more granular to the first but related by the claim surrogate key from the original fact table or is there a better way to build this?

If i do it this way i also need to build a cube but if i roll up the payments it will sum the total claim paid by the number of payments made in the 2nd fact table?

Hope i have explained this well enough for someone to be able to assist.

Kind Regards
Paul

Nothing wrong with that approach at all, 1st can be considered a Claims Summary Fact and the 2nd Claims Payment Fact.

Absolutely! Having a claim payment fact table allows you to look at the payment data at its most granular level. With it, you could answer questions like "How long does it take for us to set a reserve/make first payment/make final payment?"

As long as you are not joining the two fact tables (and are using conformed dimensions to do so), it should be fine.