Payer datamart question

View previous topic View next topic Go down

Payer datamart question

Post  dmjk2011 on Mon Jan 09, 2012 6:56 pm

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!

dmjk2011

Posts : 7
Join date : 2011-05-09

View user profile

Back to top Go down

Re: Payer datamart question

Post  Jeff Smith on Tue Jan 10, 2012 3:34 pm

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.


Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Payer datamart question

Post  dmjk2011 on Wed Jan 11, 2012 1:18 pm

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!

dmjk2011

Posts : 7
Join date : 2011-05-09

View user profile

Back to top Go down

Re: Payer datamart question

Post  Jeff Smith on Wed Jan 11, 2012 2:52 pm

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.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Payer datamart question

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