Schema Design Question
2 posters
Page 1 of 1
Schema Design Question
Hi,
I'm designing a monthly periodic snapshot fact table that will have every homeowner's insurance policy that is active at the end of each month.
Each policy has many attributes including policy number, effective date, expiration date, agent, territory, distance from coast, number of family, usage type, New or Renewal Code, and others. The measures in the fact table will include written premium, earned premium, coverage A/B/C/D amounts, deductible amount, and total exposure.
My question is should I have the Policy Number as a Degenerate Dimension in the fact table and create dimensions for all of the policy fields listed above, or should I create a policy dimension that will store certain attributes that may not need their own dimension?
I'm not sure what the best approach would be given the grain of the fact table. Looking for some advice.
thanks
I'm designing a monthly periodic snapshot fact table that will have every homeowner's insurance policy that is active at the end of each month.
Each policy has many attributes including policy number, effective date, expiration date, agent, territory, distance from coast, number of family, usage type, New or Renewal Code, and others. The measures in the fact table will include written premium, earned premium, coverage A/B/C/D amounts, deductible amount, and total exposure.
My question is should I have the Policy Number as a Degenerate Dimension in the fact table and create dimensions for all of the policy fields listed above, or should I create a policy dimension that will store certain attributes that may not need their own dimension?
I'm not sure what the best approach would be given the grain of the fact table. Looking for some advice.
thanks
scabral- Posts : 58
Join date : 2012-05-02
Re: Schema Design Question
My goal would be to avoid one-to-one relationships between a fact table and a dimension. Part of that would include putting the policy identifier as a degenerate dimension on the fact table. This goal is based on large volumes of data. Most DB's have trouble joining two large tables.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Similar topics
» Advice on schema. Is the table in question dimensional or factual?
» Question on Dimension schema change
» Question on symmetry in Star schema
» Simple Star schema question
» FACT Design Question - How to design FACT Table when computation is required on the degenerate dimension?
» Question on Dimension schema change
» Question on symmetry in Star schema
» Simple Star schema question
» FACT Design Question - How to design FACT Table when computation is required on the degenerate dimension?
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum