Data Warehouse Maintenance Best Practices
The following best practices apply to the Commerce Server Data Warehouse.
On a weekly basis, defragment indexes on all the tables that are more than 30 percent fragmented.
The Web server log import DTS 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 complete.
To determine the extent an index is fragmented, and to defragment an index, use two SQL Server Database Console 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 avoids declining performance and limited memory capacity in the Data Warehouse. If you need to maintain historic data from your Commerce Server 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.
Since deleting dimensional data is a slow process, it is recommended that you perform this step infrequently, for example, once a year.
For information about deleting dimensional data, see Running the Data Deletion DTS Task.
Before running 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, then SQL Server needs a tempdb database that is at least 15 GB. Otherwise, the Data deletion task will fail.
When new profile properties are added to the Profiling System, evaluate whether you must extend the Data Warehouse to import them.
Commerce Server provides five empty properties in the User Object profile definition so 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 specifying which properties are imported into the Data Warehouse, and determining when you must extend the Data Warehouse, see Specifying Data to be Imported into the Data Warehouse.
For information about extending the Data Warehouse and creating relations, see Extending the Data Warehouse. In addition, see the white paper "Extending the Commerce Server Data Warehouse / Analytics," located at the Development page on the Commerce Server Web site https://go.microsoft.com/fwlink/?LinkId=945. For samples that show how to extend the Data Warehouse, see Business Analytics Samples.
See Also
Extract, Transform, and Load Process for DTS Tasks
Multiple Sites and One Data Warehouse
Copyright © 2005 Microsoft Corporation.
All rights reserved.