FIX: New functionality for incremental update of changing dimensions

When you run a query on a cube that contains a changing dimension, the performance of the query degrades temporarily after an incremental update to the changing dimension occurs. This performance degradation continues until the lazy processing thread uses the changing dimension to reconstruct the aggregations for the cubes.

In Microsoft SQL Server 2000 Analysis Services, changing dimensions provide flexibility in terms of structure and data. Unlike typical dimensions, cubes that use changing dimensions typically do not require reprocessing when a changing dimension is incrementally updated. Even if the structure of the changing dimension is changed, the flexible aggregations and indexes for the cube are updated in the background through the use of lazy processing.

The query performance for the cube suffers, however, until lazy processing is complete. Lazy processing, in turn, cannot start until the transaction in which the changing dimension was incrementally updated is complete. This cumulative performance degradation may be unacceptable in certain situations, such as with high-demand cubes in enterprise applications.

This update introduces new processing and transaction options in SQL Server 2000 Analysis Services to provide finer processing control in a transaction.

Service pack information

To resolve this problem, obtain the latest service pack for SQL Server 2000 Analysis Services. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

Hotfix information

A supported fix is now available from Microsoft, but it is only intended to correct the problem that is described in this article. Apply it only to computers that are experiencing this specific problem. This fix may receive additional testing. Therefore, if you are not severely affected by this problem, Microsoft recommends that you wait for the next Microsoft SQL Server 2000 Analysis Services service pack that contains this hotfix.

To resolve this problem immediately, contact Microsoft Product Support Services to obtain the fix. For a complete list of Microsoft Product Support Services phone numbers and information about support costs, visit the following Microsoft Web site:NOTE: In special cases, charges that are ordinarily incurred for support calls may be canceled if a Microsoft Support Professional determines that a specific update will resolve your problem. The typical support costs will apply to additional support questions and issues that do not qualify for the specific update in question.

The English version of this fix has the file attributes (or later) that are listed in the following table. The dates and times for these files are listed in coordinated universal time (UTC). When you view the file information, it is converted to local time. To find the difference between UTC and local time, use the Time Zone tab in the Date and Time tool in Control Panel.
Note Because of file dependencies, the most recent hotfix or feature that contains these files may also contain additional files.

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article. This problem was first corrected in SQL Server 2000 Analysis Services Service Pack 3.

This update to Analysis Services introduces three new features that provide increased control over processing:

• A new processing option, processFullReaggregate, to use with the Process method of the MDStore interface.
• A new transaction method, CommitTransEx, in the MDStore interface.
• A new registry value, DSOCommitMode, that controls transaction behavior on the Analysis server.

Implications of using the new functionality

The new processing options improve the flexibility and control over the re-aggregation process for cubes that are affected by incremental updates to changing dimensions. However, these options also have repercussions for system resource use on the Analysis server, on transaction times, and on data change visibility.

When you use the CommitTransEx method, the processFullReaggregate method, or the DSOCommitMode=2 registry setting to force recalculation of aggregates for affected cubes in the transaction, memory and disk usage can increase on the Analysis server. Memory usage increases because the processing of the affected cubes requires additional memory for the processing buffer to recalculate the cube aggregates. Disk usage increases to approximately double the cumulative size of all affected cubes because the new copies of the data and aggregates are created and the original data is kept for current user queries. On systems where memory or disk space usage is already near capacity, the use of the new processing types may exceed the capacity of the system.

In addition to memory and disk space, the total elapsed time to complete transactions using the new processing or commit types may be much longer than the time to complete transactions by using the conventional lazy aggregation process. In the conventional changing dimension processing model, changes to the dimension are committed and aggregations for affected cubes are rebuilt in the background by the lazy aggregation thread. With the new processFullReaggregate processing method, the CommitTransEx processing method, or the DSOCommitMode processing method, the aggregates for all affected cubes are rebuilt inside the DSO transaction.

New processing option: processFullReaggregate

The processFullReaggregate method extends the ProcessTypes enumeration for the MDStore.Process method for cubes only. Using this processing option with any MDStore object other than one with a ClassType property set to clsCube raises an error.

When you use this option in a transaction, it rebuilds the indexes and re-aggregates the flexible aggregations of a cube, which instructs the actions (that the lazy processing typically performs) to complete during the scope of the transaction.

The cube must meet the following criteria to use the processFullReaggregate option:

• The cube must be a regular cube.
• The cube must have its State property set to olapStateCurrent.
• The cube cannot be previously referenced in the same transaction.

If any of these criteria are not met, an error is raised when the Process method is called. If the cube meets the following criteria, the Process method creates a temporary copy of the cube (a "shadow cube") and iterates through the partitions of the shadow cube. If any partition in the cube has been previously referenced in the current transaction, an error is raised. If a partition has never been processed (the State property is set to olapStateNeverProcessed), the partition is ignored (but no error is raised) for the purpose of re-aggregation. The flexible aggregations and indexes of each partition that qualifies are then recalculated.

During the transaction, queries are resolved by using the original cube, partitions, and dimensions while processing is performed on the shadow cube. If the transaction is successful, the original cube and its subordinate objects are replaced with the shadow cube. If the transaction fails, the original cube and its subordinate objects remain and the shadow cube is removed.

The following code sample demonstrates the processFullReaggregate option using the FoodMart 2000 sample database. Both the Sales and the Warehouse cubes in FoodMart 2000 use the Product dimension, so the code example incrementally updates the Product dimension and then uses the processFullReaggregate option to update the Sales and Warehouse cubes.

New transaction method: CommitTransEx

You can use the processFullReggregate option when you are developing processing solutions for specific cubes or dimensions. However, each cube that depends on a changing dimension must be individually processed to take advantage of the option, which may be tedious when you are processing a whole database.

The CommitTransEx method has been added to the MDStore interface to access this functionality at the database level. Using this processing option with any MDStore object other than one with a ClassType property set to clsDatabase raises an error.

The CommitTransEx uses an optional parameter from the ProcessTypes enumeration to control processing behavior. Only two values are permitted: processDefault and processFullReggregate. If you do not use a parameter, the processDefault value is used.

If you use the processFullReggregate value to call the CommitTransEx method, the method iterates through all the cubes in the database. If any cube meets the following criteria, it is processed as if you used the new processFullReggregate option to call the Process method:

• The cube uses one or more changing dimensions that was previously referenced in the current transaction.
• The cube itself has not been processed in the current transaction.
• None of the partitions for the cube have been processed in the current transaction.

If the cube does not meet all the criteria, an error is raised. Additionally, if the processing of a dimension invalidates the structure of a cube, the cube is ignored (but no error is raised) for the purposes of re-aggregation.

If you use the processDefault value to call the CommitTransEx method, the method functions identically to the CommitTrans method.

The new registry value DSOCommitMode influences the behavior of the CommitTrans and CommitTransEx methods when you use it with clsDatabase objects. For more information about DSOCommitMode and its effect on CommitTrans and CommitTransEx, see the "New Registry Value: DSOCommitMode" section later in this document.

The following code sample demonstrates the CommitTransEx method using the FoodMart 2000 sample database. Both the Sales and the Warehouse cubes in FoodMart 2000 use the Product dimension, so the CommitTransEx method automatically processes the Sales and Warehouse cubes because the Product dimension was incrementally updated in the transaction.

New registry value: DSOCommitMode

The DSOCommitMode registry value permits the behavior of CommitTrans and CommitTransEx to be changed for a specific Analysis server so that current applications that are using Decision Support Objects (DSO) can take advantage of the new processing functionality without requiring changes in code or recompilation.

The registry value is in the \\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\OLAP Server\Server Connection Info registry key, and can be set to one of three values:Note

• Set the DSOCommitMode value to DSOCommitModeRTM to disable the functionality that is described in this document.
• Set the DSOCommitMode value to DSOCommitModeNonLazyIndexing to force this functionality to always be in use.

Installation and redistribution considerations

The following table describes files that were updated to support the functionality that is described in this article. The table also indicates which files to include for redistribution when you use this functionality in DSO applications.

Example

Assume an incremental update of dimension A completed in five minutes using the conventional process method and the lazy aggregation thread completed in 55 minutes to rebuild the aggregations for the affected cubes. To the administrator that is running the incremental update from Data Transformation Services (DTS), Analysis Manager, or a custom DSO program, the duration of the transaction is five minutes. Using the new CommitTransEx, processFullReaggregate, or DSOCommitMode=2 functionality while performing an incremental updated of the same sample dimension would result in a transaction duration of one hour because the transaction is not committed until the aggregations for all affected cubes are rebuilt.

This example demonstrates the potential affect on the duration of transactions while using one of the new processing options.

The increased transaction duration with the new processing options also results in a delay in the availability of the updated dimension and cube data. Updated data is not visible to users until the DSO transaction is committed.

If you are considering using the new processing options, compare the potential costs of using the new options to the benefits of maintaining availability to the existing aggregations before you decide to use the new processing options.