How to Run the Report Preparation DTS Task

For the latest version of Commerce Server 2007 Help, see the Microsoft Web site.

You use the Report Preparation Data Transformation Services (DTS) task to populate the online analytical processing (OLAP) cubes with the data in the Data Warehouse. Commerce Server uses OLAP cubes to organize the summarized data in the Data Warehouse so that report generation is completed faster. You must run the Report Preparation DTS task every time that you import data into or delete data from the Data Warehouse. For information about OLAP cubes, see SQL Server Books Online.

If multiple sites share a Data Warehouse, run the Report Preparation task one time to process data for all the sites. When it is run, the Report Preparation task processes all the sites in the Data Warehouse. Do not run the Report Preparation DTS task separately for each site. It will affect performance of the Data Warehouse.

The first time that you run the Report Preparation task to populate the OLAP cubes in the Data Warehouse, you must select full cube processing. After you run the Report Preparation task, you can perform incremental cube processing to load only new data into the OLAP cubes.

You must also select full cube processing after you delete any data from the Data Warehouse. For information about how to delete data from the Data Warehouse, see How to Run the Data Deletion DTS Task.

Even if you run the Report Preparation DTS task in incremental mode, it might still display an increasing time-to-completion because it must perform full processing on the cubes listed in the following table.

Cube

Processing mode considerations

Page Usage

Contains the URI dimension. This is an always full process dimension.

Entry Pages

Contains the URI dimension. This is an always full process dimension.

Exit Pages

Contains the URI dimension. This is an always full process dimension.

mscsSales

Contains facts that can change. Therefore, the cube cannot be incrementally processed.

Basket Events

Contains facts that can change. Therefore, the cube cannot be incrementally processed.

Buyer Visits

Contains facts that can change. Therefore, the cube cannot be incrementally processed.

The Report Preparation DTS task does not work in a clustered OLAP environment, especially if a retry error or a failover occurs. The Report Preparation DTS task might report errors when you process OLAP dimensions and cubes if you did not import the information that is required to populate those objects into the Data Warehouse. The error message displays in the following format:

     Processing error [Source data contains no rows] <object name>

If you import a subset of data into the Data Warehouse, for example, you are not running the Catalog data import DTS task, you might want to disable the processing of objects for which there will be no data. This prevents the "Source data contains no rows" error messages.

To disable processing of Analysis Services objects, set the ProcessingEnabled flag to 0 in the CubeProcInfo and DimProcInfo tables for the cubes and dimensions you do not want to process.

Note

If you are using SQL Server 2005, you must install DTS 2000 Designer and run the Data Warehouse Import Wizard to create a package. You must have already created a package using the Data Warehouse Import Wizard or have existing packages to import before you can perform this task.

To run the Report Preparation DTS task

  1. Run the Configuration synchronization DTS task to synchronize your site configuration with the Data Warehouse. For instructions, see How to Run the Configuration Synchronization DTS Task.

  2. If you are using SQL Server 2005, do the following:

    1. Click Start, point to Programs, point to Microsoft SQL Server 2005, and then click SQL Server Management Studio.

    2. In SQL Server Management Studio, in the Object Explorer pane, expand the server on which your Data Warehouse is installed, expand Management, and then expand Legacy.

    3. Right-click the package that contains the task that you want to run, and then click Open.

      If the package that you want to open is not in the list, right-click Data Transformation Services, and then click Open Package File or Import Package File. In the Open DTS 2000 Package or Import DTS 2000 Package dialog box, click the package that you have previously saved and want to open or import, and then click Open.

  3. If you are using SQL Server 2000, do the following:

    1. Click Start, point to Programs, point to Microsoft SQL Server, and then click Enterprise Manager.

    2. In the SQL Server Enterprise Manager console, in the left pane, expand Microsoft SQL Servers, expand SQL Server Group, and then expand the server on which your Data Warehouse is installed.

    3. Right-click Data Transformation Services, and then click New Package.

      Alternatively, if you are changing an existing package, right-click Data Transformation Services, and then click Open Package. In the Select File dialog box, click the package you want to change, and then click Open.

  4. In the DTS Package or DTS 2000 Package Designer window, on the Task menu, click Report preparation (Commerce Server).

  5. In the Prepare Reports Properties dialog box, do the following:

    Use this

    To do this

    Description

    Type a description of the Report preparation task. The maximum number of characters for the description is 255.

    Site name

    Select the site for which you are populating the OLAP cubes.

    Incremental

    Select to process the OLAP cubes incrementally.

    Full

    Select to process the OLAP cubes in their entirety.

    Number of retries

    Type the number of times you want the task to retry connection failures to the Data Warehouse. The default number of retry attempts is 10.

    Seconds between retries

    Type the number of seconds you want the task to wait between retry attempts. The default time interval is 30 seconds.

  6. In the Prepare Reports Properties dialog box, click OK.

  7. On the Package menu, click Execute.

Note

Clicking Execute on the Package menu will execute all tasks associated with the selected package. If you want to execute a specific task, for example the report preparation task, right-click Report preparation (Commerce Server), and then click Execute Step.

The Executing Package dialog box appears that displays the progress of the import.

See Also

Other Resources

How to Run the Configuration Synchronization DTS Task

Running the DTS Tasks

Importing Data into the Data Warehouse

Managing the Data Warehouse

Commerce Server Operations