Question on Multiple fact table design

View previous topic View next topic Go down

Question on Multiple fact table design

Post  mru22 on Tue Jan 03, 2012 2:09 pm

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,

mru22

Posts : 34
Join date : 2011-06-14

View user profile

Back to top Go down

Re: Question on Multiple fact table design

Post  ngalemmo on Tue Jan 03, 2012 6:48 pm

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.

avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Question on Multiple fact table design

Post  mru22 on Wed Jan 04, 2012 10:30 am

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

mru22

Posts : 34
Join date : 2011-06-14

View user profile

Back to top Go down

Re: Question on Multiple fact table design

Post  ngalemmo on Wed Jan 04, 2012 11:29 am

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Question on Multiple fact table design

Post  mru22 on Wed Jan 04, 2012 12:10 pm

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.

mru22

Posts : 34
Join date : 2011-06-14

View user profile

Back to top Go down

Re: Question on Multiple fact table design

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum