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.

    Tuesday, July 30, 2013 2:09 PM

All replies

  • Hi,

    I think a simple factless fact would solve your problem.

    Fact table would have each of the dimensions you stated, and then a simple row count would give your a count, no need to store it in the fact table.  So, if you want to count the number of quote on a given date, just count the number of rows, sliced by date and where policy state = 'quote'.  Or in your example you are just grouping by date, instead of slicing.

    A simple SQL statement to show number of quotes per month

    SELECT date_month, count(*)
    FROM FactTable f INNER JOIN date d ON f.date_key = d.fate_key
    WHERE state = 'quote'
    GROUP by date_month



    Wednesday, August 07, 2013 9:20 AM