Insurance Claims Fact Table Question

[scottcabral1979]

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

 

[rasETL]

Scott

We have set up two Lookup Tables
1. Payment Type - Loss, Expense, and Reserve (3 rows)
2. Patment Fact Type - Direct and Ceded (2 Rows)

Include these two as columns in the Payment Fact Table

 

[johnherman]

Well, I disagree. Reserves and Payments are different, and there may be a need to determine the variance between reserves and payments. Therefore, I suggest 2 fact tables and a relation/bridge table between the reserve and the payment. Note that depending on your system and business processes, there could be payments without a reserve and reserves which are not yet paid, or which are denied. It is up to you to determine if the denied claims should get a zero payment transaction. The relation/bridge table would contain only its own key along with the keys to the Reserve Fact Table and the Payment Fact Table. And any administrative columns you might need for managing the table and/or audit.


Having a separate Reserve table allows you to track changes in the reserved amounts, as they often change. Assuming that your business cares about that. And if they don't, there may come a time in the future that they will care. And if your design is not flexible enough to handle such changes, then you will need to go to great lengths of effort to achieve the business goal. Granularity of fact tables is critical to a flexible design. When in doubt, go for the lowest common denominator. You can always build summary tables combining the data if needed for performance or specific reporting or analysis needs.

====================================
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright