Insurance Poicy Dimension

View previous topic View next topic Go down

Insurance Poicy Dimension

Post  tim_goodsell on Tue Oct 05, 2010 6:30 pm

Hello

In my data warehouse I am intending to have a SCD Type 2 dimension that will contain info concerning a customers insurance policy (Death and Total Permananet Disablity) and a monthly snapshot fact table that contains the status of the insurance policy at the end of each month. This snapshot table will contain attributes concerning whether the policy is new or has totally expired etc

A customers insurance poicy can change over time. This dimension will have

Customer Key
Number of Standard Units
Number of Additional Units
Effective Date
Expiry Date
.. and a few other attributes

The customer key is the foreign key of the customer dimension (type 1), 1:1 relationship between customer and insurance policy) so I am snowflaking, is this ok or is there a better way ?.

Regards

Tim

tim_goodsell

Posts : 49
Join date : 2010-09-21

View user profile

Back to top Go down

Re: Insurance Poicy Dimension

Post  bigjonroberts on Thu Oct 14, 2010 12:23 pm

Why not put the customer key in the actual fact tables? Why do you need a snowflake? You could put customer key in the dimension just to use for lookups for inserting the customer key into fact tables. In this case, you would only use it for lookups and would probably expose the policy dimension to your end users in a view that omitted the customer key.

Snow-flaking can make your cardinality problems in fact tables much more severe. Why would you want the database engine to have to join two tables every time you want to include customer information instead of just one? Also, most modern database engines can incorporate star schema as part of their query optimization. Every time that you put a snow-flake in your design, you complicate the query optimization and potentially reduce performance.

bigjonroberts

Posts : 6
Join date : 2009-07-09

View user profile

Back to top Go down

Re: Insurance Poicy Dimension

Post  BoxesAndLines on Thu Oct 14, 2010 1:21 pm

You should try to break up the customer dimension into smaller dimensions that are not 1:1 with the fact table.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Insurance Poicy Dimension

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum