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

Insurance Policy Accumulating Snapshot

4 posters

Go down

Insurance Policy Accumulating Snapshot Empty Insurance Policy Accumulating Snapshot

Post  scabral Mon Feb 11, 2013 4:44 pm

Hi,

I have a question about modeling an accumulating snapshot fact table for an insurance policy.

I know that the table will have the main policy dates like effective date, quote date, expiration date, etc...

My question is that I see most of these tables have 1 record for each policy. In our model, a policy can have multiple "terms" throughout it's life cycle.

For example, Policy 1234 was originally effective on 1-1-2010 and expired on 1-31-2010. This is considered the 1st term of the policy. The next year, or 2nd term, the policy gets a new effective (1-1-2011) and expiration (1-31-2011) dates and so on for each consecutive year that the policy is active.

How would I model the accumulating table so that I can report on each year's data or is this type of table only supposed to contain current information?

thanks
scott

scabral

Posts : 58
Join date : 2012-05-02

Back to top Go down

Insurance Policy Accumulating Snapshot Empty Re: Insurance Policy Accumulating Snapshot

Post  Jeff Smith Mon Feb 11, 2013 4:54 pm

An Acculmulating Snap Shot means that at regular intervals, a snapshot is taken and added to the fact table. If the interval is monthly, then after 1 year, the table would have 12 months of data.

I would load the most current record. For each month, the policy would be loaded once. If the policy showed up with an effective date in the future, I would load the previous version of the policy (I wouldn't load any future versions of the policy).

The only issue would be how you would want to treat retroactive changes. Sometimes a change in a policy is made with the effective date occurring several months earlier. Some people what the historical data to be updated, others want the historical data left unchanged (they want to be able to state the count of policies as they knew them on a particular date - helps with audits).

Jeff Smith

Posts : 471
Join date : 2009-02-03

Back to top Go down

Insurance Policy Accumulating Snapshot Empty Re: Insurance Policy Accumulating Snapshot

Post  BoxesAndLines Mon Feb 11, 2013 5:25 pm

Actually, an accumulating snapshot is a fact table where you update the fact row with the latest date information. I'm not sure it makes sense to do a Policy accumulating snapshot to measure policy effective and end dates. These dates can be simply handled through the dimension.
BoxesAndLines
BoxesAndLines

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

Back to top Go down

Insurance Policy Accumulating Snapshot Empty Re: Insurance Policy Accumulating Snapshot

Post  scabral Mon Feb 11, 2013 5:34 pm

BoxesAndLines,

We currently get many requests like "How many policies were effective in Dec 2011?" or "How many policies were cancelled in Jan 2010" or "How many quotes did we get in Aug 2012"?

My guess is the Policy Dimension would have the latest Effective, Expiration, Cancelled dates. I was thinking a snapshot table would allow me to store all of the dates for each year the policy was active, however I wasn't sure how to model that using star schema approach.

Scott


scabral

Posts : 58
Join date : 2012-05-02

Back to top Go down

Insurance Policy Accumulating Snapshot Empty Re: Insurance Policy Accumulating Snapshot

Post  BoxesAndLines Mon Feb 11, 2013 9:35 pm

Got it. You can do it with a daily snapshot fact table. It does chew up a lot of space, but WTH, it is a data warehouse. :-)

For cancels, you could create a disconnect fact table. When a policy is canceled, write a row into the cancel transaction fact table.

Kimball also has a strategy of adding effective and end dates to facts. This is a perfect example of a good use for it. Unfortunately, I couldn't find the article link where he (or she since Margy may have written it) in the archives.


Last edited by BoxesAndLines on Mon Feb 11, 2013 9:36 pm; edited 1 time in total (Reason for editing : bad English)
BoxesAndLines
BoxesAndLines

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

Back to top Go down

Insurance Policy Accumulating Snapshot Empty Re: Insurance Policy Accumulating Snapshot

Post  scabral Mon Feb 11, 2013 10:21 pm

So does it make sense to have a Policy dimension using this approach, or should I have the Policy Number as a DD in the fact table?

scabral

Posts : 58
Join date : 2012-05-02

Back to top Go down

Insurance Policy Accumulating Snapshot Empty Re: Insurance Policy Accumulating Snapshot

Post  Jeff Smith Mon Feb 11, 2013 11:48 pm

My bad. I got confused because an accumulating fact table isn't appropriate for the policy. A periodic snapshot is the appropriate fact table. An accumulating fact table would be appropriate for the processing of a claim.

Jeff Smith

Posts : 471
Join date : 2009-02-03

Back to top Go down

Insurance Policy Accumulating Snapshot Empty Re: Insurance Policy Accumulating Snapshot

Post  BoxesAndLines Tue Feb 12, 2013 10:57 am

scabral wrote:So does it make sense to have a Policy dimension using this approach, or should I have the Policy Number as a DD in the fact table?

No and yes. As a general rule, you do not want 1-1 relationships between a fact and dimension. You will need to break the policy attributes into smaller dimensions.
BoxesAndLines
BoxesAndLines

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

Back to top Go down

Insurance Policy Accumulating Snapshot Empty Re: Insurance Policy Accumulating Snapshot

Post  Nancy Rinn Tue Feb 12, 2013 12:21 pm

I believe the article referred to is Design Tip #145: http://www.kimballgroup.com/2012/05/01/design-tip-145-time-stamping-accumulating-snapshot-fact-tables/.

Nancy Rinn

Posts : 23
Join date : 2008-08-18
Location : MN

http://www.kimballgroup.com

Back to top Go down

Insurance Policy Accumulating Snapshot Empty Re: Insurance Policy Accumulating Snapshot

Post  scabral Sat Feb 16, 2013 10:08 pm

Sorry, but I'm having a real difficult time designing this model.

If I create a daily snapshot table, where do I put the Effective, Expiration, Cancellation, and Quote dates for each policy? Should they remain in the fact table or keep them in a Policy dimension table as attributes? Again, each policy can have multiple terms.

If I build an Accumulating snapshot table, how do I deal with multiple rows for each Policy? Each policy can have multiple rows based on how long they have been a customer. For example, a Policy could have been quoted on 1-1-2010 and had a policy term effective from 1-1-2010 to 1-1-2011 and then another policy term from 1-1-2012 to 1-1-2013 and yet another policy term from 1-1-2013 to 1-1-2014. How does accumulating snapshot handle these situations?

In the end, i want to be able to answer questions like "How many policies were effective from 1-1-2012 through 3-31-2012" or "How many policies were cancelled in 2011" or "how many polices were quoted in 2013". Not sure what the best way is to handle this.

thanks for your help.
Scott

scabral

Posts : 58
Join date : 2012-05-02

Back to top Go down

Insurance Policy Accumulating Snapshot Empty Re: Insurance Policy Accumulating Snapshot

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