Insurance product

In our insurance company we are adding a dimensional model on top of our existing 3NF model.
This is not too easy... and we hope someone can provide som thoughts on how to model this.
Product structure goes like this:
Policy<-Covered Item<-Coverage

Is it ok in the (Core) Coverage dimension to include key from Policy and Covered item ?
Is it ok in the (Core) Covered Item dimension to include key from Policy ?

while we are at it: Is it sensible in Insurance to have a separate address dimension ?
and is it wise to have a narrow core party dimension and several physical dimensions: insurer, insured, claimant, 3'd party and so on ?

Regards Ole

Hi

The insurance companies I have worked with usually have a wide policy dimension that is actually at coverage level. The dimension often includes all thinks about the policy such as broker, insured details (name, address etc), risk location, risk item details, coverage info.

If you want to model separate dimensions for things like broker and insured you need to consider whether these can be conformed and used with other facts or measure groups, if not then there is no need for a separate dimension table. However, I normally model broker as a conformed dimension as it often appears on different fact tables and is easily conformed in insurance. I probably wouldn't have a separate address dimension as this is probably just an attribute(s) of the insured or 3rd party.

Hi and thx for reply !

Wouldn't a "wide policy dimension that is actually at coverage level" be both wide AND deep as there are so many attributes changing ?

Benefits of separate address dimension might be:
Address records can be reused
Segmenting data based on stability
Facilitating customer integration
Handling multiple addresses for a customer


Ole

Yes, it is likely to be deep also. That is not so much to do with the changes but the fact that there is more than one coverage per policy. Frequency of updates will be a factor if it is type 2.

There are many ways to model it, however, in your data warehouse it should always be driven by your user / reporting requirements. We often have a geography dimension that is conformed (reused) across facts and this is usually high level and contains country / region information, but not street level. Geography is often used as a role playing dimension for domicile, insured location or region.