Insurance Poicy Dimension
3 posters
Page 1 of 1
Insurance Poicy Dimension
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
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
Re: Insurance Poicy Dimension
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.
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
Re: Insurance Poicy Dimension
You should try to break up the customer dimension into smaller dimensions that are not 1:1 with the fact table.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Similar topics
» bridge table and junk dimension on customer dimension (bank/credit union)
» Tip #136 Adding a Mini-Dimension to a Bridge Table: What if you can't split the dimension?
» How to handle a Type I or II dimension with a snowflaked customer sub dimension (kimball book page 337, 338)
» Modeling an Employee Dimension to a Fact which has two columns relating to the Dimension
» Using the Date Dimension for Summary Fact Tables or new specialized Month Dimension?
» Tip #136 Adding a Mini-Dimension to a Bridge Table: What if you can't split the dimension?
» How to handle a Type I or II dimension with a snowflaked customer sub dimension (kimball book page 337, 338)
» Modeling an Employee Dimension to a Fact which has two columns relating to the Dimension
» Using the Date Dimension for Summary Fact Tables or new specialized Month Dimension?
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|