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

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
Database Tools
SQL Scripts & Samples
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
News Via RSS Feed

follow us on Twitter
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 5

By William Pearson


Perform an Incremental Update with the New Cube

As we 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.

Incremental 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.

We can 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.

1.  Right-click the new IncrUpdate cube.

2.  Select Process from the context menu that appears, as we did in the preparation step earlier.

The Process 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.

3.  Ensure that the radio button to the left of Incremental Update is selected.

The Process a Cube - Select the processing method dialog appears, as depicted in Illustration 17.

Illustration 17: The Select the Processing Method Dialog

4.  Click OK.

The Incremental Update Wizard - Welcome dialog appears, as shown in Illustration 18.

Illustration 18: The Incremental Update Wizard - Welcome Dialog

5.  Click Next.

The Specify 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.

6.  Click the Change... button to the right of the Fact table box (currently occupied by expense_fact).

The Choose a Fact Table dialog appears.

7.  Select the PostAdditions table from the tables displayed on the left, as we did in the preparation step earlier.

Clicking 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

8.  Click OK to accept the selection.

We return 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

9.  Click Next.

We next arrive at the Create a filter expression dialog of the wizard, as shown in Illustration 21.

Illustration 21: The Create a Filter Expression Dialog

Were we 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).

An 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.

We might 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.

10.  Click Next.

The Finish dialog of the Incremental Update Wizard appears, as depicted in Illustration 22.

Illustration 22: The Finish Dialog

11.  Click 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

12.  Click Close.

The Process Viewer closes, and we arrive at Analysis Manager once again.

MS SQL Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



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