Running the Data Deletion DTS Task

You use the Data deletion DTS task to delete data from the Data Warehouse. You can use the Data deletion task to delete detailed log file data, summarized data, all data in the Data Warehouse, and all data for a particular site in the Data Warehouse. For example, you can use the Data deletion task to delete data from a partially imported log file if the Web log file import fails.

It is important that you frequently delete obsolete log file data so that you do not experience declining performance and limited memory capacity in your Data Warehouse. If you want to maintain historic data from your Commerce Server site, you can extract the data for preservation in reports.

Notes

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

• After you delete data from the Data Warehouse, you must run the Report preparation DTS task to refresh the online analytical processing (OLAP) cubes. You must select full cube processing after you delete data from the Data Warehouse.

• If you delete detailed data imported from an individual Web log file, the log file data accessed by the reports in the Commerce Server Business Desk will remain the same. The following reports are affected by the deletion of detailed data:

  • Entry Path Analysis
  • Ad Reach and Frequency by Date
  • Ad Reach and Frequency per Advertiser
  • Campaign Event Summary
  • Campaign Item Summary
  • Order Events
  • Shopping Basket Events

• If you want to delete the data on which the reports are based, you must choose to delete all data in the Data Warehouse.

• When deleting a specific Web log file data import, the Data deletion DTS task deletes data only from classes that have a child relationship with the Taskhistory class (the Taskhistory class is the parent). The classes from which the Data deletion task does not delete data can grow large over time. The Data deletion task does not delete data from the following classes:

  • Loguser
  • Uri
  • Uriquery
  • Registereduser
  • Ordergroup
  • Date

• You cannot cancel this task after it is started. Although you can click Cancel, the task does not stop.

To delete log file data

To delete all data in the Data Warehouse

To delete log file data

1. Expand Microsoft SQL Server, expand SQL Server Group, and then expand the server on which your Data Warehouse is installed.

2. Right-click Data Transformation Services, and then click New Package.

   Alternatively, if you are changing an existing package, right-click Data Transformation Services, click All Tasks, and then select Open Package. In the Select File dialog box, click the package you want to change, and then click Open.

3. On the Task menu, click Data deletion (Commerce Server).

4. In the Delete Task dialog box, do the following:

   Use this	To do this
   Description	Type a description for the task. The maximum number of characters for the description is 255.
   Operation level	Select from the drop-down list one of the following: Site level to delete Web log file data for a site Data Warehouse level to delete log file data or all data for a Data Warehouse
   Site Name	If you selected site level operation, select from the drop-down list the site for which you want to delete Web log data.
   Data Warehouse	If you selected Data Warehouse level operation, select from the drop-down list the Data Warehouse for which you want to delete Web log data.
   Select delete type	Select Web Log Imports from the drop-down list to delete data associated with particular Web log data imports.
   Number of retries	Type the number of times you want the task to retry connection failures to the Data Warehouse. The default number of retry attempts is 10.
   Seconds between retries	Type the number of seconds you want the task to wait between retry attempts. The default time interval is 30 seconds.

5. In the Select delete level section, do the following:

   Use this	To do this
   Delete all data	Select this option to delete all data associated with the Web log files you want to delete. Note The Delete all data option for data imported from a specific Web log file does not delete all of the data associated with that Web log file in the Data Warehouse. Instead, it deletes data from all classes that are related to the Taskhistory tables. (Related classes have a self, child, parent, grandparent, or grandchild relationship with the members of the Taskhistory table.) The classes in the default Data Warehouse schema are: Basket CampaignEvent FirstUriByDate HitsByHour HitsInfo LastUriByDate LogImportJobSummary LogImportSubtask OpenUserVisit Order Request RequestByDateByUriByQueryString Visit VisitInfo
   Delete detailed data	Select this option to delete the detailed data associated with the Web log files that you want to delete.

6. In the Specify Web Logs to delete section, select one of the following from the drop-down list:

   • Delete all Web log imports older than. Select this option to specify a time period for which you want to keep imported log file data.
   • Keep last n Web log imports only. Select this option to specify a number of Web log imports for which to keep imported log file data.
   • Select Web log imports to delete. Select this option to specify Web log file imports for which to delete imported log file data. Select the box to the left of the Task Id of the Web log file for which you want to delete log file data.

7. Click OK.

8. On the Package menu, click Execute.

   The Executing Package dialog box appears, showing the progress of your import. When the import process is complete, the Package Execution Results dialog box informs you that it completed successfully.

9. Click OK, and then click Done.

   The detailed data of the selected Web log files is deleted from the Data Warehouse.

10. Run the Report preparation DTS task to organize the imported data into the designated OLAP cubes. For instructions about running the Report preparation task, see Running the Report Preparation DTS Task.

To delete all data in the Data Warehouse

1. Run the Configuration synchronization task to synchronize your site configuration with the Data Warehouse. For instructions, see Running the Configuration Synchronization DTS Task.

2. Expand Microsoft SQL Server, expand SQL Server Group, and then expand the server on which your Data Warehouse is installed.

3. Right-click Data Transformation Services, and then click New Package.

   Alternatively, if you are changing an existing package, right-click Data Transformation Services, click All Tasks, and then select Open Package. In the Select File dialog box, click the package you want to change, and then click Open.

4. On the Task menu, click Data deletion (Commerce Server).

5. In the Delete Task dialog box, do the following:

   Use this	To do this
   Description	Type a description for the task. The maximum number of characters for the description is 255.
   Operation level	Select Data Warehouse level from the drop-down list.
   Data Warehouse	Select from the drop-down list the Data Warehouse from which you want to delete all data.
   Select delete type	Select All data in Data Warehouse from the drop-down list to delete all data in the Data Warehouse.
   Number of retries	Type the number of times you want the task to retry connection failures to the Data Warehouse. The default number of retry attempts is 10.
   Seconds between retries	Type the number of seconds you want the task to wait between retry attempts. The default time interval is 30 seconds.

6. Click OK.

   Note

   • You can add additional DTS tasks to the open package, add sequencing or workflow information, and then run all the tasks in the package at one time.

7. On the Package menu, click Execute.

   The Executing Package dialog box appears, showing the progress of your import. When the import process is complete, the Package Execution Results dialog box informs you that it completed successfully.

8. Click OK, and then click Done.

   All data is deleted from the specified Data Warehouse.

9. Run the Report preparation DTS task to organize the imported data into the designated OLAP cubes. For instructions about running the Report preparation task, see Running the Report Preparation DTS Task.

This data is now available for reports.

Copyright © 2005 Microsoft Corporation.
All rights reserved.