Monthly Claims Snapshot
4 posters
Page 1 of 1
Monthly Claims Snapshot
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!
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
Re: Monthly Claims Snapshot
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.
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.
Re: Monthly Claims Snapshot
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
Re: Monthly Claims Snapshot
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!
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
Re: Monthly Claims Snapshot
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
Similar topics
» Thoughts on monthly snapshot
» Updating Monthly snapshot for prior months...
» Monthly snapshot fiscal calendar issue
» Monthly Periodic Snapshot Table using SCD Type1 Dimension tables - Design Question.
» Insurance Claims
» Updating Monthly snapshot for prior months...
» Monthly snapshot fiscal calendar issue
» Monthly Periodic Snapshot Table using SCD Type1 Dimension tables - Design Question.
» Insurance Claims
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum