Updating and Refreshing Cube Data

Many changes you make within the OLAP Manager and all changes to a cube’s source data require the cube to be processed in order for the changes to be reflected in the cube’s data.

You can process a cube in three ways:

• Incremental update

  Adds new data to a partition in the cube and updates aggregations. This method does not process changes to a cube’s structure (measures, dimensions, and so on) or changes to its existing source data. An incremental update creates a temporary partition from the new data and merges it into an existing partition.

• Refresh data

  Clears and reloads a cube’s data and recalculates its aggregations. Use this method if the cube’s source data has changed but its structure has not.

• Complete process

  Completely restructures a cube based on its current definitions and then recalculates its data.

For more information about the kinds of changes processed by the preceding methods, see Processing Cubes.

The remainder of this topic describes procedures for only the first two of the preceding methods. For more information about the third method and associated procedures, see Processing Cubes Using the OLAP Manager.

Referential integrity of the data warehouse is not verified by Microsoft® SQL Server™ OLAP Services. So, for example, if the cube’s (or one of its partition’s) fact table contains foreign key values that are not present in a joined dimension table’s primary key column, the rows containing those values are not processed. In this case, processing does not produce an error message, but the cube contains incomplete and therefore inaccurate data.

To incrementally update a cube or refresh a cube’s data, use the Process a Cube and Process dialog boxes.

To incrementally update a cube

Caution This procedure updates a partition. Incorrect use of partitions can result in inaccurate cube data. For more information, see Partitions and its subtopics.

1. In the OLAP Manager tree view, under a database, expand the Cubes folder.
2. Right-click the cube, and then click Process.
3. In the Process a Cube dialog box, click Incremental update, and then click OK.
4. In the Incremental Update wizard:
   1. In the Welcome step, click Next.
   2. If the cube contains multiple partitions, the Select a partition to update step appears. In the Partition box, select the partition to update, and then click Next.
   3. In the Data source box, select the data source that contains the data to add to the partition. You can select the same data source used by the partition or a different one. By default the same data source used by the partition is initially displayed. To select a different data source, click Change, select the data source, and then click OK. If you select a different data source, it must contain a fact table with the same structure and columns as the partition’s fact table, and it must contain dimension tables with the same structure and columns as the partition’s dimension tables.
   4. In the Fact table box, select the table that contains the data to add to the partition. You can select the partition’s fact table or a different table. By default the partition’s fact table is initially displayed. If you select this table, you must use a filter, as described below, to ensure that only data not already in the partition is added. To select a different table, click Change, select the table, and then click OK. If you select a different table, it must have the same structure and columns as the partition’s fact table. You must also manually merge the table with the partition’s fact table after the incremental update completes. For more information, see Fact Table Considerations when Merging Partitions. Click Next.
   5. Specify a filter (WHERE clause expression) to limit the data selected from the fact table and added to the partition. A filter is required if you select the partition's fact table as the fact table for the incremental update (that is, if you select the default fact table). For more information, see Filters. Click Next.
   6. Click Finish.
5. In the Process dialog box, wait for the incremental update to finish processing, or click Stop to halt and cancel processing.

To refresh a cube’s data

1. In the OLAP Manager tree view, under a database, expand the Cubes folder.
2. Right-click the cube, and then click Process.
3. In the Process a Cube dialog box, click Refresh data, and then click OK.
4. In the Process dialog box, wait for the data refresh to finish processing, or click Stop to halt and cancel processing.

After processing completes but before you close the Process dialog box, you can view the SQL statement used to incrementally update or refresh the cube. This statement includes any filter specified in the Incremental Update wizard.

To view an SQL statement

1. In the Process dialog box, click a line beginning with the SQL icon.
2. Click View Details.