Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Schema Design Question

2 posters

Go down

Schema Design Question Empty Schema Design Question

Post  scabral Wed May 01, 2013 11:04 pm

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

scabral

Posts : 58
Join date : 2012-05-02

Back to top Go down

Schema Design Question Empty Re: Schema Design Question

Post  BoxesAndLines 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.
BoxesAndLines
BoxesAndLines

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

Back to top Go down

Back to top

- Similar topics

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