modeling lost business

View previous topic View next topic Go down

modeling lost business

Post  scabral on Mon Sep 29, 2014 1:30 pm

Hi,

we have an Insurance premium dimensional star schema that is setup as a quarterly periodic snapshot. The fact table (Account Premium), gets loaded each quarter with the premium amount for each client. The Client Dimension is also setup like a snapshot table and gets loaded each quarter with all of the accounts that are active for that quarter. The Client dimension tables contains a Begin and End Date that coincides with a Begin and End date on the fact table. So theoretically, they are static snapshot tables that get loaded each quarter.

What we have been asked to do is to add an attribute to allow the users to analyze how much business was lost each quarter. Seems like an easy enough task. I was thinking of just adding another attribute to the Client dimension call LostIndicator and if the Account was lost on the Quarter it would say "Lost", otherwise it would say "retained". This way the users would be able to slice and dice and analyze what accounts were "Lost" or "retained" each quarter and what the premium values were for each.

The issue that I have is that we get the quarterly premium file at the end of each quarter (usually by the 2nd or 3rd day of the following month after quarter end), but that account that was lost is not present in the file. So when we receive the file that tells us the accounts that were lost for the quarter, there is no way to tag the client dimension for that quarter because the account is not present.

I'm looking for some ways to make this work, but have not come up with anything as of yet.



scabral

Posts : 58
Join date : 2012-05-02

View user profile

Back to top Go down

Re: modeling lost business

Post  ngalemmo on Mon Sep 29, 2014 2:06 pm

It's a load processing issue. You will need to compare the incoming data against history to see which ones dropped out.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: modeling lost business

Post  BoxesAndLines on Mon Sep 29, 2014 4:06 pm

I would consider creating a new fact table with lost accounts. Identifying the deleted accounts is a straightforward ETL process as Nick mentioned.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: modeling lost business

Post  scabral on Thu Oct 02, 2014 7:56 am

Thanks BoxesAndLines,

I can see where it makes sense to create a new fact for lost accounts, but how do i correlate the lost accounts together with the retained accounts in the other fact table so that the users can analyze both lost and retained accounts for a given quarter?

scabral

Posts : 58
Join date : 2012-05-02

View user profile

Back to top Go down

Re: modeling lost business

Post  BoxesAndLines on Thu Oct 02, 2014 8:44 am

Through natural keys. Make sure you use the same dimensions in both facts (conformed) so you can easily drill across.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: modeling lost business

Post  scabral on Thu Oct 02, 2014 1:03 pm

ok, i will try it. thanks

scabral

Posts : 58
Join date : 2012-05-02

View user profile

Back to top Go down

Re: modeling lost business

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