Chapter 9: Completing the Data Movement Application

Overview

In this chapter, you will learn how to:

• Create a starter DTS package

• Add a fact table subpackage

• Add an Analysis Services Processing task to a package

In this chapter, you will complete your prototype of a data movement application. You will begin by creating a DTS package (the StarterDTS package) that is based on the subpackages you have already created. You can use this package as a template for all future packages that you create for the data movement application. This template package will include the initial configuration steps for dynamically setting package properties at run time. You will then use this template package as the starting point for creating the UpdateSalesFacts subpackage. This package will load sales data from a delimited text file into the SalesStage table and then load data from the SalesStage table into the SalesFact table, joining it with data from the ProductDim , CustomerDim , and TimeDim tables. The UpdateSalesFacts subpackage will be called by the MasterUpdate package after all dimension subpackages have completed executing. You will also add appropriate package bypass, delete data, and logging steps to this package.

You will complete your prototype of the data movement application by adding an Analysis Services Processing task to the MasterUpdate package. This task will execute after all dimension and fact subpackages have executed. It will process the data in the dimension and fact tables in the SBS_OLAP relational database into a multidimensional cube that you will restore to an instance of Analysis Services. You will also add a bypass step to the MasterUpdate package to enable you to execute the MasterUpdate package without calling the Analysis Services Processing task. Finally, you will add a logging task to the MasterUpdate package to create a log entry in the AuditEvents table that documents when the Analysis Services Processing task is bypassed.