Transactional fact table with a date ?
2 posters
Page 1 of 1
Transactional fact table with a date ?
We are builing a fact table with dimensions, couple of counters and an amount field(this won't change) as facts. Basically we are creating this table as a transactional fact with a date meaning it's similar to a daily snapshot fact but we don't want to record all policies everyday and just want to load the policies which has a change in one of these counters or dimensions.
When one of the counter does not change on a given day we will put 0 in that so we can sum it across date and use -1 when there is a cancelation for same reason.
I have seen daily snapshot, transactional fact table with begin and end dates but never saw a this kind of daily snapshot with only changes design so wondering what others do in this scenario. Mostly, business would like to know how many new policies we sold in a month (which will be a counter) or in a given range or on a given day...I think we can answer all these questions here but would like to know if I am missing something here. To count the number of policies on a given day we will go to accumulating snapshot table. Please let me know if I oversee anything here.
Thanks for your time...
When one of the counter does not change on a given day we will put 0 in that so we can sum it across date and use -1 when there is a cancelation for same reason.
I have seen daily snapshot, transactional fact table with begin and end dates but never saw a this kind of daily snapshot with only changes design so wondering what others do in this scenario. Mostly, business would like to know how many new policies we sold in a month (which will be a counter) or in a given range or on a given day...I think we can answer all these questions here but would like to know if I am missing something here. To count the number of policies on a given day we will go to accumulating snapshot table. Please let me know if I oversee anything here.
Thanks for your time...
VTK- Posts : 50
Join date : 2011-07-15
Re: Transactional fact table with a date ?
I run into this situation all the time. The temptation to improve on the daily snapshot is great. The scenario is common as well. Only a small number of rows change per day/week/month. It seems like such a waste to use all that disk space. In the end, I take a deep breath, remind myself that is a data warehouse, and I'm supposed to use a lot of disk space. Then I got to work on identifying the "real" business requirements for maintaining historical data for my snapshots. In most cases, the business wants a maximum of 13 months of daily snapshots. After that, a month end snapshot is sufficient. If I still haven't convinced you, rest assured that Kimball has written a design tip on your approach before, its location eludes me though.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Similar topics
» Transactional detail fact table w/complimentary snapshot table. How do the two play together?
» Adding a fact table with start date and end date to a (SSAS) multidimensional cube
» Transactional Fact and update of records
» Fact table for transactional data
» Datetime or DateID and TimeID in transactional fact table
» Adding a fact table with start date and end date to a (SSAS) multidimensional cube
» Transactional Fact and update of records
» Fact table for transactional data
» Datetime or DateID and TimeID in transactional fact table
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum