an Incremental Update with the New Cube
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.
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.
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.
new IncrUpdate cube.
from the context menu that appears, as we did in the preparation step earlier.
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 radio button to the left of Incremental Update is selected.
a Cube - Select the processing method dialog appears, as depicted in Illustration
Illustration 17: The Select
the Processing Method Dialog
Update Wizard - Welcome dialog appears, as shown in Illustration 18.
Illustration 18: The Incremental
Update Wizard - Welcome Dialog
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.
Click the Change...
button to the right of the Fact table box (currently occupied by expense_fact).
a Fact Table dialog appears.
PostAdditions table from the tables displayed on the left, as we did in the
preparation step earlier.
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.
Illustration 19: The
Choose a Fact Table Dialog, with Our Selection
to accept the selection.
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.
Illustration 20: The Specify
the Data Source Dialog
arrive at the Create a filter expression dialog of the wizard, as shown
in Illustration 21.
Illustration 21: The Create
a Filter Expression Dialog
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).
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.
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 of the Incremental Update Wizard appears, as depicted in Illustration
Illustration 22: The Finish
to complete the steps of the Incremental Update Wizard.
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.
Illustration 23: The Incremental
Update Completes Successfully
Viewer closes, and we arrive at Analysis Manager once again.