Copay Vs Procedures in a fact table

View previous topic View next topic Go down

Copay Vs Procedures in a fact table

Post  Jeff Smith on Wed Feb 16, 2011 6:42 pm

We're a dental insurance company. The claim has a Header Record and Detail Record. The detail Record has the procedure - 1 row per procedure, multiple procedures on a claim.. The Header Record has the Copay. I have 2 choices for fact tables.

1) 2 fact tables. The Detail or procedure Level Fact and the Header or Claim level Fact. The procedure Level has the Charged Amount, Covered Amount, Insurance Paid Amount, and the CoPayment (different from the CoPay - CoPay is $20 per office visit, CoPayment is share of the Covered amount not paid by the Insurance. The Claim Level Fact contains 1 record per claim and has the Copay as a measure along with aggregate Charged, Covered, CoPayment, and Insurance Paid Amounts from the Procedure Level Fact.

2) 1 Fact Table at the Detail Level. Treat the CoPay as a Procedure, using it to populate the Charged Amount and CoPayment Amount. Add a row to the Procedure dimension for the CoPay record.

Option 1 feels more kosher because it organizes the data like the transaction system. But Option 2 seems like the better option. I get the same information and it sames a ton of space.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Copay Vs Procedures in a fact table

Post  LAndrews on Wed Feb 16, 2011 8:10 pm


I'd lean towards option 2 - treat each office visit as a procedure. If the claim covers 5 office visits, then there would be 5 fact records for the "office visit" procedure.

LAndrews

Posts : 132
Join date : 2010-05-13
Location : British Columbia, Canada

View user profile

Back to top Go down

Re: Copay Vs Procedures in a fact table

Post  dattatraynale on Wed Feb 16, 2011 11:19 pm

Option 2 is ideal..

dattatraynale

Posts : 3
Join date : 2011-02-16

View user profile

Back to top Go down

Re: Copay Vs Procedures in a fact table

Post  ngalemmo on Thu Feb 17, 2011 12:52 am

Go with 2. Dimensional modeling is not about organizing the data like the transactional system. Do not be tempted by the dark side....
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Copay Vs Procedures in a fact table

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