Schema Design Question

View previous topic View next topic Go down

Schema Design Question

Post  scabral on Wed May 01, 2013 11:04 pm


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.



Posts : 58
Join date : 2012-05-02

View user profile

Back to top Go down

Re: Schema Design Question

Post  BoxesAndLines on Thu May 02, 2013 1:52 pm

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.

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

View user profile

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