Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Nov 8, 2004

Introduction to MSSQL Server 2000 Analysis Services: Performing Incremental Cube Updates - An Introduction - Page 4

By William Pearson

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:


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

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.

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

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.

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

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.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM