modeling lost business
3 posters
Page 1 of 1
modeling lost business
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.
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
Re: modeling lost business
It's a load processing issue. You will need to compare the incoming data against history to see which ones dropped out.
Re: modeling lost business
I would consider creating a new fact table with lost accounts. Identifying the deleted accounts is a straightforward ETL process as Nick mentioned.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Re: modeling lost business
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?
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
Re: modeling lost business
Through natural keys. Make sure you use the same dimensions in both facts (conformed) so you can easily drill across.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Similar topics
» Lost in fact granularity
» Business just wants a subset
» Calculations Defined by Business
» Dimension table with no business key?
» Business Process Identification, how low should you go?
» Business just wants a subset
» Calculations Defined by Business
» Dimension table with no business key?
» Business Process Identification, how low should you go?
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|