Versioning in the fact table

[ChrisJ]

Hi,

I am working on a project where we hold multiple versions of an order in the fact table. When an order is updated in the source system, we flow the entire order again, marking the newest 'layer' as latest and marking the previous layer as superseded (not latest). An order can equate to thousands of fact rows. We also mark the last version received on a particular day as 'end of day' which represents the state of the order at the end of the last close of business.

In the fact table, currently we have a latest flag and an end of day flag, plus a foreign key (defaulted to year 3000) to a date dimension when that layer was superseded. Is this a sensible way of doing this? We are adding table partitioning soon so need a single column, and are also trying to avoid updating fact table rows.

Thanks.

[elmorejr]

This sounds like a normal periodic snapshot table, with snapshots taken daily. Nothing out of the ordinary here...

http://books.google.com/books?id=XoS2oy1IcB4C&lpg=PT162&dq=kimball%20periodic%20snapshot%20table&pg=PT161#v=onepage&q&f=false