Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
when to create a new dimension?

Întrebare when to create a new dimension?

  • Monday, March 11, 2013 9:03 PM
     
     
    Sign In to Vote
    0

    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?

    thanks

     

All Replies

  • Monday, March 11, 2013 9:52 PM
     
     
    Sign In to Vote
    0

    >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
     
     
    Sign In to Vote
    0

    thanks

    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.