Data Warehouse Maintenance Best Practices
The following maintenance best practices apply to the Data Warehouse.
Weekly, you should defragment indexes on all tables that are more than 30 percent fragmented.
The Web server log import DTS (Data Transformation Services) task causes indexes to become fragmented. When your indexes are more than 30 percent fragmented, it will take longer for the Web server log import DTS task to finish.
To determine the extent an index is fragmented, and to defragment an index, use two SQL Server Database Console Command statements (DBCC): DBCC SHOWCONTIG and DBCC INDEXDEFRAG. For instructions, see SQL Server Books Online.
On a rotating basis, delete detailed Web log file data, and then delete all summarized data from the Data Warehouse.
The Data Warehouse cannot maintain detailed data indefinitely. Eventually, the database will reach maximum capacity.
It is important to frequently delete obsolete log file data. This helps avoid declining performance in the Data Warehouse. If you must maintain historical data from your Commerce Server 2009 database, use reports to extract and save the data in a convenient format for future reference.
On a regularly scheduled basis, delete dimensional data.
The Data deletion DTS task deletes fact data, not dimensional data. You must delete dimensional data programmatically.
Because deleting dimensional data is a slow process, we recommended that you perform this task infrequently, for example, one time per year.
For information about how to delete dimensional data, see Running the Data Deletion DTS Task.
Before you run the Data deletion DTS task, verify that the size of the tempdb database is larger than the size of the database to be deleted.
For example, if the database to be deleted is 15 GB, SQL Server needs a tempdb database that is larger than 15 GB. Otherwise, the Data deletion task will fail.
When new profile properties are added to the Profiles System, evaluate whether you must extend the Data Warehouse to import them.
Commerce Server 2009 provides five empty properties in the User Object profile definition so that you can add new profile properties without extending the Data Warehouse schema. The properties can be imported automatically.
If you add more than five custom profile properties to the User Object, or if you add properties to other data objects, you must extend the Data Warehouse.
For information about how to specify which properties are imported into the Data Warehouse, and how to determine when you must extend the Data Warehouse, see Specifying Data to be Imported into the Data Warehouse.
For information about how to extend the Data Warehouse and create relations, see How to Extend the Data Warehouse Database. In addition, see the white paper "Extending the Commerce Server Data Warehouse/Analytics," located at the Development page on the Commerce Server 2009 Web site.
See Also
Other Resources
Multiple Sites and One Data Warehouse
Data Warehouse Import Wizard UI