Create
Sample Data to Act as the "Add Target" in the Incremental Database
We
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
2000 schema.
1.
Navigate to
the FoodMart 2000.mdb file, via Windows Explorer, MS Access, or other
means desired.
The FoodMart
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
2.
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).
3.
By whatever
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
4.
Right-click
the expense_fact table.
5.
Select Copy
from the context menu that appears, as shown in Illustration 8.
Illustration 8: Select
Copy on the Context Menu
6.
Right-click in
an empty area of the table view, and select Paste, as shown in Illustration
9.
Illustration 9: Right-click
an Empty Area and Select Paste
The Paste
Table As dialog appears.
7.
Type the
following into the Table Name text box:
PostAdditions
8.
Ensure that
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
selected.
The Paste
Table As dialog appears as shown in Illustration 10.
Illustration 10: The
Paste Table As Dialog with Settings
The PostAdditions
table appears in the table view.
We will
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.
10.
Double-click
the PostAdditions table to open it.
11.
Select the exp_date
column by clicking its header.
12.
Click the Ascending
Sort button, to sort exp_date, as shown in Illustration 11.
Illustration 11: Sorting
the exp_date Column
13.
Select the top
row appearing in the table, by clicking to its left, as shown in Illustration
12.
Illustration 12: Select
the Top Row in the Table
14.
SHIFT +
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.")
This
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
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 Illustration
14.
Illustration 14: Confirmation
of the Delete Requested
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 Illustration 15, with the change.
Illustration 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 Illustration 16.
Illustration 16: Confirmation
Dialog - Click OK
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.
» 
Comment and Contribute
