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

Dimension Attributes

3 posters

Go down

Dimension Attributes Empty Dimension Attributes

Post  scabral 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

Back to top Go down

Dimension Attributes Empty Re: Dimension Attributes

Post  chade25 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 : 42
Location : Oregon

Back to top Go down

Dimension Attributes Empty Re: Dimension Attributes

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

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

http://aginity.com

Back to top Go down

Dimension Attributes Empty Re: Dimension Attributes

Post  scabral 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

Back to top Go down

Dimension Attributes Empty Re: Dimension Attributes

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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