Star Schema for Insurance Aggregator

[jamie_R]

Good Morning,

My Colleague and I are remodelling our Data Warehouse and wonder if you can help.
Our business is an insurance aggregator; we are trying to determine our grain for the FACT table(s).

Our business rule is that a customer can submit a quotation request, and multiple insurers will return a price (no sales involved just quotations).

Our questions is:

• Should we have a FACT table based on the responses? i.e. a FACT record for every response?
  Or should we have a request FACT with a separate response FACT?
  Or is there a better way?

We have been deliberating over this for a few days and really require some expert input.

Thanks in advance
Jamie

[ngalemmo]

Usually two facts. The response fact would include dimensions from the request fact.

[jamie_R]

Usually two facts. The response fact would include dimensions from the request fact.

Thank you for your reply, much appreciated.

[BoxesAndLines]

Are there metrics for the quotation? If not, you can make the quotation a dimension for a response fact.

[jamie_R]

Hello and thanks for your question.

No there's no metrics for the quotation. So do you think it would work having each response as a FACT record and the quotation details as a dimension? How would we count quotation volumes if there would be multiple records(responses) per quote?
Would we give each single quote a unique ID and duplicate this across every response, then count the distinct IDs?

Thanks again
Jamie

[BoxesAndLines]

Yes. Or just count them out of them dimension, or create an aggregate fact at the quote level.

[jamie_R]

Thanks very much for the feedback so far.

We have had a bit of a discussion this morning about this and to give you a bit more information we have within out OLTP database 2 tables to handle our Quote Request and Quote Response data. There is a one to many relationship between the request and the response tables - multiple insurance partners on our panel.

In terms of our actual data warehouse grain of what a fact represents within our business then I guess it would be a request\response(s) process. Taking this into consideration should we not be looking to denormalise our schema and have one fact table which would represent the request\response(s) process. So in the example of a Car request\response then we would be able to slice and dice accross both the users insurance car details (within the request) and the data partners that partners have provided via a quote responses.

i.e For All Car Quotes in July 2011 (request data) please give me the avg premium (response data) for all BMW (quote request data) across a subset of our current partners (quote response data).....

For this reason I think we should be moving forward with one fact table and all request\responses dimensions represented with the single fact table. If we had 2 fact tables then I fear we would end up having to join them to do some of our analysis which I'm not sure would be the best way forward.

Does this make sense as a way forward for our grain\star schema? We are keen to get this correct at this stage. If we get our design correct then everything else should slot into place :-)

Any further feedback would be greatly appreciated.

[BoxesAndLines]

Sounds reasonable to me.

[jamie_R]

Many thanks for your responses so far , we are quite happy with the grain that we have decided on

We can actually take in our sales as well so that in terms of our Web Application our business fact would be across quote request \ quote response \ sale.

I have another question now which is for the same design. We are now looking at our customer dimension which I'm having a bit of trouble trying to design.

The issue we have is that not only can a policy have more than one customer but also a customer can have more than one previous claim and for car insurance the customer may have one or more convictions as well.

In the OLTP database this is represented by 3 tables , driver , claim and conviction with a 1 to many between the driver table and claim table and 1 to many between the driver table and the conviction table. The quote request table also has a one to many between request and customer which reflects that a quote can cover more than 1 customer i.e joint policy cover.

I'm trying not to move away from classic star schema but not sure how to flatten this dimension in such a way as all the customer, previous claim and previous conviction information is capatured into one dimension. Is the best way forward here to use a bridge table between the main customer dimension and both the claim and conviction dimensions?

Maybe snowflake is the way to go ?

Any advice would be very much appreciated :-)

[BoxesAndLines]

You can't flatten those three entities into a single dimension. Two of the entities are facts, Claim and Conviction. That leaves Customer which should flatten rather easily.

[jamie_R]

Thanks for the feedback boxes and lines. So you think that if we had an extra 2 fact tables to cover the Claims and Convictions then this would cover it.

My concern then would be how we would be do analytics across the claims\convictions and quote requests\responses

i.e the business might ask for the total number of quotes responses from a certain partner when the driver had more than one conviction.

With 3 fact tables we would be joining facts ? Unless you mean have this all within one fact table in which case we will have to re-think about the grain?

Any thoughts or am I talking nonsense ;-)

[SV]

I only see you needing these two fact tables. Grain of one at one row per Insurer per request. Another at one row per customer per claim, as you may have claims without convictions. Between these two and dimensions you should be able to support most of your cross analytic requirements. However, you may want to explore more options if performance seems to be a drag down the line - SV

[jamiehout]

Usually two facts. The response fact would include dimensions from the request fact.

I agree

__________________________________
Ask for 20 year term life insurance
and Georgia life insurance today

[BoxesAndLines]

Thanks for the feedback boxes and lines. So you think that if we had an extra 2 fact tables to cover the Claims and Convictions then this would cover it.

My concern then would be how we would be do analytics across the claims\convictions and quote requests\responses

i.e the business might ask for the total number of quotes responses from a certain partner when the driver had more than one conviction.

With 3 fact tables we would be joining facts ? Unless you mean have this all within one fact table in which case we will have to re-think about the grain?

Any thoughts or am I talking nonsense ;-)

You join fact tables by drilling across on dimensions (generally unioning two subqueries). I would assume you would have common dimensions between the two fact tables.

[JeremyL]

• Should we have a FACT table based on the responses? i.e. a FACT record for every response?
  Or should we have a request FACT with a separate response FACT?
  Or is there a better way?

Your request and responses should be seperate facts sharing conformed dims which gives you the ability to analyze data in any which way you want. Be sure of the grain and go for it.

[Sponsored content]