Insurance - Policies and Riders Periodic Snapshot Fact Table Design Suggestion

View previous topic View next topic Go down

Insurance - Policies and Riders Periodic Snapshot Fact Table Design Suggestion

Post  Leonhart on Wed Apr 24, 2013 5:36 am

Currently I have a large database of policies and riders.
The policies and riders are captured monthly and appended. (previous month append to next month and so on)
Now the database is too large. I intend to change the structure of the database to Dimensional Modeling, in order to only capture the data which has changed from last month. I use 2 Valid Date and End Date columns to check and mark the validate period of a data row. This way I could reduce the size of the database by eliminating those redundancy data.
Is there any effective design for my situation? Policy:Rider has the 1:M relationship. I intend to create 2 fact tables, 1 for Policies and 1 for Riders, but I don't know if it's an effective way. For the policies and riders, there are many columns that we need to capture historical data, so I'm afraid that the database will still be large after the changes.
Your help is really appreciated. Thanks in advances.


Leonhart

Posts : 2
Join date : 2013-04-24

View user profile

Back to top Go down

Re: Insurance - Policies and Riders Periodic Snapshot Fact Table Design Suggestion

Post  umutiscan on Wed Apr 24, 2013 8:02 am

Dimensional models are designed for reporting needs. I don't know the reason of capturing the data each month, but if you do this just for getting a back up of the operational system you can continue with the current solution.
If you mention about a reporting environment, your design depends on your data and reporting requirements. Your solution may not be as simple as you think. You will need two dimensions and a fact between them but you will have to do more for an effective solution.

umutiscan

Posts : 51
Join date : 2010-11-26
Age : 36
Location : Istanbul, Turkey

View user profile

Back to top Go down

Re: Insurance - Policies and Riders Periodic Snapshot Fact Table Design Suggestion

Post  Leonhart on Wed Apr 24, 2013 8:16 am

Sorry if I didn't make myself clear. I don't do this for back-up, but indeed for reporting needs. For example, we need the answer for following questions:

- How many in force policies are there in 2010, 2011, 2012?
- List all customers who have lapsed policies in 2010, 2011, 2012?
- Count the most riders component that customers usually buy with the basic product A?
etc.

The current solution is that, the data is captured every month. So there are a lot of data that doesn't have anything changed for a long time, but still got captured every month, hence the redundancy of the data and the large size of database.
I'm in a process of changing the database structure, in order to reduce the size of database by eliminating the redundancy of the database. I intend to use the Dimensional Models/Star Schema to re-structure the database.
My plan is using SCD type 6, to keep the historical data when there is any change occur and also reduce the redundancy. What I have in my mind is there will be two fact tables with its correspondent dimensions, one for policies grain and one for riders grain.

The problem is I'm trying to find a most effective database structure for the current requirement and the data that I have. Are there any way to combine those 2 fact tables into 1 fact with few dimensions, but still capture the change of each policy/component rider if there is.

Leonhart

Posts : 2
Join date : 2013-04-24

View user profile

Back to top Go down

Re: Insurance - Policies and Riders Periodic Snapshot Fact Table Design Suggestion

Post  umutiscan on Thu Apr 25, 2013 9:58 am

I'm not familiar with insurance, but if your aim is just keeping the relation with policies and riders you don't need two different fact tables. You can create two dimensions (policies and riders) and keep the relation in a fact table. You insert new rows into dimension when any attribute of your dimension changes, and insert new rows into fact table when the relation between your dimensions changes or a new relation occurs.

Your design also depends on the reporting requirements, you may need some advanced design techniques. If you have some rapidly changing attributes in your dimension and they are not required by the business you may take them out of your dimension. Or maybe you don't need to track the history of all attributes, or you can pack all rapidly changing attributes in a different dimension etc..

If you are in trouble with rapidly changing dimensions, I recommend you to read the article "When A Slowly Changing Dimension Speeds Up" from Kimball Group which is telling about some desing techniques to resolve rapidly changing dimension problems.
http://www.kimballgroup.com/1999/08/03/when-a-slowly-changing-dimension-speeds-up/

umutiscan

Posts : 51
Join date : 2010-11-26
Age : 36
Location : Istanbul, Turkey

View user profile

Back to top Go down

Re: Insurance - Policies and Riders Periodic Snapshot Fact Table Design Suggestion

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