Dimension Attributes

View previous topic View next topic Go down

Dimension Attributes

Post  scabral on Fri Feb 22, 2013 4:55 pm

I'm building a dimensional model for homeowner's insurance policies and many of the fields that describe the policy have 2-4 possible values that probably will never change:

Policy Category (New, Renewal)
Product Line (Homeowner, Dwelling Fire)
Policy Status (Issued, Cancelled, Quote)
Number of Family (1, 2, 3, 4, 5, 6)
Policy TIER (Platinum, Platinum Plus, Preferred, Ultra)

Does it make sense to create separate dimensions for each of these fields to store the values and have FK's in the fact table or is it more efficient to store these values in a Policy Dimension along with the Policy Number?

Also, what if the difference and/or benefit of having these fields as either FK's in the fact table or as attributes in the Policy Dimension table? Is there something that I can do or can't do between one or the other?

thanks
Scott

scabral

Posts : 58
Join date : 2012-05-02

View user profile

Back to top Go down

Re: Dimension Attributes

Post  chade25 on Mon Feb 25, 2013 11:54 pm

Your fact table is the business event you are measuring, and the dimensions describe that event, but you have not described that event

chade25

Posts : 29
Join date : 2012-04-12
Age : 37
Location : Oregon

View user profile

Back to top Go down

Re: Dimension Attributes

Post  ngalemmo on Tue Feb 26, 2013 4:09 am

The better choice would be one or two junk dimensions. Given the cardinality of the various values, one table would suffice. If you also need the policy number, just store it as a degenerate dimension value in the fact.

It may also make sense to store family size in the fact as a degenerate dimension/measure, rather than in a dimension table.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Dimension Attributes

Post  scabral on Tue Feb 26, 2013 11:01 am

Hi chade25,

The fact table in this case would be a daily snapshot premium table. The table would have a list of policies that are "Active" each day along with measures such as Written Premium, Earned Premium, Total Exposure Cost, etc...

I'm also thinking of creating a Monthly Snapshot from this table to store the Month End values of Written Premium and Earned Premium along with Commissions paid on each policy at month end.

The Policy Dimension would probably be re-used in the future for other business events, So i want to make sure it is setup correctly. I was also thinking of making it a Type 2 dimension to track changes in Status (New, Renewal) and other attributes that may change slowly throughout the year.

I'm just stuck on deciding whether it is better to store the attributes in the policy dimension as type 2 attributes, or store them in the fact table as FK's to separate dimension tables.

thanks again.

Scott

scabral

Posts : 58
Join date : 2012-05-02

View user profile

Back to top Go down

Re: Dimension Attributes

Post  Sponsored content


Sponsored content


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