Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Insurance Claims Fact Table Question

Insurance Claims Fact Table Question

Insurance Claims Fact Table Question


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?


RE: Insurance Claims Fact Table Question


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

RE: Insurance Claims Fact Table Question

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close