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


Transactional Fact Table Design

Transactional Fact Table Design

Transactional Fact Table Design


I am trying to build a Claims Transaction Fact table for a homeowner's insurance company.

My claims transactions source table is setup like this:

Each Claim entered has an Occurrence.

Each Occurrence can have 1 or many Features.

Each Feature has 1 or more transactions.

So for example, Claim Occurrence 9999 can have Feature 123 with 2 transactions, 1st transaction is a loss payment of $100 and second transaction is loss reserve of $100. Feature 456 has 2 transactions as well, first being an expense reserve of $500 and second being $300 expense payment.

Each transaction has a Transaction Date/Time as well as a Transaction type (Loss payment, Loss Reserve, Expense Payment, etc...) and a Transaction Amt.

Each Feature has a Close Date.

Each Occurrence has a Loss Date and a Notify Date.

Given this setup, what is the best design on building a Claims Transaction Fact table? Can I have the Occurrence, Feature, and Transaction data all in teh table, or do I need to build the table just at the Transaction level without the Feature and Occurrence numbers?


RE: Transactional Fact Table Design

As I stated in a previous reply, granularity is the key to flexibility. Feature is your lowest common denominator. Claim is an attribute of the feature. If you need a claim summary table, it can be built from the Feature fact table. Assuming such a summary table is needed for the reasons I stated in the other post.....performance or specific reporting or analysis needs. And there may be other reasons.....

But remember to go for granularity. The smallest piece for both facts AND dimensions. For instance, your Geography dimension should (probably) be based on zip code, from which you can roll up into city, metro area, county, state, etc. Your time dimension should probably be day or maybe even Date/Time. You can always roll up into something larger, but delving into the details is impossible if you don't have the granularity.

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!

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