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 2

By William Pearson

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.

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