Resetting the Data Movement Application in the SBS_OLAP Database

In the following procedures, you will use the TRUNCATE statement to delete all test data from the dimension, fact, and auditing tables in the SBS_OLAP database, which will also reset the IDENTITY property for each table. You will then delete each log file in the C:\Microsoft Press\SQL DTS SBS\DataMovementApplication folder and delete the package logging data stored in Microsoft SQL Server. Finally, you will delete the delimited text files stored in the C:\Microsoft Press\SQL DTS SBS\DataMovementApplication folder that you used during the development of the data movement application.

Note  

If you skipped Chapter 9, execute the IfYouSkippedChapter9.cmd batch file in the C:\Microsoft Press\SQL DTS SBS\Ch10\SkippedChapterFiles folder before you begin these procedures. This batch file restores the SBS_OLTP and SBS_OLAP databases and copies the DTS packages that would have been created in Chapters 1 through 9 into the appropriate folders. It also records the location of the Config.ini initialization file in the Windows registry. If you do not want this batch file to overwrite any packages that you created in Chapters 1 through 9, you must move them or rename them before you execute this batch file. After executing this script you will also need to restore C:\Microsoft Press\SQL DTS SBS\Ch9\ChapterFiles\SQL DTS SBS.cab in Analysis Services. (See Chapter 9 for details.)

Reset the tables in the SBS_OLAP database

Open SQL Query Analyzer and then connect to your SQL Server instance as a system administrator.

1. On the toolbar, click Load SQL Script.

2. In the Look In box, navigate to C:\Microsoft Press\SQL DTS SBS\Ch10\ChapterFiles and then double-click ResetTables.sql.

   This script deletes all data from the dimension, fact, staging, and auditing tables in the SBS_OLAP database by using the TRUNCATE TABLE statement; this statement also resets the IDENTITY property on each of these tables to their initial SEED values (which in this case are 1). The TRUNCATE TABLE statement is used rather than the DELETE statement because it is faster, uses fewer system and transaction log resources, and resets the IDENTITY property. In order to execute the TRUNCATE TABLE statement, this script begins by dropping the foreign key constraints on the SalesFact table. It then recreates these constraints after the TRUNCATE TABLE statements have executed.

   

3. Execute the ResetTables.sql script.

Now that you have deleted the data in these tables and reset the IDENTITY properties, you will delete the delimited text files containing the test data and delete the text-based log files that the data movement application previously generated.

Delete delimited text files and text-based log files

Using Microsoft Windows Explorer, navigate to C:\Microsoft Press\SQL DTS SBS\DataMovementApplication.

Note  

If you skipped one or more of the previous chapters, some of the files listed below may not be present in the DataMovementApplication folder.

1. Delete the following files:

   • 1996SalesData.txt

   • 1997SalesData.txt

   • LoadCustomerStageTableExceptionFile.txt

   • LoadCustomerStageTableExceptionFile.txt.Dest

   • LoadCustomerStageTableExceptionFile.txt.Source

   • LoadHistoricalDataErrorLog.txt

   • LoadProductDimTableExceptionFile.txt

   • LoadProductStageTableExceptionFile.txt

   • MasterUpdatePackageErrorLog.txt

   • NewCustomers.txt

   • NewProducts.txt

   • NewSalesData.txt

   • UpdateCustomerDimPackageErrorLog.txt

   • UpdateProductDimPackageErrorLog.txt

   • UpdateSalesFactsPackageErrorLog.txt

   • UpdateTimeDimensionExecutionLog.txt

Now that you have deleted these text files, you will delete the logging information stored in SQL Server for the data movement application packages.

Deleting logging information stored in SQL Server

Open SQL Server Enterprise Manager.

1. Expand Microsoft SQL Servers, expand SQL Server Group, expand your SQL Server instance, and then expand Data Transformation Services.

2. Right-click Local Packages and then click Package Logs.

3. In the DTS Packages Available On The Server (LOCAL) list, select LoadHistoricalData.

4. Click Delete, click Delete All Logs For The Package: LoadHistoricalData, and then click OK.

5. In the DTS Packages Available On The Server (LOCAL) list, select MasterUpdate.

6. Click Delete, click Delete All Logs For The Package: MasterUpdate, and then click OK.

7. In the DTS Packages Available On The Server (LOCAL) list, select UpdateCustomerDim.

8. Click Delete, click Delete All Logs For The Package: UpdateCustomerDim, and then click OK.

9. In the DTS Packages Available On The Server (LOCAL) list, select UpdateProductDim.

10. Click Delete, click Delete All Logs For The Package: UpdateProductDim, and then click OK.

11. In the DTS Packages Available On The Server (LOCAL) list, select UpdateSalesFacts.

12. Click Delete, click Delete All Logs For The Package: UpdateSalesFacts, and then click OK.

13. Click Close.

Now that you have finished resetting the data movement application, you are ready to load historical data into the SBS_OLAP database from the C:\Microsoft Press\SQL DTS SBS\Ch10\ChapterFiles\Data folder and then process this data into the Sales cube.