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 3

By William Pearson

Hands-On Procedure

As we noted in the last section, we use Incremental Updates to append new data to a cube - more precisely to a single partition of a cube - and to update the aggregations involved. Let's take a look at how we manage the process with the MSAS tool that is provided to manage this operation, the Incremental Update Wizard.

We will use, in our practice example a copy of one of our sample cubes, the Budget Cube. You can skip the section immediately following, where we copy the cube, if you prefer to perform it with the actual sample Budget cube (you can always restore the cube from the .cab archive that comes along with the MSAS installation, or from the original CD itself - see the Books Online for the process). Or, if you prefer, simply follow along with another cube of your choice (realizing that results I illustrate, etc., will, of course, differ for another cube).


Make a Copy of the Budget Cube and Prepare it for an Incremental Update

1.  Open Analysis Manager, beginning at the Start menu.

2.  Expand the Analysis Servers folder by clicking the "+" sign to its immediate left.

Our server(s) appear.

3.  Expand the desired server.

Our database(s) appear.

4.  Expand the FoodMart 2000 database.

5.  Expand the Cubes folder.

The sample cubes appear, as shown in Illustration 1.

Illustration 1: The Sample Cubes in the FoodMart2000 Database

NOTE: Your databases / cube tree may differ, depending upon the activities you have performed since the installation of MSAS (and the simultaneous creation of the original set of sample cubes). Should you want or need to restore the cubes to their original state, simply restore the database under consideration. For instructions, see the MSSQL Server 2000 / MSAS Books Online.

6.  Right-click on the Budget sample cube.

Working with a copy of the sample Budget cube will allow us to maintain our existing Budget sample cube in its original condition.

7.  Select Copy from the context menu that appears.

8.  Right-click on the Cubes folder.

9.  Select Paste from the context menu that appears.

The Duplicate Name dialog appears.

As we have noted in other articles, we cannot have two cubes of the same name in a given MSAS database.

10.  Type the following into the Name box of the Duplicate Name dialog:


The Duplicate Name dialog appears, with our modification, as depicted in Illustration 2.

Illustration 2: The Duplicate Name Dialog, with New Name

TIP: As I have mentioned elsewhere in the series, this is also an excellent way of renaming a cube (a "rename" capability is not available here): Simply create a duplicate, give it the name to which you wish to rename the old cube, and then delete the old cube. (This also works for MSAS databases.)

11.  Click OK to save the name change.

The new cube appears in the cube tree, among those already in place. We now have a copy of the Budget cube, IncrUpdate, as shown in Illustration 3, upon which we can perform our exercise with the Incremental Update Wizard.

Illustration 3: The Budget Cube Clone, IncrUpdate, Appears

As we learned in several of my other articles, where we made "clones" of existing sample cubes, when we make a copy of an existing sample cube, we copy the structure exactly as it exists for the original sample.

One component critical to on-going processing with a given sample cube, however, cannot physically be a part of our cloning process - the Query log entries that relate to the history of the copied cube cannot accompany the cloned cube; the entries are identified in the Query log database (see MSAS Administration and Optimization: Simple Cube Usage Analysis and MSAS Administration and Optimization: Toward More Sophisticated Analysis for more information regarding the Query log) as belonging to the cube that generated them.

This leaves MSAS a bit perplexed (at least with regard to the "processed status" of the cube) - it has a cube structure in its midst that, while physically circumspect with regard to its structure, "has no past." Therefore, when we attempt to process the cube, MSAS "looks upon him, and knows him not." It therefore assumes - actually, it mandates - Full Processing. We will humor MSAS and do a quick Full Process, so that we can continue our examination of Incremental Updates - Keep in mind that this is only a preparatory step, and may not be encountered in the "real world."

We will "register" the clone with MSAS - at least from the perspective of the Query log, by taking the following steps:

12.  Right-click the new IncrUpdate cube.

13.  Select Process from the context menu that appears, as partially shown in Illustration 4.

Illustration 4: Select Process from the Context Menu (Partial View)

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

Illustration 5: The Select the Processing Method Dialog

Full Processing is the default (the other options are disabled), as the IncrUpdate cube has never been processed, at least from the perspective of history (none for the clone, at present).

14.  Leaving settings at default, click OK.

Processing begins, and runs rapidly, as evidenced by the Process viewer's presentation of processing log events in real time. Processing ends rather quickly and the success of the evolution is indicated by the appearance of the Processing Completed Successfully message (in green letters) at the bottom of the viewer, as shown in Illustration 6.

Illustration 6: Indication of Successful Processing

15.  Click Close.

We are returned to Analysis Manager, having prepared the cube for an Incremental Update (based upon the fact that it was a clone with no history).

Next, let's do some preparation to make the process work as it would in a real Incremental Update. For this to be simulated, we will need to build a small set of data to treat as the "add' source of 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