Thoughts on monthly snapshot
2 posters
Page 1 of 1
Thoughts on monthly snapshot
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
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
rajeshwarr59- Posts : 21
Join date : 2015-06-26
Re: Thoughts on monthly snapshot
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.
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.
Similar topics
» Monthly Claims Snapshot
» Monthly snapshot fiscal calendar issue
» Updating Monthly snapshot for prior months...
» Monthly Periodic Snapshot Table using SCD Type1 Dimension tables - Design Question.
» Which date to be used for rolling up the data into monthly aggregate fact ?
» Monthly snapshot fiscal calendar issue
» Updating Monthly snapshot for prior months...
» Monthly Periodic Snapshot Table using SCD Type1 Dimension tables - Design Question.
» Which date to be used for rolling up the data into monthly aggregate fact ?
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum