Should I zero off previous entry?

View previous topic View next topic Go down

Should I zero off previous entry?

Post  Toffeeman on Mon Feb 06, 2012 11:16 am

I have a scenario to model bookings in an holiday environment. where I have a fact table with numerous surrogate keys (SK's) and a number of measures (holiday cost, nights etc.). SK's can change (i.e. booking status) and measures can change (i.e. holiday cost). Currently if anything at all changes on the bookings we insert a fact table row zeroing off the current record and inserting a new record using a transaction amount column. I am thinking of changing this to only zero off the record if one of the SK's has changed but if one of the measures has changed then maintain a transaction column to hold the difference between the latest value from the source and the current value in the fact table. The are obviously going to be date keys so that point in time analysis can be undertaken.

We used to run a similar model but then moved to the zeroing off method for reasons that I am unaware of. Can anyone spot an obvious problem with my proposal?

example
BookingID | Version | SK1 | SK2 | Value | ValueTransaction
123 | 1 | 2 | 5 | 100 | 100
Change to SK 1
123 | 2 | 2 | 5 | 0 | -100
123 | 3 | 3 | 5 | 100 | 100
Value change
123 | 4 | 3 | 5 | 150 | 50
Change to SK 2
123 | 5 | 3 | 5 | 0 | -150
123 | 6 | 3 | 6 | 150 | 150

Toffeeman

Posts : 2
Join date : 2011-03-04
Location : Cheshire, UK

View user profile

Back to top Go down

Re: Should I zero off previous entry?

Post  ngalemmo on Thu Feb 09, 2012 12:00 am

The idea of having a separate 'value' and 'value transaction' doesn't make sense as it does not add any information and, as described, the 'value' column is semi-additive which is not a good thing. The 'value transaction' column is the true value and should be the single 'value' column.

If you take booking #123, SK1 = 2 and SK2 = 5, it would report as having a 'value' of 100 and a 'value transaction' of 0. That simply isn't correct. That version of the booking has no business value at this point in time. It had value at the time it was current, but not now. The way to report what it's value was then is to include a timestamp on the row and limit to report to transaction states before a point in time.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

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