'Storing Updating Fact Table Data

I'm looking for some advice on how to handle this scenario. I have a fact table for Purchase Orders Lines, each line has a Planned Delivery Date. As the purchasing buyers work with vendors, the planned delivery date is updated in the source system. The delivery date can change multiple times (backorders etc.).

I'm trying to figure out the best way to store the delivery dates in the data warehouse. I could update the line with the latest delivery date, however, we also need to be able to store the history of the changes to planned delivery dates.

My other thought was to build a separate table for delivery dates.

Any thoughts would be appreciated.

sql-serverdata-warehousefact-table


Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source

Related Questions