Versioning in the fact table

View previous topic View next topic Go down

Versioning in the fact table

Post  ChrisJ on Fri Dec 16, 2011 9:58 am

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.

ChrisJ

Posts : 1
Join date : 2011-09-22

View user profile

Back to top Go down

Snapshot table...

Post  elmorejr on Mon Dec 19, 2011 9:54 am

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

elmorejr

Posts : 25
Join date : 2011-10-20
Location : Columbus, Ohio, United States of America, Earth, Sol

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