Deleting Data from the Data Warehouse

It is important that you periodically delete data from the Data Warehouse on an ongoing basis 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. While 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 provides you with the Data Deletion DTS task, which will delete 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 using the Data Deletion task, see Running 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 their site.

A user visits the site and adds the new product to their basket. Adding the product to the user's shopping basket causes Commerce Server to log a Commerce Event into the 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 within the Catalog runtime database.

If the system administrator runs the Product Catalog 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 containing the Product ID for the new product will resolve as a foreign key to a catalog table.

If, however, 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 could also happen if one of the Commerce Server run-time components deletes items before the system administrator runs the Configuration synchronization DTS task.

For information about deleting orphaned data from the Data Warehouse database, see Deleting Orphan Data.

Note

• 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.

Copyright © 2005 Microsoft Corporation.
All rights reserved.