Dimension Attributes

[scabral]

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

[chade25]

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

[ngalemmo]

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.

[scabral]

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

[Sponsored content]