Creating the SalesCubeProcessing Subpackage

You can add the Analysis Services Processing task to a DTS package to process Analysis Services dimensions, cubes, and partitions from SQL Server dimension and fact tables. Processing a cube, dimension, or partition adds data to or replaces data in the object being processed using the data in the underlying relational database. You can define this task to perform full or incremental processing, or perform a complete data refresh. You can use an ActiveX Script task to dynamically specify the type of processing at run time based on a global variable value that modifies the ProcessOption property of the Analysis Services Processing task. For instance, if only new data is being added and the dimension structure of the cube is not changing, you might want to perform only an incremental processing of the cube or partition. For an example of such an ActiveX Script task, see Changing Properties of an Analysis Services Processing Task in SQL Server Books Online.

The Analysis Services Processing task is written using Microsoft Visual Basic 6. Because Visual Basic 6 does not support the free-threaded threading model, the Analysis Services Processing task must execute either on the main execution thread for a package or in a separate process space in which a new thread of execution is established using the Execute Process task. If the Analysis Services Processing task executes in a separate process space, several Analysis Services Processing tasks can execute in parallel. Choosing among processing options, executing multiple Analysis Services Processing tasks in parallel, and designing the data movement application to dynamically set different processing options at run time are beyond the scope of this book and this simple data movement application. For a complete example of a data movement solution that uses DTS to dynamically process Analysis Services objects based on the objects that require processing, and that processes objects in parallel using the Execute Process task, see the Microsoft SQL Server Accelerator for Business Intelligence, which can be downloaded at http://www.microsoft.com/solutions/bi/ .

Note

You must have administrator access to an Analysis Services instance to continue with the remaining procedures in this chapter. This instance can be on your local computer or on a remote computer. The following steps assume the Analysis Services is installed on your local computer. If it is not, restore the SQL DTS SQL.cab file to the remote computer, point the restored SQL DTS SBS database to the SBS_OLAP database on your local SQL Server instance, and then point the Analysis Services Processing task to the Analysis Services instance on the remote computer.

Restoring the Analysis Services Database and Adding an Analysis Services Processing Task

In the following procedures, you will begin by restoring an Analysis Services database from a .CAB file. This Analysis Services database contains the structure for the Sales cube, which is based on the schema you have been creating in the SBS_OLAP database. You will then create an Analysis Services Processing task in the MasterUpdate package to fully process the Sales cube, adding data from the SBS_OLAP database into Sales cube.

Restoring the SQL DTS SBS database to an Analysis Services instance

Click Start, point to All Programs, point to Microsoft SQL Server, point to Analysis Services, and then click Analysis Manager.

In the Analysis Manager console tree, expand Analysis Servers, and then click your Analysis Services instance to establish a connection to Analysis Services.
Right-click the Analysis Services instance in the console tree and click Restore Database.
In the Look In list, navigate to C:\Microsoft Press\SQL DTS SBS\Ch9\ChapterFiles, and then double-click SQL DTS SBS.CAB.
In the Restore Database dialog box, click Restore, and then click Close when the Analysis Services database has been restored.
Expand the SQL DTS SBS database, and then expand Data Sources.
Right-click SBS_OLAP and then click Edit to review the Data Link Properties.

The Sales cube and its dimensions in the SQL DTS SBS database are configured to connect to the SBS_OLAP database in the local SQL Server instance. Change the data link properties if you are using a remote Analysis Services instance to point to the appropriate SQL Server instance.
Click OK.
Expand Cubes in the console tree, click the Sales cube, and then click the Data tab in the details pane.

Notice that you cannot browse the data in the Sales cube because it is not processed.

Now that you restored the SQL DTS SBS database, you are ready to add an Analysis Services Processing task to the MasterUpdate package and process the Sales cube.

Processing the Sales cube by adding an Analysis Services Processing task to the MasterUpdate Package

Switch to SQL Server Enterprise Manager, and then right-click Data Transformation Services in your local instance.

Click Open Package and then open the most recent version of the MasterUpdate package in the C:\Microsoft Press\SQL DTS SBS\DataMovementApplication folder using a password of mypassword .
On the Task menu, click Analysis Services Processing Task.
In the Name box, change the name of this task to ProcessSalesCube and change the Description of this task to Process Sales Cube .

Notice that this task allows you to set both the task name and description when you create the task rather than just the description.
Select the Local Server check box.

When you select this check box, this task will always try to connect to an Analysis Services instance on the computer on which the MasterUpdate package is executing. By selecting this check box, you will be able to migrate the data movement application from the development environment to the production environment without editing the Process Sales Cube step in the MasterUpdate package.
In the Select The Object To Process list, expand LOCALHOST, and then click SQL DTS SBS.
In the Select A Processing Option box, click Full Process, and then click OK.
Right-click an open area of the design sheet and click Disconnected Edit.
In the left pane, expand Steps and then click DTSStep_DTSOlapProcess.Task_1.
In the right pane, double-click Name in the Property Name column, change the name of this step to ProcessSalesCube , click OK, and then click Close.

Now that you have added the Process Sales Cube step to the MasterUpdate package, you will add a bypass step to enable you to execute the MasterUpdate package without processing the Sales cube.

Adding Step Bypass and Logging Steps

In the following procedures, you will add a step to enable you to bypass the Process Sales Cube step and then add a logging step to record in the AuditEvents table if the Process Sales Cube step is bypassed for a particular execution of the MasterUpdate package. You will also add logging steps to record the success or failure of the Process Sales Cube step.

Bypass the Process Sales Cube step by adding an ActiveX Script task

On the Task menu, click ActiveX Script Task, and then type Bypass Process Sales Cube in the Description box.

Click Browse, navigate to C:\Microsoft Press\SQL DTS SBS\Ch9\ChapterFiles, and then double-click BypassProcessSalesCube.bas.

This script is the very similar to the previous scripts you used to create branching tasks.
Click OK to save this ActiveX Script task.
On the design sheet, right-click an open area and then click Package Properties.
On the Global Variables tab, type gbProcessBypass in the Name column, select Boolean in the Type column, type in the Value column, and then click OK.
Right-click an open area of the design sheet and click Disconnected Edit.
In the left pane, expand Steps and then click DTSStep_DTSActiveScriptTask_5.
In the right pane, double-click Name in the Property Name column, change the name of this step to LogProcessSalesCubeBypass , click OK, and then click Close.

Now that you have created the Bypass Process Sales Cube step and the associated global variable, you will add a step that logs the bypass of the Process Sales Cube step.

Log the bypass of the Process Sales Cube step by creating an Execute SQL task

On the Task menu, click Execute SQL Task, type Log Process Sales Cube Bypass in the Description box, and then click Browse.

In the Look In list, navigate to C:\Microsoft Press\SQL DTS SBS\Ch9\ChapterFiles, and then double-click LogProcessSalesCubeBypassed.sql.

This script inserts logging information into the AuditEvents table to document when the Process Sales Cube step is bypassed.
Click Parameters, select giBatchID in the Input Global Variables list, and then click OK.
Click OK to save this Execute SQL task.

You have created a step that will create a log entry in the AuditEvents table when the Process Sales Cube step is bypassed. Next you will create steps to log the success or failure of the Process Sales Cube step.

Log the success or failure of the Process Sales Cube step by adding Execute SQL tasks

On the Task menu, click Execute SQL Task.

Type Log Process Sales Cube Success in the Description box and then click Browse.
In the Look In list, navigate to C:\Microsoft Press\SQL DTS SBS\Ch9\ChapterFiles and then double-click LogProcessSalesCubeSuccess.sql.
Click Parameters, select giBatchID in the Input Global Variables list for Parameter 1, and then click OK.
Click OK to save the Execute SQL task.
On the Task menu, click Execute SQL Task.
Type Log Process Sales Cube Failure in the Description box and then click Browse.
In the Look In list, navigate to C:\Microsoft Press\SQL DTS SBS\Ch9\ChapterFiles and then double-click LogProcessSalesCubeFailure.sql.
Click Parameters, select giBatchID in the Input Global Variables list for Parameter 1, and then click OK.
Click OK to save the Execute SQL task.

Now that you have finished adding the Sales cube processing steps to this package, you will ensure that these steps execute in the proper order by adding precedence constraints.

Ensuring Execution Order and Testing the New Steps

In the following procedures, you will add precedence constraints to ensure that these new steps execute in the proper order. Next you will create a new configuration of global variables in the PackageGVs table that will enable you to execute the Process Sales Cube step in the MasterUpdate package while bypassing each of the subpackages. You will then create a new batch file and execute the MasterUpdate package to process the Sales cube. Finally, you will browse the Sales cube to verify that it was processed successfully.

Ensure execution order by adding precedence constraints

On the design sheet, arrange the steps according to their execution flow.

On the design sheet, click the Call UpdateSalesFacts step, and then hold down the Ctrl key and click the Bypass Process Sales Cube step.
On the Workflow menu, click On Completion.
On the design sheet, click the Bypass Process Sales Cube step, and then hold down the Ctrl key and click the Process Sales Cube step.
On the Workflow menu, click On Success.
On the design sheet, click the Bypass Process Sales Cube step, and then hold down the Ctrl key and click the Log Process Sales Cube Bypass step.
On the Workflow menu, click On Success.
On the design sheet, click the Process Sales Cube step, and then hold down the Ctrl key and click the Log Process Sales Cube Success step.
On the Workflow menu, click On Success.
On the design sheet, click the Process Sales Cube step, and then hold down the Ctrl key and click the Log Process Sales Cube Failure step.
On the Workflow menu, click On Failure.
On the toolbar, click Save and then close the MasterUpdate package in DTS Designer.
Close SQL Server Enterprise Manager.

Now that you have created these precedence constraints, you will create a new configuration of global variables to enable you to execute the Process Sales Cube step in the MasterUpdate package while bypassing the steps in each of the subpackages.

Create a new configuration of global variables in the PackageGVs table and then create a new batch file

Switch to SQL Query Analyzer and then click New Query on the toolbar.

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

This script adds a record to the default configuration in the PackageGVs table, disabling the Process Sales Cube step. It then creates a new configuration in the PackageGVs table that bypasses each subpackage and executes the Process Sales Cube step in the MasterUpdate package.
On the toolbar, click Execute to create these entries in the PackageGVs table and then close this query in SQL Query Analyzer. Do not close SQL Query Analyzer.
Using Windows Explorer, navigate to the C:\Microsoft Press\SQL DTS SBS\DataMovementApplication folder, right-click Config2.cmd, and click Edit.
Change the value of the giConfigID parameter from 2 to 6 and then click Save As on the File menu.
Type ProcessOnly.cmd in the File Name box, select All Files in the Save As Type list, and then click Save. Close Notepad.

Now that you have created these configurations, you are ready to test the execution of the data movement application.

Test the execution of the data movement application

Using Windows Explorer, navigate to C:\Microsoft Press\SQL DTS SBS\DataMovementApplication and then double-click ProcessOnly.cmd.

After the ProcessOnly.cmd batch file completes its execution, switch to SQL Query Analyzer.
In the query window, change the query to read SELECT * FROM SBS_OLAP.dbo.AuditEvents WHERE BatchID = 10 ORDER BY ExecutionDate and then click Execute on the toolbar.

Notice that each of the subpackages were bypassed and then the Process Sales Cube step executed successfully.
Close SQL Query Analyzer, without saving any script changes, and then switch to Analysis Manager.
In the console tree, right-click Sales in the Cubes node and then click Refresh.

The data that the Analysis Services Processing task loaded into the Sales cube is now visible for browsing.
Close Analysis Manager.

You have now successfully completed your prototype of a data movement application. In the next chapter, you will learn to use the data movement application to load monthly sales data into the Analysis Services cube and use the global variables and initialization file to control its execution without opening or editing any of the packages in the data movement application.