Transactional Fact Table Design

[scottcabral1979]

Hi,

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?

thanks
Scott

 

[johnherman]

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