Introduction to MSSQL Server 2000 Analysis Services: Performing Incremental Cube Updates - An Introduction - Page 5

Procedure

Perform an Incremental Update with the New Cube

As we learned earlier, we are using the incremental update to append new data, from the data warehouse or mart, to one of our cube's partitions, as well as to update aggregations. Incremental updates are not appropriate, or even an option (as we have seen), if the cube's structure has changed, or if the structural data from which the cube was created has changed.

Incremental updates have no effect upon the data that has already been processed, and which is already at home in the cube. An incremental update can be performed while users continue their work querying the cube, and, as another convenience, users will have access to the additional data without having to reconnect, after the update has completed.

Because an Incremental Update creates a temporary partition from the new data, and merges it into an existing partition, it is necessary to understand the various considerations that apply to partitions before performing an Incremental Update. This can be especially important if the cube contains multiple partitions: we are required to specify the partition into which the temporary partition is merged in such cases.

The cube in our practice exercise contains only one partition, so the temporary partition is merged into that partition. However, it is important to understand the special precautions related to data integrity that apply to multiple-partition cubes, and to consider these precautions before we perform an incremental update on any cube. For more information, see the MSAS Books Online.

We can begin the Incremental Update process of our new cube with a few basic steps, using the Incremental Update Wizard. The Wizard makes it a breeze to perform an Incremental Update, but skill becomes the focus with some of the settings (which the Wizard simply coaxes from us), as we shall see in the following steps.

The Process a Cube - Select the processing method dialog appears, defaulted to Incremental update. We note that all processing options are available to us, now that the cube has been processed once (and "registered," with status updated) in our earlier preparation.

The Process a Cube - Select the processing method dialog appears, as depicted in Illustration 17.

The Incremental Update Wizard - Welcome dialog appears, as shown in Illustration 18.

The Specify the Data Source dialog of the Incremental Update Wizard appears next. Here we specify the location of the "new" data that we will be adding. We isolate "new" values to prevent double-counting those that are already included in the existing cube, through two approaches. We can either filter the fact table that supports the existing cube (for example, to include recently added rows in the common fact table), or we can point the wizard to a different table entirely as the source for the new data.

The Choose a Fact Table dialog appears.

Clicking the PostAdditions table brings its constituent columns into the Details section on the right of the Choose a Fact Table dialog, which appears as depicted in Illustration 19.

We return to the Specify the Data Source dialog of the Incremental Update Wizard, where we see our selection in the Fact table box, as depicted in Illustration 20.

We next arrive at the Create a filter expression dialog of the wizard, as shown in Illustration 21.

Were we using the same fact table (in our case, expense_fact) as that used as the source for an existing cube, we could filter all except the new data that we wished to include in the incremental update (and thus avoid double-counting of data already summarized in the processed cube).

An example of how I have used this capability, within client implementations of the past has been to filter on a designated "status flag" column in the rows of a fact table. In this and similar approaches, I have (as part of mart / warehouse development) designed the flag to be changed to indicate a "processed" status, once it has driven the selection of the row for an update cycle. Modification of the status therefore "marks" processed rows, to ensure that they are filtered out of prospective incremental update cycles). Potential other nuances within the Create a filter expression dialog have proven to be virtually unlimited, in my experience.

We might also use the filter to further refine a selection in another table. We do not need the filter for our simple example source, so we will continue to the next dialog.

The Finish dialog of the Incremental Update Wizard appears, as depicted in Illustration 22.

Processing begins, and runs, as evidenced by the Process viewer's presentation of processing log events in real time (just as we see for Full Process operations). Processing ends rather quickly, and the success of the evolution is indicated by the appearance of the Incremental update completed successfully message (in green letters) at the bottom of the viewer, as shown in Illustration 23.

The Process Viewer closes, and we arrive at Analysis Manager once again.