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.

From the Blogs

Don’t let bad data sneak up on you when and where you least expect it. Ferret out bad data with Melissa Data’s newest Profiling Component for SSIS. Learn how to take control of your data using knowledge-base-driven metadata. The truth shall set you free!...More

Now that we’ve outlined the process to let servers in a SQL Server AlwaysOn Availability Group "talk to each other" by means of setting up linked servers, it’s possible to set up some additional or improved checks on Availability Group Health....More

In my previous post, I provided a high-level outline of the core logic (and rationale behind that logic) that would be needed to set up regular synchronization checks on SQL Server Agent Jobs for servers where AlwaysOn Availability Groups have been deployed. In this post, I’ll walk through the steps--and the code--needed to setup those checks....More