Monthly Claims Snapshot

View previous topic View next topic Go down

Monthly Claims Snapshot

Post  sgrover3 on Thu May 05, 2011 10:40 am

I am designing a Monthly Claims Snapshot dimensional model for auto insurance company. I have a measure called ReserveAmount (Insurance company reserved the amount for claim). One of the business questions mentions that they want to be able to analyze the timing, amount, and frequency of reserve amount changes. I know this can be handled creating a seperate Accumulating Snapshot (I think). My question is, Can I replace the ReserveAmount measure with 2 measures:
1. Original Reserve Amount
2. Current Reserve Amount
So, when the claim opens, both amounts will have the same value but over time only the current reserve amount value will change and the original will stay as-is. I want to highlight that this is a Monthly Snapshot fact table.
I believe the timing and frequency can be handled by the Date of Loss Key and comparing the 2 reserve amount measures.
Please provide any suggestions or improvements?

Thanks in Advance!

sgrover3

Posts : 8
Join date : 2011-04-14

View user profile

Back to top Go down

Re: Monthly Claims Snapshot

Post  ngalemmo on Thu May 05, 2011 11:53 am

Generally speaking, when magnitude of change is a critical requirement, a transactional fact table is superior to an accumulating snapshot. In a transactional fact you would store the net change from the previous value, giving you a metric that allows you to easily calculate the change for any time period. You could also have a semi-additive 'current balance' measure to simplify current state reporting.

As for your proposed solution, storing the original amount is not a bad thing, but it doesn't help resolving the queries you are trying to address. It only gives you the change from the original reserve, not change from the last reserve adjustment.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Monthly Claims Snapshot

Post  datamodeller on Thu May 05, 2011 12:04 pm

If there is a requirement to track every change then recon it would be better to modify the grain of the fact table and capture all the reserve amounts. The delta between the two resever amounts can either be computed in the presentation layer or it will also be good to compute successive delta and store it against the grain of the fact as a non-additive measure.

datamodeller

Posts : 9
Join date : 2010-07-25

View user profile

Back to top Go down

Re: Monthly Claims Snapshot

Post  sgrover3 on Thu May 05, 2011 12:32 pm

Great, thanks. That answers my question.
I have another question. I am thinking of adding the Salvage or Salvation Amount in the Claims Monthly Snapshot Fact table. Have you ever worked on a Claims Snapshot where you stored the Salvation Amount there. It is the amount that an Insurance company gets when the automobile is completed destroyed. I am thinking this (Salvation) doesn't happen till much later time as opposed to Claims Paid Amount.
Would it belong in the same snapshot fact table as ReserveAmount and CliamPaidAmount or Do I need to design a new Snapshot to store just the Salvation Amount?

Thanks!

sgrover3

Posts : 8
Join date : 2011-04-14

View user profile

Back to top Go down

Re: Monthly Claims Snapshot

Post  Dave Jermy on Fri May 06, 2011 8:48 am

One thing insurance companies often want an answer to is quality of reserving. Essentially, they want to know how good they are at estimating their indemnity when the claim first comes in. Because they have to set aside money to cover it, the ideal would be to set aside just enough to cover the claim. So, they may want to ask questions like "how many claims have a movement of +/- 25% from the initial reserve?" That would be easier to analyse if you store the original reserve separately.

Dave Jermy

Posts : 33
Join date : 2011-03-24
Location : London, UK

View user profile

Back to top Go down

Re: Monthly Claims Snapshot

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