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:
-Line of Business
Features have the following Attributes:
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.