Insurance - Policies and Riders Periodic Snapshot Fact Table Design Suggestion
2 posters
Page 1 of 1
Insurance - Policies and Riders Periodic Snapshot Fact Table Design Suggestion
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.
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
Re: Insurance - Policies and Riders Periodic Snapshot Fact Table Design Suggestion
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.
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 : 44
Location : Istanbul, Turkey
Re: Insurance - Policies and Riders Periodic Snapshot Fact Table Design Suggestion
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.
- 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
Re: Insurance - Policies and Riders Periodic Snapshot Fact Table Design Suggestion
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/
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 : 44
Location : Istanbul, Turkey
Similar topics
» Monthly Periodic Snapshot Table using SCD Type1 Dimension tables - Design Question.
» Aggregates in Periodic Snapshot Fact Table
» Is this a Correct Periodic Snapshot Fact Table?
» Muliple currencies for periodic snapshot fact table
» Transaction Fact or periodic snapshot fact
» Aggregates in Periodic Snapshot Fact Table
» Is this a Correct Periodic Snapshot Fact Table?
» Muliple currencies for periodic snapshot fact table
» Transaction Fact or periodic snapshot fact
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum