Optimizing Microsoft SQL Server Analysis Services: Optimization Tools: Incremental Update Wizard
15. Keeping all except the bottom two rows of the table highlighted, push the DELETE button on the keyboard.
A message box appears, bearing a warning, and asking for confirmation, as shown in Figure 14.
Figure 14: Confirmation of the Delete Requested
16. Click OK.
The rows we have highlighted are deleted as the message box closes. We now see the two unselected rows as all that remain in our new table.
17. Change the amount from $210.00 to $9,789.00 in the populated row.
The table appears as depicted in Figure 15, with the change.
Figure 15: The Table with Amount Change
18. Select File —> Close to close the table.
A confirmation dialog appears, asking if we wish to save changes to the table design, as depicted in Figure 16.
Figure 16: Confirmation Dialog – Click OK
19. Click Yes.
The dialog closes and we return to the table view. We now have a source that we can easily distinguish as separate for purposes of an Incremental Update. Recall that we did not change any information, other than the amount, leaving keys in place from the original fact table. This was for a couple of reasons, mainly because it would have been more work to change the dimension table involved, to add a new year or month, so I simply set up an intentional replica of the last entry to the already existing fact table, changing the amount to make it easy to detect in the updated cube.
20. Close the FoodMart 2000 database.
21. Close MS Access.
With our preparation complete, let’s get to the Incremental Update.
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 data from which the cube was created has changed.
Incremental updates have no effect upon the data that has already been processed, and 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. But it is important to understand the special precautions related to data integrity that apply to multiple-partition cubes, and to take these precautions into consideration 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 that the Wizard simply coaxes from us, as we shall see in the following steps.
1. Right-click the new IncrUpdate cube.
2. Select Process from the context menu that appears, as we did in the preparation step earlier.
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 in our earlier preparation.
3. Ensure that the radio button to the left of Incremental Update is selected.
The Process a Cube — Select the processing method dialog appears, as depicted in Figure 17.
Figure 17: The Select the Processing Method Dialog