Insurance Claims

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

Create a claim type dimension.

What he said.

Thanks for that.

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


Regards

Tim

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.

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`

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.

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

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

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.