Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Insurance Policy Submission Cube

3 posters

Go down

Insurance Policy Submission Cube Empty Insurance Policy Submission Cube

Post  scabral Sun Jul 28, 2013 10:52 pm

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

scabral

Posts : 58
Join date : 2012-05-02

Back to top Go down

Insurance Policy Submission Cube Empty Re:Insurance Policy Submission Cube

Post  hkandpal Tue Jul 30, 2013 9:07 am

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

hkandpal

Posts : 113
Join date : 2010-08-16

Back to top Go down

Insurance Policy Submission Cube Empty Re: Insurance Policy Submission Cube

Post  scabral Tue Jul 30, 2013 10:03 am

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

scabral

Posts : 58
Join date : 2012-05-02

Back to top Go down

Insurance Policy Submission Cube Empty Re: Insurance Policy Submission Cube

Post  hkandpal Tue Jul 30, 2013 4:13 pm

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


hkandpal

Posts : 113
Join date : 2010-08-16

Back to top Go down

Insurance Policy Submission Cube Empty Re: Insurance Policy Submission Cube

Post  BoxesAndLines Tue Jul 30, 2013 10:57 pm

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

You lost me there. Why is it better? The whole premise is designed on summing granular data. Only aggregate when needed for performance.
BoxesAndLines
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

Back to top Go down

Insurance Policy Submission Cube Empty Re: Insurance Policy Submission Cube

Post  scabral Wed Jul 31, 2013 9:37 am

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

Back to top Go down

Insurance Policy Submission Cube Empty Re: Insurance Policy Submission Cube

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum