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


multi leve fact table

multi leve fact table

multi leve fact table


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


-Insured Name

-Line of Business


Features have the following Attributes:


-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.

RE: multi leve fact table

Granularity, granularity. I'm going to write a FAQ on granularity. Your facttable is at the lowest level. Claim is an attribute and/or dimension. If you need to summarize data at the Claim level with a separate table/cube/datamart, then do so. Otherwise, if your system can handle the query and analysis load at the lowest level of granularity, then don't build the additional structures. Often in DW, we trade disk (which is cheap) and off-hours processing time for the time needed to produce reports and other products, or to save time for the analysts, who are generally "not cheap". HTH.

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