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

Monday Nov 8th 2004 by William Pearson
Share:

Join Bill Pearson in an introduction to incremental updates in MSAS, where we divide a logical cube into separate "containers" to provide storage flexibility and enhanced query performance.

About the Series ...

This article is a member of the series Introduction to MSSQL Server 2000 Analysis Services. The series is designed to provide hands-on application of the fundamentals of MS SQL Server 2000 Analysis Services, with each installment progressively adding features and techniques designed to meet specific real - world needs. For more information on the series, as well as the hardware / software requirements to prepare for the exercises we will undertake, please see my initial article, Creating Our First Cube.

Note: Service Pack 3 updates are assumed for MSSQL Server 2000, MSSQL Server 2000 Analysis Services, and the related Books Online and Samples. Images are from a Windows 2003 Server environment, upon which I have also implemented MS Office 2003, but the steps performed in the articles, together with the views that result, will be quite similar within any environment that supports MSSQL Server 2000 and MSSQL Server 2000 Analysis Services ("Analysis Services" or "MSAS"). The same is generally true, except where differences are specifically noted, when MS Office 2000 and above are used in the environment, in cases where MS Office components are presented in the article.

Overview

Having processed cubes in preceding articles, we are aware that processing calculates the aggregations that have been designed for the cube, and then loads the cube with the data, and the calculated aggregations of the data. While we have looked only at Full Processing in most of my articles, we will expose other approaches to processing in this session. We will then focus on the use of a tool that MSAS provides to help us to exploit one of those options, the incremental processing of our cubes.

The processing of cubes, once we get beyond the basics, is complex subject matter. Nevertheless, an in-depth grasp of the subject is highly critical to mastering optimization of our cubes and MSAS in general. For this reason, I will be spending considerable time within the articles of this series (as well as articles and columns elsewhere) that address many nuances and approaches to the tuning of the processing event. Our purpose within this article, however, is to overview the Incremental Processing Wizard, and, within that context, the concept of incremental processing.

In this article, we will introduce the MSAS Incremental Processing Wizard, whose role is to enable us to perform incremental updates of our cubes, while helping us to steer clear of some of the dangers that are inherent with incremental processing. We will first discuss processing in general, and then focus on incremental processing as a concept within the context of MSAS cubes. Next, we will perform a hands-on exercise where we incrementally update a copy of one of the sample cubes that accompany the installation of MSAS.

Our objective in this article is an overview of the wizard itself; other of my articles will focus on specific processing strategies and techniques. Within our exploration of the Incremental Update Wizard, we will accomplish the following:

  • Create a copy of the Budget sample cube for use in our practice exercise.
  • Prepare the cube further by processing.
  • Perform a practice exercise, using the Incremental Update Wizard, to incrementally update our practice cube.
  • Examine the options that are available to us, as we proceed through the guided steps of the Wizard.
  • Confirm our understanding of the mechanics behind the action of the Incremental Update Wizard by examining pre- and post- update values, as a straightforward means of verifying that the difference we compute equals the value we established to be added via the incremental update.

Incremental Processing

Overview and Discussion

As we noted before, processing a cube recalculates its aggregations, and then loads the data and aggregations to the cube. The steps that MSAS undertakes in this process include filling in of the various dimension levels in the cube with data that it reads from the dimension tables, as well as reading data from the fact table, calculating the designed aggregations, and then populating the cube with the results.

We have seen that a cube must be processed before it can be queried, in numerous scenarios we have encountered in our series to date. To expand upon this a bit more precisely, any of the following actions can, if performed on a cube, force the processing of the cube before it can be queried or browsed within MSAS, as we have noted individually in other articles:

  • The initial build of the cube;
  • Designing storage options and aggregations for the cube (in conjunction with an initial build or not);
  • Changing the cube's structure (measures, dimensions, and so on) and saving the changes to the cube;
  • Making structural changes to a shared dimension used within the cube.

For reasons that are obvious, changes in the data mart or warehouse underlying a cube are common grounds for processing. The synchronization of any cube with the data from which it is constructed insures that the cube accurately and completely reflects the data that it exists to present. Periodicity of these synchronization updates relies upon the frequency of change in the cube's source data, among other possible factors.

In summary, then, we see that any change to the source data that underlies a cube, and many structural changes we make in Analysis Manager, force processing of the cube to ensure that the changes are, in turn, synchronized, and cube updates completed, before presentation of the cube data to information consumers. Let's take a look next at processing options that MSAS makes available.

Processing Options

MSAS offers numerous processing options, as we shall see in this section. The best way to synchronize our cubes with the underlying data, and to ensure that they reflect completely the dimensional structure that we have established in Analysis Manager, is to "toss" the cube entirely, and rebuild from scratch. There are many times when this is not desirable, however, and so other options exist. The time it takes to perform a Full Processing cycle (say, longer than the overnight period that lies between hours that information consumers are expected to need access to the cube we are updating) may be prohibitive, although updates are vital. Moreover, storage space may be a consideration. The accumulation of aggregations in a Full Processing cycle requires the creation of temporary files that, in combination with the independent copies of cube and dimension files that accompany a full build, can become major resource challenges.

One way to manage challenges of this sort within MSAS is to perform "piecemeal processing" of certain components of the MSAS database. Of the four options available for these independent processing approaches, three are mutually exclusive, and include Full Process, Refresh Data and Incremental Update. The fourth option, Incrementally Update the Dimensions of this Cube can be performed along with any of the first three options, to incrementally update the cube's dimensions as part of the cube processing.

Our exposure to cube processing within my articles largely centers upon the Full Process option. Unsurprisingly, Full Processing is the path we take most of the time, because it affords us a simple approach, within the context of articles that focus on another aspect of cube design and development entirely, to updating the cube involved, so that we can continue with the primary focus of the article without too much distraction. As we have discussed, Full Processing completely rebuilds a cube from scratch, reconstructs it based upon the current MSAS definition, recalculates its data and aggregations, and repopulates the new structure with the data and aggregations. In the business environment, other processing options can be of value for various reasons.

When we simply need to clear out the data in a cube, then to reload the data with its recalculated aggregations, (as in a case where the underlying source data has changed, but the cube structure has not been altered), we have the Refresh Data option. This would be the case, say, where we want to update a cube for each month's activity (new data), and to recalculate all aggregations surrounding the data, and where we are certain that no changes have been made in the structure of the cube. This approach is intended for the "steady state" operations of many organizations.

In my opinion, little is gained by choosing Refresh Data over Full Process because both processing options completely rebuild the aggregations tables from scratch. If testing on your local environment shows little difference, you might simply use the Full Process option for similar update needs: at least with Full Process you get an automatic check of the dimension structure to ascertain that no changes have, in reality, been made.

An Incremental Update adds new data and updates aggregations. Structural changes, such as those made to dimensions, measures, and so forth, are not updated with an Incremental Update. Moreover, Incremental Updates do not update changes to the cube's existing, underlying source data. We will examine the Incremental Update further, within the context of walking through the steps of the Incremental Update Wizard, in our next section. The important characteristic of an Incremental Update to keep in mind is that it merges new data into an existing partition, adding the data to, and updating the aggregations of, the cube for which it is initiated.

This management of "what has changed only" in the warehouse / mart, for addition to the cube, has its obvious attractions when the goal is to reduce cube processing time, but only if the way that it works is understood in detail, and if accommodation for the process is made prior to its use. To summarize its operation in simple terms, we can look at the Incremental Update process as being pointed at a different fact table than that in which the "existing" / already processed data is being stored - or, if pointed at the same fact table, pointed only at the "new" subset of the data via a filter we put in place. New files are created by the process - cube files that are identical to those produced in a Full Process build.

The Incremental Update process, in contrast with Full Process, does not simply swap the new files it creates with the ones that make up the previous cube. Remember, these cube files presumably represent "new" data that is not summarized in the existing cubes. The Incremental Update process creates yet another set of files, composed of a combination of the original cube files and the "new" cube set. Because we have, at least temporarily, three full sets of files, Incremental Updates on larger cubes may not act to relieve the disk space issues that cause problems with a Full Process approach. In addition, other potential dangers can arise within the process by which the Incremental Update creates a temporary partition to accomplish the merge with the existing data, which is housed within its own partition(s). We will touch upon this further at the appropriate point in our practice exercise within the next section.

NOTE: Once the "combination cube" is born, the original and "complementary" (or "delta") cube files are removed completely from the drive. The combination cube, now alone, is then named to identify it as the cube it replaces.

We will discuss the Incremental Update process in more detail, when it is relevant to our introduction to the Incremental Update Wizard, in the next section. As I have stated, I address detailed specific incremental processing, optimization and other strategies and approaches in other articles. Our focus in this article is to introduce the concept of incremental updates, and the use of the Incremental Update Wizard, and, therefore, our efforts rely upon the underlying assumption of an Incremental Update.

Incrementally Update the Dimensions of this Cube is a supplementary action that, as we have stated before, can be performed along with any of the available options to incrementally update the cube's dimensions. This is done as part of cube processing under the respective option, and is not the type of Incremental Update for which we use the Incremental Update Wizard. It simply represents a means of adding in new dimension members that have come along. This "add-on" feature within the primary update options exists to enable us to easily handle simple member adds that do not alter the dimension structure enough to drive a forced reprocessing of the cube.

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

Preparation

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:

IncrUpdate

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.



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

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.

Procedure

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.



Verify the Operation of the Incremental Update



In drawing our exercise to a close, let's verify the results of our Incremental Update. This should be easy to do, in light of the fact that we know we only applied an added month, the final month (12) of the 1998 operating year, to a single account that we can also easily locate (Account 4400, the Lease Expense account in the chart of accounts used in the Budget cube, from which we cloned the IncrUpdate cube.)


Conveniently enough, we have a copy of our cube just before its Incremental Update - the Budget cube itself - which we can browse for a total Lease expense for the period (12-1998) under examination. We can then compare the balance we obtain there to the balance for Lease expense, for period 12 of 1998, for the IncrUpdate cube, and see that the difference between the two is the amount of the incremental addition we have performed.


Let's perform a couple of quick browses at this point, to prove our understanding of the Incremental Update process.


1.  Right-click the Budget sample cube.


2.  Select Browse Data from the context menu that appears, as shown in Illustration 24.



Illustration 24: Select Browse Data from the Context Menu

The Cube Browser indicates that data is being retrieved, and then populates the default view.

3.  Click the Level02 heading for the row axis, and drag it into the dimension area above it to remove it from the grid, and to swap the Time dimension into the column axis.

Year appears as the new heading.

4.  Click the down arrow in the Account selector box.

The Account hierarchy appears under the selector box, allowing us to drill down on the hierarchy.

5.  Expand All Account by clicking the "+" sign to its left.

6.  Expand the Net Income level underneath All Account.

7.  Expand the Total Expense level underneath Net Income.

The Account hierarchy in the expanded selector, with our ultimate target of the Lease member at the bottom, appears as depicted in Illustration 25.


Illustration 25: The Expanded Account Hierarchy

8.  Click the Lease expense member (which equals Account 4400 in the Budget cube- and therefore in its clone, IncrUpdate) to select it in the selector.

The Cube Browser, with our changes reflected, appears as depicted in Illustration 26.


Illustration 26: The Cube Browser with Our Settings

9.  Double-click 1998 in the row axis to drill down to Quarters.

The Quarters appear to the right of 1998.

10.  Double-click Q4 to drill down to its constituent months.

We see the value of $ 20,117.95, for Month 12, 1998, appear, as shown in Illustration 27.


Illustration 27: The Balance for 12-1998 Lease Expense - Before Incremental Update.

Now, let's isolate the same value within IncrUpdate, our newly updated cube.

11.  Click Close to dismiss the Cube Browser for the Budget cube.

The Cube Browser closes.

12.  Right-click the IncrUpdate cube.

13.  Select Browse Data from the context menu that appears, as we did with the Budget cube above.

The Cube Browser indicates that data is being retrieved, and then populates the default view.

14.  Click the Level02 heading for the row axis, and drag it into the dimension area above it to remove it from the grid. (Drop to swap the Time dimension into the column axis.)

15.  Click the down arrow in the Account selector box.

The Account hierarchy appears under the selector box, allowing us to drill down on the hierarchy.

16.  Expand All Account by clicking the "+" sign to its left.

17.  Expand the Net Income level underneath All Account.

18.  Expand the Total Expense level underneath Net Income.

The Account hierarchy in the expanded selector, with the Lease member at the bottom, appears.

19.  Click the Lease expense member to select it in the selector.

The Cube Browser, with our changes reflected, appears as depicted in Illustration 28.


Illustration 26: The Cube Browser with Our Settings

20.  Double-click 1998 in the row axis to drill down to Quarters.

The Quarters appear to the right of 1998.

21.  Double-click Q4 to drill down to its constituent months.

We see the value of $ 29,906.95, for Month 12, 1998, appear, as shown in Illustration 29.


Illustration 29: The Balance for 12-1998 Lease Expense -Incrementally Updated Cube

The difference between the Lease expense for Month 12, 1998 ($ 29,906.95) in the incrementally updated IncrUpdate cube, and the same value for the original Budget cube ($ 20,117.95) is $ 9,789.00, the amount we typed into the special "new value" table we created in the FoodMart 2000 warehouse (see Illustration 15). This confirms our understanding of the operation of the incremental update process.

22.  Close the Cube Browser for the IncrUpdate cube.

The Cube Browser closes.

23.  Delete the practice cube, IncrUpdate, as desired.

24.  Delete the "add" table we created, PostAdditions, in the FoodMart 2000 MS Access database, IncrUpdate, as desired.

25.  Close all applications, as appropriate.

Summary ...

In this article, we introduced the MSAS Incremental Processing Wizard, whose role is to enable us to perform Incremental Updates of our cubes, while helping us to avoid double-counting data, among other dangers that are inherent with Incremental Processing. We first explored processing options in general, and then focused on Incremental Updates within the context of MSAS cubes. Next, we performed a hands-on exercise where we incrementally updated a copy of the MSAS Budget sample cube with a data value that we placed in a special table in the source warehouse, for purposes of our exercise.

We examined the options that are available to us, as we proceeded through the guided steps of the Wizard. Finally, we proved the results we obtained by showing that the magnitude of the increase in an account balance, within the Incrementally Updated cube, equaled exactly the value we established to be added to our original cube, via the Incremental Update process.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved