Insurance Claims and Features Design

Hi,

I am in the process of building a dimensional model for Insurance Claims and I've already received some great help within this forum on another issue I was having :

http://forum.kimballgroup.com/t2146-insurance-claims-model-question

However, I have another design question that I am trying to work through regarding claims transactions.

The way our claims transactions are setup is that each claim gets an occurrence number. This number never changes through the life of the claim. Within the occurrence, there are feature numbers. Each feature number is related to a coverage type. For example, a customer calls to create a claim because he had a fire in his house. The claim would be created and given an Occurrence Number (12345 for this example). Next, 3 separate features are created (f1, f2, f3) to handle the different transactions that occur. The 1st feature (f1) handles payments and reserves against the dwelling (dwelling coverage), the 2nd feature (f2) handles payments and reserves against the contests (personal property coverage) and the 3rd feature (f3) handles payments and reserves against additional living expenses (loss of use coverage).

Now my issue is that I need to track both the Occurrence Number and Feature Numbers (along with the coverage types) for this claim and the status of the claim as either being Opened or Closed. The Claim or "Occurrence" status is tied to the features because all of the features need to be completed or closed for the entire claim or Occurrence to be considered closed.

I'm not sure how to deal with the Occurrence and Feature numbers. Should they be FK fields in the Claims Fact table and have their own dimensions? Basically there is a 1 to many relationship between Occurrences and Features. Each Claim Occurrence can have 1 or many features associated with it, with a separate close date for each feature. Each feature has a 1 to 1 relationship with a coverage type (each feature maps to exactly 1 coverage type).

Sorry of the description is long, but wanted to make sure I explained everything correctly.

Thanks
Scott

scabral wrote:Now my issue is that I need to track both the Occurrence Number and Feature Numbers (along with the coverage types) for this claim and the status of the claim as either being Opened or Closed. The Claim or "Occurrence" status is tied to the features because all of the features need to be completed or closed for the entire claim or Occurrence to be considered closed.

I'm not sure how to deal with the Occurrence and Feature numbers. Should they be FK fields in the Claims Fact table and have their own dimensions? Basically there is a 1 to many relationship between Occurrences and Features. Each Claim Occurrence can have 1 or many features associated with it, with a separate close date for each feature. Each feature has a 1 to 1 relationship with a coverage type (each feature maps to exactly 1 coverage type).

Based on this info, I would say both Occurrence and Feature numbers are the degenerate dimensions (DD), no need to model them explicitly as separate dimensions. Their other characteristics (e.g. Status you mentioned) should be a separate dimension attached to the fact. Assuming there is no further granularity to the fact table, Occurrence/Feature numbers will define the grain.

Thank you Nash for your reply,

However, the more I think about this design, I don't think I can make the Feature Number(s) a Degenerate Dimension. The reason being that any Occurrence can have either 1 or many features, so the number can vary for each Occurrence. I guess my question now would be how to handle a Feature Number in the fact table if the number of Features can vary for each Occurrence of a claim?

Thinking about it now, I almost want to make the Occurrence a dimension with a FK in the fact table and have the Occurrence Status be part of the Occurrence dimension. This way if all of the features have been completed, the occurrence status can be marked as "Closed", otherwise it can be "Open".

I still don't know how the handle the feature numbers. What is the best practice for storing something that can have 1 or many values as part of an occurrence??

Thanks
Scott

This is the classic header/detail problem. The lowest grain of your fact table should Claim Feature. This is where you are tracking open and close dates, coverage types, etc. The other issue is you want to track claim level metrics as well, claim amount, claim counts, etc. I assume that the claim amounts are not allocated to the feature level. You have two choices. You can either build a bridge table to capture the many features for a given claim or build your fact table at the feature level and then create an aggregate table at the claim level. Both solutions are acceptable from a best practice perspective.

Thanks BoxesAndLines,

I think you are on to something, but I'm still a little confused.

Let's say i have a claim occurrence (Occurrence 12345 for example). This claim has 2 features, f1 and f2. f1 is to track dwelling payments/reserves and f2 is for dwelling contents (jewelery, clothes, etc...).

Feature 1 can have many claim amounts associated with it ($100 loss reserve, $100 loss payment, $500 expense reserve, etc...) each with a separate payment/reserve dates. These payments/losses are separate rows in the source table. Feature 2 has its own set of claim amounts ($500 loss reserve,$300 loss payment, etc...) with its own set of dates.

Knowing this and building the fact table at feature level, how do i track all of the different rows for each feature when the feature number is the same? Does it matter that the each feature number will have multiple rows in the fact table?

So for example, f1 will have 5 rows in the fact table with 5 amounts and dates. f2 will have 3 rows in the fact table with 3 amounts and dates. Also, does that make feature number a degenerate dimension in the fact table? And what if the date of 2 transactions of f1 are on the same day and for the same amount and same type? Does the row have to be unique at the feature level for the grain to work?

thanks for your help.

Scott

To try and make this simple, here is how I see our claims source data:

Each Claim has 1 Occurrence Number.

Each Occurrence Number has 1 or many Feature Numbers.

Each Feature Number has 1 or many Transactions associated with it.

So given this setup, what is the best method for designing the fact table to track the claims process?

Each of the amounts is a column in the fact table at the lowest level. Your normalizing the data structure. The goal is to get all of the amounts and dates on the same row for a given claim feature.

Ok,

so each amount (Loss reserve, Loss Payment, Expense Reserve, Expense Payment) will have a separate column for each feature, i get that.

How about if I have the same feature with multiple payments. For example Feature 111 might have several Loss Payments and Loss Reserves. They may happen on different dates or on the same day and they may be different amounts or the same amounts. Would it be a separate row for each feature and Transaction type?

Also, Should feature be its own dimension with its own attributes? How about the occurrence number? should that be a DD?

Here is the design I have so far:

LineOfBusiness (FK)

RiskSate_ID (FK)

Agent_ID (FK)

Insured_ID (FK)

Claimant_ID (FK)

Payee_ID (FK)

Employee_ID (FK)

Adjuster_ID (FK)

CAT_ID (FK)

Occurrence_Status (FK)

Loss_Date (FK)

Notify_Date (FK)

Transaction_Date (FK)

Close_Date (FK)

Feature_Type (FK)

Peril_ID (FK)

Occurrence_No (DD?)

Feature_No (DD?)

Loss_Reserve_Amt

Loss_Payment_Amt

Expense_Reserve_Amt

Expense_Payment_Amt

Subro_Reserve_Amt

Expense_Payment_Amt

Subro_Reserve_Amt

Subro_Payment_Amt

The question that I have about this design are the fields that are related to the feature (Close_Date, Transaction_Date, Feature_Type, Peril_ID). Should I create a Feature Dimension and have all of these fields as attributes and have the Feature_No be a FK in the fact table?

Also, Should The Occurrence be a Degenerate Dimension in the fact table? Does this design hold true the grain of the Claim Transaction fact table?

thanks
Scott

If you lump all the feature dimensions into a feature dim don't you end up with 1-1 relationship with the fact? The idea is to have small dimensions. A degenerate dimension is an identifier used in the source that is useful for grouping fact rows. If you don't have any other Occurrence attributes to support a dimension and you want to query the table using the Occurrence ID, then yes, put it in the fact table.

Yes,

you are correct. There will be a one-to-one relationship between the Feature dimension and the fact table. I was just thinking that because there are 4 or 5 attributes related to the feature, it would be better to put those attributes in their own dimension. What is considered best practice?

Also, each occurrence can have many features. If I'm trying to keep the grain of this Claims fact table at the feature level, does it make sense to include the Occurrence Number at all? Occurrence can have 1 or many features for a given claim. Most of the keys on this table are at the Occurrence level (Loss_Date, Notify_Date, Claimant, Employee, CAT_ID, etc...).

thanks for your help.

Best practice is to not have one-to-one relationships between the fact and dimension. Most DBMS' croak when joining to large tables together. I would keep occurrence as a DD.

Great,

how about the feature number? Keep that as a degenerate dimension as well or should i not include the features as part of this fact table? Remember, Occurrences can have many features for any given claim.

thanks