Insurance Policy Dimension

View previous topic View next topic Go down

Insurance Policy Dimension

Post  scabral on Tue Feb 19, 2013 3:12 pm

I'm in the process of building a daily snapshot fact table for homeowner's insurance policies. One of the tricky parts about our systems is that we are in the process of migrating our policies from an older vendor to a new vendor which we have been using for new business in the past 2 years.

So what happens is that when a Policy gets migrated on renewal into the new system, it gets a new policy number. We do keep track of the previous policy number from the old system in a field called "InceptionPolicy".

For example, the Policy below has been 'Active' for 3 years, but it was migrated in the 3rd year. Here is what it looks like in a simplified version:

POLICY_NUMBER INCEPTION_DATE EFFECTIVE_DATE EXPIRATION_DATE STATUS WRITTEN_PREMIUM INCEPTION_POLICY
3H38033114 2011-01-18 00:00:00.000 2011-01-18 00:00:00.0002012-01-18 00:00:00.000 New 6503H38033114
3H38033114 2011-01-18 00:00:00.000 2012-01-18 00:00:00.000 2013-01-18 00:00:00.000Renewal 621 3H38033114
10349518 2011-01-18 00:00:00.000 2013-01-18 00:00:00.0002014-01-18 00:00:00.000 Renewal 643 3H38033114

So if i build a Policy Dimension to hold the Policy Number and Inception Policy Number, is it best to store it as a SCD type 2, with a new row for the new policy number? I know that we will be asked to produce counts of policies for each week or month from this fact table, so I need to be able to count the above policy as just 1 policy even though it changed policy numbers. With SCD type, i think it will treat this as 2 separate policies given that the surrogate key will change.

Do you think a Type 3 would be better to store the Original Policy in this case? Just trying to get some advice on how to design this given the metrics we will need to produce.

thanks
Scott

scabral

Posts : 58
Join date : 2012-05-02

View user profile

Back to top Go down

Insurance Policy Dimension

Post  Jeremyoos on Tue Feb 19, 2013 4:25 pm

Hi Scott

I don't know the first thing about the insurance industry, but it smells like in this case that you're on the right track with the Type 3 attribute. See this discussion by Margy Ross (Design Tip #152) about new SCD types (well, not new, but newly named) where she talks about a Type 3 column on a Type 2 Dimension that is overwritten as a Type 1, now known as a Type 6.

Jeremy

Jeremyoos

Posts : 4
Join date : 2009-02-05

View user profile

Back to top Go down

Re: Insurance Policy Dimension

Post  scabral on Tue Feb 19, 2013 5:58 pm

Thanks,

the more I thought about it, the more a type 3 made sense for my scenario.

I know that SCD type 3 is not the most used method, but it seems right for what i need to do.


scabral

Posts : 58
Join date : 2012-05-02

View user profile

Back to top Go down

Re: Insurance Policy Dimension

Post  BoxesAndLines on Tue Feb 19, 2013 10:45 pm

I think this is a perfect use of a type 3. Especially if you need to keep the old policy number around. If you don't need to keep the old policy number I would also look at creating a cross reference table to translate the old numbers to the new numbers. That way you can simply drop the cross reference when it is no longer needed.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Insurance Policy Dimension

Post  kpdw166 on Wed Feb 20, 2013 12:20 pm

Instead of having it as a type 3 attribute would you not just have a type 2 and close the old records down and create a new row for the changed policy. For example;

INS_IDSTART_DATEEND_DATEPOLICY_NUMBERINCEPTION_DATEEFFECTIVE_DATEEXPIRATION_DATESTATUSWRITTEN_PREMIUMINCEPTION_POLICY
123405-DEC-201210-JAN-20133H380331142011-01-182011-01-182012-01-18New6503H38033114
123510-JAN-201320-FEB-20133H380331142011-01-182012-01-182013-01-18Renewal6213H38033114
345620-FEB-201331-DEC-9999103495182011-01-182013-01-182014-01-18Renewal6433H38033114

Becuase you have the INCEPTION_POLICY field you can easily match back to the closed records.

kpdw166

Posts : 7
Join date : 2013-01-24

View user profile

Back to top Go down

Re: Insurance Policy Dimension

Post  scabral on Wed Feb 20, 2013 12:47 pm

kpdw166,

this raises another question that I had with this design.

In my daily snapshot table, I will need to get counts of policies that were effective or expired during different time periods. If I keep the policy dimension the way it's designed here, wouldn't the policy get double counted depending the time frame that is used? For example, since the policy gets a new surrogate key, won't it be counted twice in the fact table?

I was thinking of keeping the effective and expiration dates in the fact table and having the policy and inception policy in the Policy dimension table. This way the surrogate key will always be the same even after the policy gets migrated and gets a new number.

Am i thinking about this correctly?

scabral

Posts : 58
Join date : 2012-05-02

View user profile

Back to top Go down

Re: Insurance Policy Dimension

Post  kpdw166 on Thu Feb 21, 2013 10:23 am

Without understanding the situation fully, I can only make guesses but from looking at the data you supplied in your example, the incecption dates match across all records, so would you use this and only look at open records in your dimension (end_date=31/12/9999).

kpdw166

Posts : 7
Join date : 2013-01-24

View user profile

Back to top Go down

Re: Insurance Policy Dimension

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

I really think you are not designing this right, in my opinion. I think (assumption here) the policy is what you want to measure, and the dimensions are the things that describe the policy event, like person...because if the status say changes from renewal to something, you want to record that one the FACT table, not in a dimension.

chade25

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

View user profile

Back to top Go down

Re: Insurance Policy Dimension

Post  scabral on Tue Feb 26, 2013 1:53 pm

chade25,

So if the fact table was measuring written premium and earned premium for each policy on a daily basis (Daily Periodic Snapshot), are you saying that fields that describe the policy like Policy_Status (New, Renewal), Coastal_Band (<1 mile, 1-2 miles, >2 miles), Policy_Effective_Date, Policy_Expiration_Date, etc... should be stored in the fact table as FK's and linking to separate dimension tables (DimPolicy_Status, DimPolicy_Coastal_Band, DimDate)?

The Policy Status will only be New on the 1st year of the policy, after that it will be renewal. Effective and Expiration Dates will change each year on Renewal.

I'm just trying to figure out if it's best to keep the attributes in the Policy Dimension or place them in the fact table. What are the benefits for doing one over the other?

thanks
Scott


scabral

Posts : 58
Join date : 2012-05-02

View user profile

Back to top Go down

Re: Insurance Policy Dimension

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