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

Thoughts on monthly snapshot

2 posters

Go down

Thoughts on monthly snapshot  Empty Thoughts on monthly snapshot

Post  rajeshwarr59 Tue Jan 05, 2016 11:20 pm

I am trying to develop a claims data model. I get details like claim paid date, service date , status, procedure codes, diagnosis codes, claim type etc. I developed a claim dimension so far but wanted to know your thoughts on modeling the diagnosis and procedures information. I wanted to have a bridge table which looks like this: So on claim dimension, I would have the bridge group key populated and if they can go back to the bridge group table to get additional details of diagnosis codes and the type of diagnosis(primary, secondary etc). Is this the right way of modeling the diagnosis/procedure information in insurance world? Also, on the claim line fact table, I would have the service date id and paid date id fields to link back to the paid , service dates of the claims. But some of the fact tables we have are truncate reload(monthly snapshot) type tables based on the requirements we got from the users. And am a bit confused as to how to model the claim line fact table. If other facts are truncate reload, would it make sense to have this done as accumulating snapshot? And if we end up modeling this as an accumulating snapshot, can someone give me detail of how this works as am fairly new to data modeling. Thanks.

D bridge group:

BridgeGroup Key Diagnosis_Id Diagnosis_type
1 100 1
1 101 2
1 102 3
1 103 4
1 104 5

Diagnosis dim
Diagnosis_Id Diagnosis_Cd Diagnosis-desc
100 V120.1 Chronic disease
101 E100.1 Cardiac


Posts : 21
Join date : 2015-06-26

Back to top Go down

Thoughts on monthly snapshot  Empty Re: Thoughts on monthly snapshot

Post  ngalemmo Wed Jan 06, 2016 12:51 am

Diagnosis is fine.  You may want to find out if it is necessary to track sequence beyond the primary.  If not, you wind up with far fewer groups and a smaller bridge table.

There are two ways to implement diagnosis groupings. One is to assign a new group key to every claim and list the diagnosis in the bridge. The other way is to create unique groups of diagnosis and manage the groups. My experience has been that the latter technique results in a bridge table that is significantly smaller (by over 90%) than the former. This is because most of the time people see doctors for the same things, and doctors tend to report simple matters simply. This technique is probably less effective if you only deal with in-patient data.

Procedures are like line items in an order.  They are kept in a procedure level fact with charges associated with each.

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

Back to top Go down

Back to top

- Similar topics

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