Copay Vs Procedures in a fact table
4 posters
Page 1 of 1
Copay Vs Procedures in a fact table
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.
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
Re: Copay Vs Procedures in a fact table
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
Re: Copay Vs Procedures in a fact table
Option 2 is ideal..
dattatraynale- Posts : 3
Join date : 2011-02-16
Re: Copay Vs Procedures in a fact table
Go with 2. Dimensional modeling is not about organizing the data like the transactional system. Do not be tempted by the dark side....

» 'Routing' the grain of the fact table to multpile members of multiple dimensions causes the fact table to 'explode'
» Integrating new fact table which has one to many relationship with the main fact table in existing star schema
» How to create fact table with measures derived from comparing two fact table rows
» Healthcare Hospital Stay fact table - transaction fact table - a row for In and a row for Out?
» Design a snapshot Fact table in data mart from compliance-enable fact table in enterprise data warehouse
» Integrating new fact table which has one to many relationship with the main fact table in existing star schema
» How to create fact table with measures derived from comparing two fact table rows
» Healthcare Hospital Stay fact table - transaction fact table - a row for In and a row for Out?
» Design a snapshot Fact table in data mart from compliance-enable fact table in enterprise data warehouse
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum