Maintaining SQL Server 2000 Analysis Services Systems

Microsoft SQL Server 2000 Analysis Services allows you to ensure data integrity by archiving and restoring OLAP cubes. Another key Analysis Services data integrity task you should perform is to process the OLAP cubes associated with a database whenever you update that database’s data or structure so that the cubes reflect the changes.

Archiving and Restoring OLAP Cubes

To archive an Analysis Services database, right-click a database icon in the Analysis Manager window and then click Archive Database. Provide the archive settings in the Archive Database dialog box, and then click Archive to archive the database.

To restore an archived database, right-click a server icon in the Analysis Manager window and then click Restore Database. Select the archive database file, and then click Open to restore the file.

Processing OLAP Cubes

Analysis Services creates OLAP cubes from source data. These cubes contain summarized data, fact tables, and dimension tables that depend on the source data’s specific structure. Changes to the structure of your source data affect the integrity and accuracy of the source data’s associated cubes. Because Analysis Services provides continuous access to cubes, any changes to an underlying data source must be made with the integrity of the cubes in mind. Many of the changes you make to cube structure within Analysis Manager and all the changes to an OLAP cube’s underlying source data require the cube to be updated so that the changes can be reflected in the cube’s data.

You can update OLAP cubes in the following ways:

• An incremental update appends data in an OLAP cube but does not update the OLAP cube’s summarized data. This update method does not process changes to a cube’s structure or make changes to its existing source data. You should use this method only if you want to keep your data current.

• A refresh data update clears and reloads an OLAP cube’s data and recalculates its summarized data. You should use this method if the OLAP cube’s source data has changed but its structure has not.

• A full process update completely restructures an OLAP cube based on its current definition and then recalculates its data.

To manually process an OLAP cube, right-click an OLAP cube icon in the Analysis Manager window, click Process, select an update processing method, and then click OK. If you select an incremental update, provide any additional information required by the Incremental Update Wizard.

To automate OLAP cube processing, you must create a recurring SQL Server 2000 scheduled Data Transformation Services task as follows:

1. Open SQL Server 2000 Enterprise Manager. (On the Start menu, point to Programs, point to Microsoft SQL Server, and then click Enterprise Manager.)

2. Expand the server icon for the SQL Server on which you want to automate the OLAP cube processing task.

3. Right-click the server’s Data Transformation Services folder, and then click New Package.

4. In the DTS Package window, on the Task menu, click Analysis Services Processing Task.

5. In the Analysis Services Processing Task dialog box, select the cube that you want to process. In the right pane, click the update process type and then click OK.

6. On the Package menu, click Save, provide a package name of your choice, and then click OK.

7. In the Enterprise Manager window, in the Data Transformation Services folder, click the Local Packages icon.

8. Right-click your package icon, customize the settings in the Edit Recurring Job Schedule dialog box, and then click OK to begin the automatic OLAP cube processing schedule.