Loading and Verifying Monthly Data


Before you can successfully load new sales data for January, you must add the appropriate time records to the TimeDim table, new product information (if any) to the ProductDim table, and new customer information (if any) to the CustomerDim table. You can add new time records for January 1998 by executing the PopulateTimeDimension package and passing appropriate global variable values to it by using the UpdateTimeDim.cmd batch file. The new sales information for January 1998 is stored in the SalesData199801.txt file located in the C:\Microsoft Press\SQL DTS SBS\Ch10\ChapterFiles\Data folder. You will edit the Config.ini file to point the UpdateSalesFacts package to this delimited text file. To demonstrate how to handle the error caused by a missing customer dimension member, you will not modify the Config.ini file to point the UpdateCustomerDim package to the new customer data stored in the NewCustData199801.txt file until after an error is generated. After you modify the Config.ini file, you will execute the MasterUpdate package using the MasterUpdateDefaultConfig.cmd batch file to load this new data, review the contents of the AuditEvents and the SalesFactErrorRows tables to determine the errors, and load a second batch that corrects for the errors that occur. Finally, you will process the Sales cube and then view the Sales cube in Analysis Manager to verify that the January data was added successfully.

Add new Time dimension members for January 1998 by modifying the UpdateTimeDim.cmd file and then executing the PopulateTimeDimension package

Switch to the UpdateTimeDim.cmd file in Notepad.

  1. Change the StartDate global variable value to 1/1/1998 and then change the EndDate global variable value to 2/1/1998 .

  2. Save the changes to the UpdateTimeDim.cmd batch file. Do not close this file in Notepad.

  3. Switch to the DataMovementApplication folder in Windows Explorer, and then double-click UpdateTimeDim.cmd.

  4. After this batch file completes its execution, switch to SQL Query Analyzer and then click New Query on the toolbar.

  5. In the query pane, type SELECT * FROM SBS_OLAP.dbo.TimeDim and then click Execute on the toolbar.

    Verify that 580 rows appear in the results pane. It should contain a row for each date between July 1, 1996, and January 31, 1998.

  6. Close the current query window without saving any changes, but do not close SQL Query Analyzer.

Now that you have added time records for January 1998 to the time dimension table, you are ready to modify the Config.ini file to point the UpdateSalesFacts subpackage to the delimited text file containing new sales data for January 1998.

Point the UpdateSalesFacts package to new sales data by modifying the Config.ini file

Switch to the Config.ini file in Notepad.

  1. In the [NewSalesData] section, change the NewSalesData key to C:\Microsoft Press\SQL DTS SBS\Ch10\ChapterFiles\Data\SalesData199801.txt .

  2. Save the modified Config.ini file. Do not close the Config.ini file in Notepad.

You modified the Config.ini file, so you are ready to execute the MasterUpdate package using the MasterUpdateDefaultConfig.cmd batch file. Execution of this package loads the sales data for January 1998 into the SalesFact table in the SBS_OLAP database, but it does not process that data into the Sales cube. This allows you to check for, and correct, errors that might occur before processing the data into the Sales cube.

Execute the MasterUpdate package using the MasterUpdateDefaultConfig.cmd file and then review the results

Switch to the DataMovementApplication folder in Windows Explorer and then double-click MasterUpdateDefaultConfig.cmd.

Executing the MasterUpdate package using the MasterUpdate.cmd batch file loads new sales data from the location specified in the Config.ini file into the SBS_OLAP database. No new product or customer data is loaded into the ProductDim and CustomerDim tables because the ProductStage and CustomerStage tables contain no data. No data is added to these tables during package execution because the keys in the Config.ini file for the UpdateProductDim and the UpdateCustomerDim packages do not point to valid delimited text files. However, the error caused by invalid delimited text files does not cause these packages to cease executing tasks because On Completion constraints are used after these steps rather than On Success constraints.

  1. After this batch file completes its execution, switch to SQL Query Analyzer.

  2. In the query pane, change the query to read SELECT * FROM SBS_OLAP.dbo.AuditEvents WHERE BatchID = 2 ORDER BY ExecutionDate and then click Execute on the toolbar.

    The LoadSalesStage step added 152 new sales records to the SalesStage table, but the LoadSalesFact step added only 149 rows to the SalesFact table. Notice that the UpdateProductDim and UpdateCustomerDim packages were unable to load any data from the delimited text files because the data files these packages attempted to load from did not exist. This failure is expected. Also, the Sales cube processing step was bypassed.

    click to expand

Now that you have added new sales data for January, you will view the data in the SalesFactErrorRows table to determine why three rows were not added to the SalesFact table. You will then correct the error and then add these error rows to the SalesFact table to ensure that the Sales cube will contain all sales data for January 1998.

Add missing dimension information and add the error rows to the SalesStage table and then to the SalesFact table

In SQL Query Analyzer, click New Query on the toolbar.

  1. In the query pane, type SELECT * FROM SBS_OLAP.dbo.SalesFactErrorRows and click Execute on the toolbar.

    The reason that three rows were not successfully inserted into the SalesFact table appears in the results pane. The ErrorReason column indicates that these rows were not inserted because the LACOR customer code was not present in the customer dimension table when the UpdateSalesFacts package attempted to insert sales data pertaining to this customer into the SalesFact table.

    click to expand
  2. On the toolbar, click Clear Window, type DELETE FROM SBS_OLAP.dbo.SalesStage WHERE CustomerCode != 'LACOR' and then click Execute on the toolbar.

    This script deletes the 149 rows of data in the SalesStage table that were successfully inserted into the SalesFact table. It leaves the three rows of new sales data in the SalesStage table that were not successfully inserted into the SalesFact table because the corresponding customer information was not added to the customer dimension table before the insert was attempted. The data movement application is designed so that it does not automatically delete data in the staging tables when the corresponding DTS package completes to enable you to easily resubmit error rows if necessary. Before you can resubmit these three error rows, you will point the UpdateCustomerDim package to the delimited text file containing the new data for the missing customer. The UpdateCustomerDim package will then load this information into the CustomerDim table.

  3. On the toolbar, click Load SQL Script, click No, navigate to C:\Microsoft Press\SQL DTS SBS\Ch10\ChapterFiles, and then double-click ModifyDeleteConfigs.sql.

    This script modifies configurations 3, 4, and 5 by configuring the Process Sales Cube step to be bypassed for each of these configurations. It also modifies configurations 4 and 5 by configuring the UpdateSalesFacts package to be bypassed for these configurations.

    click to expand
  4. On the toolbar, click Execute and then close this query window. Do not close SQL Query Analyzer.

  5. Switch to the DataMovementApplication folder in Windows Explorer, right-click DeleteAllStagingData.cmd and click Copy.

  6. Right-click an open area in the DataMovementApplication folder and then click Paste.

  7. Right-click Copy Of DeleteAllStagingData.cmd and then click Edit.

  8. Change the value of the giConfigID global variable from 3 to 4 , and save this batch file as DeleteProductStageData.cmd (make sure you change the file type to All Files).

  9. Change the value of the giConfigID global variable from 4 to 5 , save this batch file as DeleteCustomerStageData.cmd (make sure you change the file type to All Files), and then close this file in Notepad.

  10. Switch to the DataMovementApplication folder in Windows Explorer, and then double-click DeleteProductStageData.cmd.

  11. After this batch file completes its execution, double-click DeleteCustomerStageData.cmd.

  12. After this batch file completes its execution, switch to SQL Query Analyzer and then click Clear Window on the toolbar.

  13. In the query pane, type SELECT * FROM SBS_OLAP.dbo.AuditEvents WHERE BatchID > 2 ORDER BY ExecutionDate and then click Execute on the toolbar.

    Notice that the delete branch of the UpdateProductDim package executed as part of BatchID 3 and that the delete branch of the UpdateCustomerDim package executed as part of BatchID 4. The UpdateSalesFact package and the Process Sales Cube step was bypassed during each batch.

    click to expand
  14. Switch to the Config.ini file in Notepad.

  15. In the [Customers] section, change the NewCustomerData key to C:\Microsoft Press\SQL DTS SBS\Ch10\ChapterFiles\Data\NewCustData199801.txt .

    This text file contains the missing customer information for the new customer with a CustomerCode of LACOR.

  16. In the [NewSalesData] section, change the NewSalesData key to C:\Microsoft Press\SQL DTS SBS\Ch10\ChapterFiles\Data\NoNewSalesData.txt .

    This step is required to avoid adding the new sales data for January a second time from the same text file. You could also modify the UpdateSalesFacts package to include a bypass step to bypass loading new data from a delimited text file whenever this functionality was required.

  17. Save the modified Config.ini file. Do not close the Config.ini file in Notepad.

  18. Switch to the DataMovementApplication folder in Windows Explorer, and then double-click MasterUpdateDefaultConfig.cmd.

    The UpdateCustomerDim package adds new customer data to the CustomerStage table and then to the CustomerDim table. Thereafter, the UpdateSalesFacts table loads the three error rows remaining in the SalesStage table into the SalesFacts table.

  19. After this batch file completes its execution, switch to SQL Query Analyzer.

  20. In the query pane, modify the query to read SELECT * FROM SBS_OLAP.dbo.AuditEvents WHERE BatchID = 5 ORDER BY ExecutionDate and then click Execute on the toolbar.

    One new row was added to the CustomerStage table by the UpdateCustomerDim subpackage and three rows were added to the SalesFact table.

    click to expand

    Now that you have verified that all January sales data was successfully added to the SalesFacts table, you need to process the Sales cube.

  21. Switch to the DataMovementApplication folder in Windows Explorer and then double-click ProcessOnly.cmd.

  22. After this batch file completes its execution, switch to SQL Query Analyzer.

  23. In the query pane, modify the query to read SELECT * FROM SBS_OLAP.dbo.AuditEvents WHERE BatchID = 6 ORDER BY ExecutionDate and then click Execute on the toolbar.

    The Sales cube is successfully processed .

    click to expand
  24. Switch to the DataMovementApplication folder in Windows Explorer and then double-click DeleteAllStagingData.cmd.

    You must delete all staging data for January before you add new data for February.

Now that you have added all new sales data for January and processed the Sales cube, you are ready to browse the Sales cube to verify that the January data was added successfully.

Browse the Sales cube in Analysis Manager

Switch to Analysis Manager.

  1. In the Analysis Manager console tree, right-click Sales and click Refresh.

  2. In the details pane, drag the Time dimension and drop it just above the Bill Country field in the Customer dimension.

  3. Expand 1998 and then expand Quarter 1 to display the sales information for January 1998.

    click to expand
  4. In the Customer dimension list, click All Customer, expand All Customer, expand France, expand the region level, expand Versailles, and then click La Corne D Abondance.

    The sales made in January to the customer with the customer code of LACOR appear. The data movement application successfully added the error rows.

    click to expand

Now that you have verified that the Sales cube contains the January 1998 sales data, you will add data for February.

Add new Time dimension members for February 1998 by modifying the UpdateTimeDim.cmd file and then executing the PopulateTimeDimension package

Switch to the UpdateTimeDim.cmd file in Notepad.

  1. Change the StartDate global variable value to 2/1/1998 and then change the EndDate global variable value to 3/1/1998 .

  2. Save the changes to the UpdateTimeDim.cmd batch file and then close the UpdateTimeDim.cmd batch file in Notepad.

  3. Switch to the DataMovementApplication folder in Windows Explorer, and then double-click UpdateTimeDim.cmd.

You have added time records for February 1998 to the TimeDim table. Now you are ready to modify the Config.ini file so that it will point the UpdateSalesFacts subpackage to the delimited text file containing new sales data for February 1998.

Modify the Config.ini file

Switch to the Config.ini file in Notepad.

  1. In the [NewSalesData] section, change the NewSalesData key to C:\Microsoft Press\SQL DTS SBS\Ch10\ChapterFiles\Data\SalesData199802.txt .

  2. In the [Customers] section, change the NewCustomerData key to C:\Microsoft Press\SQL DTS SBS\Ch10\Data\NoNewCustomerData.txt .

    No new customer or product data is needed for February, and you do not want to add the LACOR customer again to the CustomerDim table because it would duplicate existing data in that table.

  3. Save the modified Config.ini file and then close the Config.ini file in Notepad.

Now that you have modified the Config.ini file, you are ready to execute the MasterUpdate package using the MasterUpdateDefaultConfig.cmd batch file, which will load the sales data for February into the SalesFact table in the SBS_OLAP database.

Execute the MasterUpdate package, review the results, process the Sales cube, and delete staging data

Switch to the DataMovementApplication folder in Windows Explorer, and then double-click MasterUpdateDefaultConfig.cmd.

The February 1998 sales data is added to the SalesStage table and then joined with the dimension data and added to the SalesFact table. No new data is loaded into the ProductDim and CustomerDim tables because no data exists in the ProductStage and CustomerStage tables. No data is added to the ProductStage and CustomerStage tables during package execution because the keys in the Config.ini file for the UpdateProductDim and the UpdateCustomerDim packages do not point to valid delimited text files.

  1. After this batch file completes its execution, switch to SQL Query Analyzer.

  2. In the query pane, change the query to read SELECT * FROM SBS_OLAP.dbo.AuditEvents WHERE BatchID = 8 ORDER BY ExecutionDate and then click Execute on the toolbar.

    The LoadSalesStage step added 122 new sales records to the SalesStage table, and the LoadSalesFact step added 122 rows to the SalesFact table. Since there are no new error rows, you are ready to process the Sales cube and delete all staging data.

    click to expand
  3. Switch to the DataMovementApplication in Windows Explorer, and then double-click ProcessOnly.cmd.

  4. After this batch file completes its execution, double-click DeleteAllStagingData.cmd.

  5. After this batch file completes, switch to SQL Query Analyzer.

  6. In the query pane, change the query to read SELECT * FROM SBS_OLAP.dbo.AuditEvents WHERE BatchID > 8 ORDER BY ExecutionDate and then click Execute on the toolbar.

    The Sales cube was processed during BatchID 9 and all staging data was deleted during BatchID 10.

    click to expand
  7. Close SQL Query Analyzer without saving any queries.

You have processed the Sales cube, and you are ready to browse the Sales cube and add more sales data as it becomes available.

Browse the Sales cube in Analysis Manager

Switch to Analysis Manager.

  1. In the Analysis Manager console tree, right-click Sales and then click Refresh.

  2. In the details pane, drag the Time dimension and drop it just above the Bill Country field in the Customer Dimension.

  3. Expand 1998 and then expand Quarter 1.

    The sales information for January and February 1998 appears in the Sales cube.

    click to expand

You are now ready to add data for additional months and operate the data movement application on your own. Additional sales data for March, April, and May is located in the C:\Microsoft Press\SQL DTS SBS\Ch10\ChapterFiles\Data folder. Have fun!




Microsoft SQL Server 2000 DTS Step by Step
Microsoft SQL Server 2000 DTS Step by Step (Step by Step (Microsoft))
ISBN: 0735619166
EAN: 2147483647
Year: 2003
Pages: 87
Authors: Carl Rabeler

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net