Deleting Data from the Data Warehouse
Make sure that you periodically delete data from the Data Warehouse to prevent the Data Warehouse database from running out of space. If the database runs out of space, you will lose both performance and data. Although you can set the Data Warehouse database to automatically expand, this option degrades performance over time, and eventually, the size of the database will reach the physical limits of available memory.
Commerce Server gives you the Data Deletion DTS task that deletes detailed log file data or all data in the Data Warehouse. For information about how the Data Deletion DTS task works, see ETL Process for the Data Deletion DTS Task. For information about how to use the Data Deletion task, see How to Run the Data Deletion DTS Task.
After you use the Data Deletion DTS task to delete Web log file data from the Data Warehouse, there will still be data in the Data Warehouse that you must delete manually. For example, a site administrator adds a product to a catalog and that product is added to the Catalog runtime database. The site administrator then deploys the updated catalog to the site.
A user visits the site and adds the new product to the user's basket. Adding the product to the user's shopping basket causes Commerce Server to log a Commerce Event in the Internet Information Services (IIS) Web log file (Add to basket event).
The Commerce Event now contains a product ID for that product that references a row in a table in the Catalog runtime database.
If the system administrator runs the Catalog Data Import DTS task, the task will import the Catalog runtime database into the Data Warehouse, and resolve the product ID to a full description. The Commerce Event that contains the product ID for the new product will resolve as a foreign key to a catalog table.
However, if the site administrator deletes the new product from the Catalog runtime database after the Commerce Event is logged, but before product catalog data is imported into the Data Warehouse, the product ID of the new product will never be resolved to a product in the catalog, and is considered "orphaned." In this example, a fact (the product ID) references dimension data (the name and description of the product) that does not exist. This might also occur if one of the Commerce Server run-time components deletes items before the system administrator runs the Configuration synchronization DTS task.
The Profile, Catalog, and Campaign resources do not log delete operations. If you delete a user, campaign, or catalog item, the user, campaign, or catalog item is permanently removed.