Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

multi level fact table Expand / Collapse
Author
Message
Posted Monday, October 22, 2012 8:02 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 4, 2016 7:29 AM
Points: 40, Visits: 569
Hi,

I am trying to model an insurance claims process that contains 2 levels.

Each claim has a main occurrence number. This is the main ID for a claim.

Each occurrence number has 1 or many features. Features are what hold the measures or amounts for each claim transaction.

Occurrences have the following attributes:

-Report Date

-Loss Date

-Employee

-Insured Name

-Line of Business

-State

Features have the following Attributes:

-Peril

-Close Date

-Feature Type

-Cat Event

Features also contain the measures for each claim:

-Loss Paid Amt

-Loss Reserve Amt

-Expense Paid Amt

-Expense Reserve amt

What I would like to do is be able to build a dimensional model that will allow me to report claims at the occurrence level with aggregated measures for each claim, and also allow me to drill down on each occurrence to get to the feature level data.

Is it better to try and build 2 separate fact tables at each level or is it better to have both the occurrrence and feature data in 1 fact table?

thanks for your help.
Post #1375805
Posted Tuesday, October 23, 2012 8:52 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 2:46 PM
Points: 635, Visits: 4,411
I would start with one fact table at the highest level of granularity (features in your case). It will be easy enough to create a summarized fact table at the claim level if there is a compelling reason to so in the future.


Post #1376107
Posted Tuesday, October 23, 2012 10:49 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 4, 2016 7:29 AM
Points: 40, Visits: 569
Thanks,

that was the path i was thinking about taking, just wanted another point of view :)
Post #1376172
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse