Star Schema for Insurance Aggregator

View previous topic View next topic Go down

Star Schema for Insurance Aggregator

Post  jamie_R on Tue Oct 11, 2011 5:39 am

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

jamie_R

Posts : 6
Join date : 2011-10-10

View user profile

Back to top Go down

Re: Star Schema for Insurance Aggregator

Post  ngalemmo on Tue Oct 11, 2011 10:34 am

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

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Star Schema for Insurance Aggregator

Post  jamie_R on Tue Oct 11, 2011 10:43 am

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

Thank you for your reply, much appreciated.

jamie_R

Posts : 6
Join date : 2011-10-10

View user profile

Back to top Go down

Re: Star Schema for Insurance Aggregator

Post  BoxesAndLines on Tue Oct 11, 2011 1:37 pm

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

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

View user profile

Back to top Go down

Re: Star Schema for Insurance Aggregator

Post  jamie_R on Tue Oct 11, 2011 3:09 pm

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

jamie_R

Posts : 6
Join date : 2011-10-10

View user profile

Back to top Go down

Re: Star Schema for Insurance Aggregator

Post  BoxesAndLines on Tue Oct 11, 2011 10:47 pm

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

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

View user profile

Back to top Go down

Re: Star Schema for Insurance Aggregator

Post  jamie_R on Wed Oct 12, 2011 4:57 am

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.







jamie_R

Posts : 6
Join date : 2011-10-10

View user profile

Back to top Go down

Re: Star Schema for Insurance Aggregator

Post  BoxesAndLines on Wed Oct 12, 2011 11:06 am

Sounds reasonable to me.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Star Schema for Insurance Aggregator

Post  jamie_R on Tue Oct 18, 2011 6:07 am

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 :-)


jamie_R

Posts : 6
Join date : 2011-10-10

View user profile

Back to top Go down

Re: Star Schema for Insurance Aggregator

Post  BoxesAndLines on Tue Oct 18, 2011 9:13 am

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.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Star Schema for Insurance Aggregator

Post  jamie_R on Tue Oct 18, 2011 10:25 am

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 ;-)

jamie_R

Posts : 6
Join date : 2011-10-10

View user profile

Back to top Go down

Re: Star Schema for Insurance Aggregator

Post  SV on Tue Oct 18, 2011 12:48 pm

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

SV

Posts : 2
Join date : 2011-10-17

View user profile

Back to top Go down

Re: Star Schema for Insurance Aggregator

Post  jamiehout on Wed Oct 19, 2011 11:04 pm

ngalemmo wrote: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

jamiehout

Posts : 3
Join date : 2010-12-22

View user profile

Back to top Go down

Re: Star Schema for Insurance Aggregator

Post  BoxesAndLines on Thu Oct 20, 2011 9:08 am

jamie_R wrote: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.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Star Schema for Insurance Aggregator

Post  JeremyL on Wed Nov 02, 2011 7:32 am

jamie_R wrote:
  • 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.

JeremyL

Posts : 6
Join date : 2011-10-25
Location : USA

View user profile

Back to top Go down

Re: Star Schema for Insurance Aggregator

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

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