Insurance Policy Submission Cube
3 posters
Page 1 of 1
Insurance Policy Submission Cube
Hi,
I would like to build a fact table that tracks homeowner's insurance policy submissions. This table would measure policies that were quoted and/or issued.
The source data is stored by policy with a Quote Date and an Issue Date and a policy_status field. Every policy would have a quote date and if the policy was issued, it would have an issue date. Quoted policies that have not been issued have a policy_status field of 'quote'. Issued policies have a policy_status field of 'Issued'.
Dimensions would be State, Agent, Territory, Quote Date, Issue Date, etc...
I'm looking for some suggestions on the best way to store the data in a fact table. Should I just store a count of each quote and a count of each issued by date? I basically just need to get the numbers of quotes and the number of issues by any one of the dimensions.
thanks
Scott
I would like to build a fact table that tracks homeowner's insurance policy submissions. This table would measure policies that were quoted and/or issued.
The source data is stored by policy with a Quote Date and an Issue Date and a policy_status field. Every policy would have a quote date and if the policy was issued, it would have an issue date. Quoted policies that have not been issued have a policy_status field of 'quote'. Issued policies have a policy_status field of 'Issued'.
Dimensions would be State, Agent, Territory, Quote Date, Issue Date, etc...
I'm looking for some suggestions on the best way to store the data in a fact table. Should I just store a count of each quote and a count of each issued by date? I basically just need to get the numbers of quotes and the number of issues by any one of the dimensions.
thanks
Scott
scabral- Posts : 58
Join date : 2012-05-02
Re:Insurance Policy Submission Cube
Hi,
you could build an accumulating snapshot fact table where you will capture the quote and if a policy is issue then add the issue information.
If you need the count then you can capture the count in a summary table.
thanks
you could build an accumulating snapshot fact table where you will capture the quote and if a policy is issue then add the issue information.
If you need the count then you can capture the count in a summary table.
thanks
hkandpal- Posts : 113
Join date : 2010-08-16
Re: Insurance Policy Submission Cube
hkandpal,
I understand the accumulating snapshot part in order to create a record for each policy and fill in the quote dates and issued dates as they occur. But what do you mean when you say store the counts in a summary table? Are you talking about a separate table all together? How would it join to the accumulating snapshot? I would need to get counts for number of quotes for each year or counts of quotes that were issued, etc...
thanks
Scott
I understand the accumulating snapshot part in order to create a record for each policy and fill in the quote dates and issued dates as they occur. But what do you mean when you say store the counts in a summary table? Are you talking about a separate table all together? How would it join to the accumulating snapshot? I would need to get counts for number of quotes for each year or counts of quotes that were issued, etc...
thanks
Scott
scabral- Posts : 58
Join date : 2012-05-02
Re: Insurance Policy Submission Cube
Scott,
it is better to have the counts/sums stored in a seperate summary table or a materalized view or calcualate those number during run time and have the fact only contain the granular data which in you case is quote number.
If you send a refised quote dose it get a new number ?
thanks
it is better to have the counts/sums stored in a seperate summary table or a materalized view or calcualate those number during run time and have the fact only contain the granular data which in you case is quote number.
If you send a refised quote dose it get a new number ?
thanks
hkandpal- Posts : 113
Join date : 2010-08-16
Re: Insurance Policy Submission Cube
You lost me there. Why is it better? The whole premise is designed on summing granular data. Only aggregate when needed for performance.hkandpal wrote:Scott,
it is better to have the counts/sums stored in a seperate summary table or a materalized view or calcualate those number during run time and have the fact only contain the granular data which in you case is quote number.
If you send a refised quote dose it get a new number ?
thanks
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Re: Insurance Policy Submission Cube
The grain of the fact table will be one row per quote. If the policy is never issued, only the quote date will be filled in with a blank issue date. If the policy gets issued, then the issue date will be filled in at the time of issue. I also need to be able to count the number of quotes, number of issues, and then do a conversion rate on how many quotes actually become issued and be able to see that value by any of the dimensions available (State, Agent, Territory, etc...)
scabral- Posts : 58
Join date : 2012-05-02
Similar topics
» Understanding Cube
» Insurance Policy Dimension
» Insurance Policy Accumulating Snapshot
» Policy Customer/Address dimension question
» Difference between a Cube and a Star
» Insurance Policy Dimension
» Insurance Policy Accumulating Snapshot
» Policy Customer/Address dimension question
» Difference between a Cube and a Star
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|