Sample Data to Act as the "Add Target" in the Incremental Database
will "go to the source," (for our sample cubes, at least), the FoodMart
2000 MS Access database, for the purposes of creating a basic table, from
which we can illustrate the operation of an Incremental Update using the
Incremental Update Wizard in MSAS. While we could certainly source the
existing fact table (with a filter to restrict all except the correct records
into our Incremental Update process) for the "new" information
we wished to add to our existing cube, we will create a simple additional table
for that purpose, mainly to prevent causing any harm to the existing FoodMart
the FoodMart 2000.mdb file, via Windows Explorer, MS Access, or other
2000 database is typically installed in the Program directory for
MSAS, in the Samples folder. An example path, assuming installation of
MSAS on the C: drive would appear as follows:
C:\Program Files\Microsoft Analysis Services\Samples
Open the FoodMart
2000.mdb in MS Access (I am using Access 2003 in my illustrations, but Access
2000 forward will work, and will be very similar in operations).
means is convenient, get to the Database View, which will appear similar
to that shown in Illustration 7.
Illustration 7: Database
View - FoodMart 2000.mdb
the expense_fact table.
from the context menu that appears, as shown in Illustration 8.
Illustration 8: Select
Copy on the Context Menu
an empty area of the table view, and select Paste, as shown in Illustration
Illustration 9: Right-click
an Empty Area and Select Paste
Table As dialog appears.
following into the Table Name text box:
the radio button to the left of the middle option, Structure and Data,
in the Paste Options section of the Paste Table As dialog box, is
Table As dialog appears as shown in Illustration 10.
Illustration 10: The
Paste Table As Dialog with Settings
table appears in the table view.
simulate, on a very small scale, the addition of a "new" balance to
our cube - in a manner that will make it easy to see the effect on a total once
we incrementally update the cube.
the PostAdditions table to open it.
Select the exp_date
column by clicking its header.
Click the Ascending
Sort button, to sort exp_date, as shown in Illustration 11.
Illustration 11: Sorting
the exp_date Column
Select the top
row appearing in the table, by clicking to its left, as shown in Illustration
Illustration 12: Select
the Top Row in the Table
SCROLL to the
next-to-last populated row in the table, clicking again (SHIFT
still depressed) on the left side of the next-to-last populated row
(ignore the bottom "zeros row.")
should highlight all rows in the table, except the last populated row (and the "zeros
row"), as shown in Illustration 13.
Illustration 13: Select
All Except Last Populated Row and Bottom Empty Row
except the bottom two rows of the table highlighted, push the DELETE
button on the keyboard.
box appears, bearing a warning, and asking for confirmation, as shown in Illustration
Illustration 14: Confirmation
of the Delete Requested
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.
amount from $210.00 to $9,789.00 in the populated row.
appears as depicted in Illustration 15, with the change.
Illustration 15: The
Table with Amount Change
--> Close to close the table.
confirmation dialog appears, asking if we wish to save changes to the table
design, as depicted in Illustration 16.
Illustration 16: Confirmation
Dialog - Click OK
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.
Close the FoodMart
preparation complete, let's get to the Incremental Update.