Anmelden
 
HomeLibraryDownloadsSupportWebcasts & EventsCommunityForenBlogs
Ressourcen für IT-Professionals >
> 
> 
Insurance Policy Snapshot

คำตอบ Insurance Policy Snapshot

  • Dienstag, 12. Februar 2013 02:29
     
     
    Anmelden
    0

    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 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

     

Alle Antworten

  • Mittwoch, 13. Februar 2013 23:26
     
     
    Anmelden
    0

    Hi,

    it's all depends upon the grain of the data you have. if your source contains the day level data then you can easily calculate the year level information but reverse is difficiult to achieve.

    so please have a look into your source and analyse the data and then bring this into your fact table.

    i hope this helps you.

    Regards,

    Zaim Raza.

     
  • Dienstag, 19. Februar 2013 22:05
     
     
    Anmelden
    0

    Yes, the snapshot would work.

    Thomas

    TheSmilingDBA Thomas LeBlanc MCITP 2008 DBA

     
  • Mittwoch, 20. Februar 2013 01:54
     
     Beantwortet
    Anmelden
    0

    Hi Scott -

    Accumulating snapshot can provide a very flexible solution for your case.  You'll need the individual events captured in a separate, base transaction fact though.  Then you can use the transaction fact as the source for the accumulating snapshot and the various dates / milestones and status changes that get pivoted for a given policy.

    Suggest you check out Kimball's DW Toolkit and the Insurance chapter (15).  They actually touch on your specific policy accumulating snapshot design on p.315.  And have a Claims accumulating snapshot diagram for reference.

    Regarding your specific questions, here are my comments:

    We get a lot of requests for "How many policies were effective in 2011?"

    • accumulating snapshot is the best design for this type of analysis.  You can just filter for policies (fact rows) with something like policy effectiveDate <= 12/31/2011 and policy expirationDate or TerminationDate is >= 1/1/2011.

    or "How Many Policies were Cancelled in 2012?".

    • this could be answered directly from the transaction fact (assuming your capturing cancellation trxns) as well as from the accumulating snapshot assuming you capture a final status and cancellation date

    My guess would 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?

    • yes, you can have a row for each term and embed the "master" policy number in the accumulating snapshot to make it easy to tie the related records together.  And you could set the final status on the first 3 to renewed, to signify that a subsequent record is available.  This is similar to student enrollments where the same student can have multiple successful enrollments.  DW Toolkit covers that scenario as well if you need additional references and examples.

    Let me know if that helps.

    Brent Greenwood, MS, MCITP, CBIP
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com