Insurance Claims Model Question

Hi,

I currently work for a property and casualty insurance company where we only deal with homeowner's insurance.

I would like to build a claims transaction fact table along with all the dimensions used to describe the transactions.

Being fairly new to Dimensional modeling, i have a few questions about how i should design the fact table.

Our claims source data has all of the basic insurance claim information including Insurer Name, Agent, Employee, Claim Status (Open, Closed, Re-Opened), Report Date, Close Date, Transaction Date, Loss Type (Property or Liability), Peril, Sub Peril, Payee, Transaction Type, and Claim Amount.

The piece that I am having trouble with is how to categorize the Claim Amounts by Transaction Type. We have both Losses and Expenses. Each Loss is either a payment or a reserve. Each Expense is also either a payment or reserve.

Should i create a Transaction Type Dimension to describe either Loss Payment, Loss Reserve, Expense Payment, Expense Reserve, and have the Claim Amount as a single fact Or should i create separate fact amounts for each type of payment/reserve such as a field for Loss Payment, Loss Reserve, Expense Payment or Expense Reserve and have the Transaction ID as a degenerate dimension?

Thanks
Scott

Hi scabral

If each transaction's type can only ever be one of the four, then I would store everything in a single fact and create a dimension that contains the four types of transactions.

Something like:

DimTransactionType
TransactionTypeKey TransactionType TransactionSubType
1 Loss Payment
2 Loss Reserve
3 Expense Payment
4 Expense Reserve

FactClaim
TransactionTypeKey ClaimAmount
1 $100.40 <- claim is of type Loss - Payment
3 $232.30 <- claim is of type Expense - Payment

Hope this helps.

Thank you for the response.

This seems to be the way that Kimball approaches his Claims Transaction design in the Data Warehouse Toolkit (chapter on Insurance).

He basically has the Claim Amt as the fact and transaction type as a dimension.

I also saw another example of a claims model where they had each of the different amounts as separate facts in the table.

I always like to get another opinion before developing. Thanks again for your assistance!

Scott

Break the parts into their separate buckets. You can also add metrics that combine the parts into useful subtotals, e.g. net claim amount. I don't see a need for the degenerate dimension since the individual buckets tell you what you need to know.

Hi BoxesandLines,

Are you saying that it is a better method to have separate fact amounts for each type of transaction (Loss Payment Amt, Loss Reserve Amt, Expense Payment Amt, Expense Reserve Amt)?

If so, how do you see that as being a better approach than what min.emerg is stating where we have on fact measure called Claim Amt and a Transaction Type Id which has it's own dimension that stores the Type of Transaction (Loss Payment Amt, Loss Reserve Amt, Expense Payment Amt, Expense Reserve Amt)?

Just trying to get a sense of why one method might be better than the other.

thanks
Scott

Because it is easier to query. All amounts are on one row.

BoxesandLines, I still feel that using a single fact would be easier. If you're wanting to total/sum the total claim amount this year for only a subset of the four transaction types, you would have to combine values from multiple facts, which could be complicated when slicing.

I would have four separate measures in the fact for each transaction type if each transaction amount had four components - one for each transaction type. But it seems that the transaction types are mutually exclusive for each transaction.

Thoughts?

Maybe I wasn't clear enough in my post. Yes, you only want one fact table with all the buckets. It's the same model for health care claims.

Yes,

min.emerg is correct in stating that each transaction type is mutual exclusive for each transaction. The source table will have a separate transaction row for each type of transaction that is made against the claim. There will never be a transaction that has more than 1 transaction type.

I hope that makes sense, but i think BoxesAndLines may have not known the exact way the transactions were stored in the source table.

sorry for not explaining it better.

BoxesAndLines,

So in your model, each transaction will always have the 4 different claim amounts, but only 1 will have data and the others will be 0.

The other method where we only use 1 claim amount will always have an amount populated with the corresponding Transaction Type ID.

I guess my question is does it seem to be redundant to always have every claim amount on every transaction when we know only 1 can ever have an amount populated?

I would look to consolidate all the claim amounts on a single row.