Discontinued Products

When a product is deleted from your production system, you don't necessarily want to delete it from your data warehouse; you probably keep historical data that correlates to the discontinued products. In the scenario I discuss in the main article, using a discontinued bit value lets you keep track of a product's status without deleting it from the data warehouse. You might decide sometime to delete all fact table rows that correlate to discontinued products, then delete the discontinued products themselves. If you turn on a discontinued bit in the data warehouse when a product is deleted from the source system, you'll need to implement some kind of mechanism in the online transaction processing (OLTP) system that makes sure that deleted product IDs aren't reused for new products. Reuse of discontinued product IDs causes ambiguity and errors in a data warehouse. I don't cover that type of mechanism in this article; the process we're developing is complex enough. But you can implement such a mechanism by using a discontinued bit column in the source Products table instead of deleting the products. Or you can use another log table to hold the deleted product IDs, plus an INSERT trigger that verifies that those product IDs aren't reentered into the Products table.