Should I zero off previous entry?
2 posters
Page 1 of 1
Should I zero off previous entry?
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
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
Re: Should I zero off previous entry?
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.
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.
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|