Transactional fact table with a date ?

View previous topic View next topic Go down

Transactional fact table with a date ?

Post  VTK on Wed Aug 28, 2013 1:54 pm

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...

VTK

Posts : 50
Join date : 2011-07-15

View user profile

Back to top Go down

Re: Transactional fact table with a date ?

Post  BoxesAndLines on Thu Aug 29, 2013 9:06 am

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.
avatar
BoxesAndLines

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

View user profile

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