Aggregated fact tables

I work for a homeowner's insurance company and I've built a Claims Transaction fact table recently to store each claim transaction that occurs in the source system. Some of the dimensions include Loss Date, Insured Name, Loss Type and the measures include the actual amount paid or reserved for each claim transaction (Loss Payment Amt, Loss Reserve Amt, Expense Reserve Amt, Expense Payment Amt).

The source claims are stored in a flat table and contain 2 levels. Each claim has 1 Occurrence Number. Each Occurrence Number within a claim can have 1 or many features associated with it. I built the fact table at the claim feature level with an amount field for each measure. I also have the Feature Number and Occurrence Number as Degenerate Dimensions.

Before I build the SSAS cube, i was wondering if it would make sense to create another fact table at the claim occurence level and roll up the measure amounts to that level? Or is it ok to have SSAS aggregate the cube for me to the occurrence level? I want to be able to show the total amounts for each occurrence and also be able to drill down to the feature level to see the underlying details of each claim.

thanks
Scott

Hi Scott,

It sounds to me like you should construct a "Claim Feature" dimension, with a Claim level (Occurence Number) and then a Feature Level. Dimension logical key is maybe Occurence and Feature Number? As your model matures you can hang any extra dimension attributes on that dimension.

I prefer to do this sort of work back in the ETL layer resulting in a new Dimension table. Views or DSVs are tempting short-cuts, but they tend to make your cube more complex to debug, test and maintain.

You can then ditch your degenerate dimensions. In your new SSAS dimension you can build a 2-level hierarchy to support your drill-down - this will work nicely in your client tools and should perform well.

Good luck!
Mike

Hi Mike,

thanks for the input.

So if I create a dimension that holds the "Occurrence" and "Feature" attributes, what would go into the fact table, just the measures with a foreign key to the "Claim Feature" dimension?

All dimensions are either related to the Occurrence (loss date, employee, Line of Business, etc..) or the Feature (close date, Peril, coverage, etc...). If i design it this way, I won't have any dimensions left for the fact table, they will all be in the dimension table. Does that sound correct or am I missing something with your design?

thanks
Scott

Hi Scott,

I'd probably keep the independant dimensions. Each Conformed dimension can have other uses as your model matures - Date Dimension is the classic example and you already mentioned two dates.

Mike