Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Copay Vs Procedures in a fact table

4 posters

Go down

Copay Vs Procedures in a fact table Empty Copay Vs Procedures in a fact table

Post  Jeff Smith Thu Feb 17, 2011 12:42 am

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-04

Back to top Go down

Copay Vs Procedures in a fact table Empty Re: Copay Vs Procedures in a fact table

Post  LAndrews Thu Feb 17, 2011 2:10 am


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

Back to top Go down

Copay Vs Procedures in a fact table Empty Re: Copay Vs Procedures in a fact table

Post  dattatraynale Thu Feb 17, 2011 5:19 am

Option 2 is ideal..

dattatraynale

Posts : 3
Join date : 2011-02-17

Back to top Go down

Copay Vs Procedures in a fact table Empty Re: Copay Vs Procedures in a fact table

Post  ngalemmo Thu Feb 17, 2011 6: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....
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

Copay Vs Procedures in a fact table Empty Re: Copay Vs Procedures in a fact table

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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