Insurance Claims

View previous topic View next topic Go down

Insurance Claims

Post  tim_goodsell on Sun Sep 26, 2010 10:55 pm

Hi

I was reading the Kimball DW Toolkit and need some advice concerning the chapter concernining Claims Processing:

Our dataware contains a claims accumulating fact table containing a number of date fields (received date, signed date, entered into the system date etc ) along with other keys (claimant key, claim status key linked to client and claim status dimensions) .

A claim can be one of three types (Death Total Permanent Disability and Income Protection). Is it worth having a seperate dimension (Claim Type) and put a key in the accumulating fact table or just have a column in the fact table taht can have the values "Income Protection", "Total Benefit Disability" and "Death". We will be doing a lot of reporting on things like the number of new claims received suring a date period etc.

Regards

Tim

tim_goodsell

Posts : 49
Join date : 2010-09-21

View user profile

Back to top Go down

Re: Insurance Claims

Post  ngalemmo on Mon Sep 27, 2010 12:22 pm

Create a claim type dimension.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Insurance Claims

Post  BoxesAndLines on Mon Sep 27, 2010 3:07 pm

What he said.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Insurance Claims

Post  tim_goodsell on Mon Sep 27, 2010 6:16 pm

Thanks for that.

I assume that the claims type key will be added to the fact table not the claims dimension table


Regards

Tim

tim_goodsell

Posts : 49
Join date : 2010-09-21

View user profile

Back to top Go down

Re: Insurance Claims

Post  ngalemmo on Mon Sep 27, 2010 6:33 pm

You have a claim dimension table? One row per claim? What's in it?

The reason I ask is, a lot of information on the claim can be covered by other dimensions (payor, provider, patient, dates, demographics, etc...) so you often only have a few leftovers, such as claim type, which wind up in junk or separate dimensions.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Insurance Claims

Post  tim_goodsell on Mon Sep 27, 2010 8:55 pm

Not alot at the moment except for the ClaimKey and a couple of other columns that hold clam id's from the source systems.


So far I have:

A claims accum fact table that stores the attributes (mostly dates) concerning claim processing (date claim received, date of claim injury , date claim was closed) very similar to what is in the toolkit book.

A benefit decisions accum fact table that tracks the lifespan of a claim benefit decision (there can be multiple benefit decsiins per claim but not simultaneously)

A monthly snapshot fact table that combines the claims accum fact and the latest benefit descisions fact (joined by claim key). This allows me to compare dates like claim received date through to latest benefit decision date.

The above three fact tables have a claimkey column which links the facts to the claims dimension. However I am slight confused where to put the claim type (claims accum fact or claims dimension)

Regards

Tim`





tim_goodsell

Posts : 49
Join date : 2010-09-21

View user profile

Back to top Go down

Re: Insurance Claims

Post  ngalemmo on Tue Sep 28, 2010 12:13 pm

One objective in designing dimension tables is to keep them small (i.e. a reasonable number of rows), so claim type as its own dimension or combined with a few other low cardinality attributes (or correlated attributes) in a junk dimension is preferable.

If the only thing in the claim dimension is the source claim ID, you may want to discard that dimension and store the claim ID as a degenerate dimension in the facts. That is commonly done with things like order and invoice numbers. If there are a few other attributes left over (from the claims dimension) put them in one or two junk dimensions as well.... maybe incorporating them wiht claim type.

If you have a design where there is a near 1:1 relationship between a fact table and a dimension table, you need to rethink the model.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Insurance Claims

Post  tim_goodsell on Tue Sep 28, 2010 7:39 pm

Hi thanks for your answer and I agree that as a general rule of thumb a dimension table should have a few rows and a fact table a lot. However what has confused me is that the Claims Processing Snapshot Fact table in the toolKit book (p324) refers to a Claims Dimension, isn't that a 1:1 relationship ?

Regards

Tim

tim_goodsell

Posts : 49
Join date : 2010-09-21

View user profile

Back to top Go down

Re: Insurance Claims

Post  ngalemmo on Wed Sep 29, 2010 12:51 pm

Don't have the book handy... but there really are no 'right' answers... it all has to do with context.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Insurance Claims

Post  BoxesAndLines on Wed Sep 29, 2010 1:02 pm

It's been awhile since I did health care, but Ralph states that this dimension has the applicable coverage for the claim. That would imply to me that this is not a 1-1 relationship especially since he specifically tells us earlier that we should not do this.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Insurance Claims

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