Payer datamart question

[dmjk2011]

Hello,
I am working on a Healthcare Payer dimensional data mart. My goal is to display the member's eligibility, enrollment, benefits, coverage etc. at the time when the claim was submitted. However, the eligibility, enrollment etc. are captured by other 'event' tables in the source.(with begin and end dates)

I was hoping to be able to consolidate all the information in those event tables into the member dimension and tie it to the claim fact table with a surrogate key so it can queried easily.

But there are often situations when we get retroactive eligibilty/enrollment records which makes me lean towards fact tables since it is easier to handle retroactive changes in fact tables as opposed to dimensions.

Is there a way to accomplish my goal without introducing an additional fact table? (I am trying to avoid the reporting tool having to do a multi-pass/stitch query) If not, what would that fact table look like?
Thanks for your help!

[Jeff Smith]

A lot of those member related elements can change. Benefits, coverage, eligibility can change. And benefits can be complicated.

I tend to put those items in their own dimension tables for a number of reasons. The member dimension can get extremely long. If you add benefits, coverage, etc., the number of rows in the dimension will grow quickly. Adding a lot of other elements will also make the table very wide. Plus, in many instances, you may want to aggregate the data to the coverage or the benefit level. In those situations, the query will have to go through an extremely large dimension/ If benefit and coverage were in a smaller, seperate dimension, the query will work much, much faster.

My experience is that Benefits can differ based on procedure, network, etc. Services can be covered at different rates, In Network reimbursement rates can be different from out of network, copays can vary.

[dmjk2011]

Jeff,
Thank you. I was leaning towards dimensions due to the retroactive changes that happens to enrollment and coverage, but you have given me additional reasons to do so. Another requirement I learned recently was for the users to be able to see what the member's coverage and eligibility looked like when the claim was incurred and also when the claim was paid (specifically for claims where payment was delayed) So I see role playing eligibility/coverage dimensions tied to the claim fact table.

Thanks again!

[Jeff Smith]

I have Coverage as a seperate dimension connected to the member and claim fact, which allows us to calculate loss ratios base on coverage.

Benefits on the Claim and member can be tricky. The benefits for a member can be varied. Different copays based on In/Out of Network, different copays for the type of procedures, etc. The benefit info can be linked to the Claim, but it's tough to do so unless it comes with the claim. Otherwise, you basically have to reprocess the claim in the ETL process.

[Sponsored content]