Next, run the script that Listing 8, page 30, shows in a Query Analyzer connection to the destination database to create the usp_Update_Products_type2 stored procedure, which I'll describe shortly. Move to the Queries tab and use the information that Table 3, page 30, shows to fill in the parameterized queries and map the parameters to the destination recordset's columns. The Insert query inserts into the Products_type2 table a new row that includes an effective_date column. The Delete query sets to 1 the discontinued value of the product that was deleted from the OLTP system.
The Update query has a more complex task. You implement it through the stored procedure usp_Update_Products_type2, which the code in Listing 8 created. The stored procedure first checks whether the package changed (@package <> -1). If so, the stored procedure uses an UPDATE statement to set the to_date of the most recent product version to the current effective date, then uses an INSERT statement to generate a new version of the product. If the package didn't change, the product name must have changed, so the stored procedure uses an UPDATE query to overwrite the existing product name in all product versions. Remember that we're using Type 1 handling for all product names in this scenario.
Now you're done implementing the refresh process for the Type 2 transformation. Click OK, then open the Type3 Refresh DDQ task's Properties dialog box. Again, fill in the properties on the Source and Bindings tabs with the same settings you used in the Type1 and Type2 Refresh DDQ tasks, except that the destination table is Products_type3. On the Transformations tab, create an ActiveX transformation according to the mappings that appear in Figure 4.
In the ActiveX Script Transformation Properties dialog box, enter the same code you used in Listing 7's Type 2 transformation. Run the script from Listing 9, page 32, in a Query Analyzer connection to the destination database to create the usp_Update_Products_type3 stored procedure, which I'll describe shortly. On the Queries tab, use the information from Table 4, page 32, to fill in the parameterized queries and map the parameters to the destination recordset's columns.
Note that the Update query in the Type 3 transformation also executes a stored procedure—in this case, usp_Update_Products_type3. This stored procedure first checks whether the package changed. If so, the stored procedure uses an UPDATE statement to overwrite the productname value and, in FIFO order, "pushes" the package and effective date values and updates the new ones. If the package didn't change, an UPDATE statement overwrites only the existing product name.
Click OK, then save and run the package; make sure that all the tasks complete successfully. Each of the TypeN Refresh tasks should have processed five transactions. Execute the package again to make sure that no transactions are processed if no transactions took place after the previous run. Issue a SELECT * query against the Prod_log table in a Query Analyzer window to verify that the Prod_log table is empty. Now issue SELECT * queries against the three dimension tables and examine their contents. Tables 5, 6, and 7 show the contents of the three dimension tables at this point. You can see that Type 1 changes overwrote original values, Type 2 changes added dimension rows, and Type 3 changes pushed attributes to the right.
Next, issue a couple more modifications against the Products table:
INSERT INTO Products(productid, productname, package)
VALUES(104, 'prod4', 50)
UPDATE Products
SET package = 40
WHERE productid = 103
Execute the DTS package again and reexamine the dimension tables to verify that all the refresh types ran correctly.
Final Touches
Generally, you'd create a SQL Agent job to run the DTS package that implements the refresh process on a nightly schedule to apply changes that occurred in the source system to the data warehouse. Row-at-a-time processing doesn't yield good performance when it has to handle a very large number of rows. In that case, you need a set-based solution. However, in this case, although the refresh processes work on a row-at-a-time basis, the package should complete its run quickly. Each night, the refresh works on a relatively small number of rows—one day's worth of transactions.
DTS is a powerful and fascinating transformation tool, but as with any other programming tool, you—the designer and programmer—are the key to the project's success. Learning more about how DTS works can give you an edge in designing and implementing new projects.
End of Article
Prev. page
1
2
3
4
[5]
next page -->
