Introduction to MSSQL Server 2000 Analysis Services: Performing Incremental Cube Updates - An Introduction - Page 4
November 8, 2004
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.
4. Right-click the expense_fact table.
5. Select Copy from the context menu that appears, as shown in Illustration 8.
6. Right-click in an empty area of the table view, and select Paste, as shown in Illustration 9.
The Paste Table As dialog appears.
7. Type the following into the Table Name text box:
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.
9. Click OK.
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.
13. Select the top row appearing in the table, by clicking to its left, as shown in Illustration 12.
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.
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.
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 Illustration 15, with the 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.
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.