Question on Multiple fact table design

I have the following relationships in the First Pass at a design for our datamart.

DimClaim
ClaimKey
,,,other Fields

FactClaimPayment
CliamKey
PaymentNumber
Amount

FactClaimReserve
ClaimKey
Amount

FactClaimReviewAgent
ClaimKey
ClaimReviewDayQuantity

FactClaimStatus
ClaimKey

It seems to me that the claim status should not be a fact but a claim can have many statuses just like the claim can have many Review Agent records.

Is there a better way design these tables with regard to a claim or is the current design ok for Facts ?

Thank you,

I question the idea of a claim dimension... in a lot of ways it is like an 'sales order' dimension. You are far better off breaking it down into usable, conformed dimensions, such as date (service date, claim date, adjudication date, payment date, etc...), person (insured, patient, payor, etc...), agent, and other information, depending on your business. The claim ID itself usually winds up as a degenerate dimension value in the fact table.

As far as the facts go, it depends on your business processes. It's difficult to say if any of these make sense and depends a lot on your business, its processes, and what the needs are. For example, a claim status fact is only necessary if you need to track a history of the status of a claim. There are other ways to do this, which way makes sense depends a lot on why such history is needed in the first place.

I definitely need to track history. And since a claim can have one or more claim payment records I thought that would be a candidate for a fact table, but I am open to other suggestions. I am going to have either g a slowly changning fact table or fact transaction for the claim payments. Other than that nothing was "etched in stone".

Payments would definitely be a fact table. You could, potentially, incorporate the other facts in a single table, but I can't say one way or the other without knowing what kind of claim you are talking about and the processes behind agents and status assignment.

Thanks, I was trying to figure out a way to consolidate the fact tables. One person I talked to mentioning figuring out a way to maybe use a many to many relationshiop but since the review agent records cannot be directly traced by date to a given claim reserve amount or claim status I was not sure if there was a way.