Insurance Poicy Dimension

[tim_goodsell]

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

[bigjonroberts]

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.

[BoxesAndLines]

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