Monday, March 11, 2013 9:03 PM
If I have an Insurance Policy Dimension which has attributes like Effective, Expiration, Policy Num, but the policy also has some other attributes like Status (Issued, Cancelled, Quote) and Category(New or Renewal) and Distance to Coast(1 mile, 2 miles, 3 miles, > 3miles).
Does it make sense to add these attributes to the Policy dimension or create separate dimensions for each? I can see some of these being used on multilpe fact tables (Premium snapshot, policy lifecycle), so not sure if it is better to create new dimensions. What is the benefit/drawback on doing one or the other?
Monday, March 11, 2013 9:52 PM
>Does it make sense to add these attributes to the Policy dimension
I vote for this option. That is the logical place for them.
Kalman Toth Database & OLAP Architect
Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012
Tuesday, March 12, 2013 1:24 AM
i'm also thinking this will have to be treated as a type 2 dimension. For example, Policy Status (New, Renewal) will always be new in the first year of the policy and then change to renewal on years 2 and greater. So i'm thinking that if a policy was active for 2 years, then there will be 2 records in the policy dimension, one with New and the other with Renewal (following type 2 change with new surrogate key but same policy num).
let me know if this design sounds right.