Insurance Policy Snapshot

[scottcabral1979]

Hi,

I have a question about modeling a fact table representing Insurance policies.

We get a lot of requests for "How many policies were effective in 2011?" or "How Many Policies were Cancelled in 2012?".

My guess would be an accumulating snapshot, but how do I model policies that have had 4 terms? Do I just have 4 separate rows in the snapshot table for each year the policy was active?

thanks

scott

 

[blom0344]

I would suggest using the start and end date of the policy and a seperate table containing all years and project the data using a theta-join, like (just an example):

SELECT YT.YEAR,P.POLICY,P.STARTDATE,P.ENDDATE FROM POLICIES P
,YEAR_TABLE YT
WHERE YT.YEAR BETWEEN YEAR(P.STARTDATE) AND YEAR(ENDDATE)

You may need to add some logic to capture different scenario's (depending on your business logic)

Don't see the need to physically build a fact to answer the type of questions you are being asked.

Ties Blom