FIX: New Functionality for Incremental Update of Changing Dimensions (325289)

RESOLUTION

Service pack information

To resolve this problem, obtain the latest service pack for 290211. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

INF: How to obtain the latest SQL Server 2000 service pack

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:

http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMS

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.

   Date         Time   Version            Size    File name
   --------------------------------------------------------------
   27-Jun-2002  22:38                     54,272  Changesinprocessing.doc
   03-Jun-2002  21:56  8.0.0.637       2,957,312  Msmddo.dll       
   03-Jun-2002  22:05  8.0.0.637       4,055,616  Msmddo80.dll     
   03-Jun-2002  21:54  8.0.637.0       1,008,192  Msmdgd80.dll     
   03-Jun-2002  22:50  8.0.637.0       1,774,148  Msmdsrv.exe      
   03-Jun-2002  22:26  8.0.637.0       2,024,016  Msolap80.dll     
   18-Apr-2001  06:23  2000.80.382.0     815,676  Sqldata.dll      
   19-Nov-2001  20:32  2000.80.534.0     590,396  Sqlsort.dll

Note Because of file dependencies, the most recent hotfix or feature that contains these files may also contain additional files.

MORE INFORMATION

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.

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.

Public Sub ReaggregateProductAndCubes()
   Dim dsoServer As DSO.Server
   Dim dsoDatabase As DSO.MDStore
   Dim dsoCube As DSO.MDStore
   Dim dsoDimension As DSO.Dimension
   
   On Error Resume Next
   
   Set dsoServer = New DSO.Server
   dsoServer.Connect "LocalHost"
   
   If dsoServer.State = stateConnected Then
      Set dsoDatabase = dsoServer.MDStores("FoodMart 2000")
      If Not (dsoDatabase Is Nothing) Then
         ' Begin the transaction.
         dsoDatabase.BeginTrans
         
         ' First, process the Product changing dimension.
         Set dsoDimension = dsoDatabase.Dimensions("Product")
         dsoDimension.Process processRefreshData
         
         ' Then, reaggregate the Sales cube.
         Set dsoCube = dsoDatabase.MDStores("Sales")
         dsoCube.Process processFullReaggregate
                  
         ' Then, reaggregate the Warehouse cube.
         Set dsoCube = dsoDatabase.MDStores("Warehouse")
         dsoCube.Process processFullReaggregate
                  
         ' Commit the transaction.
         dsoDatabase.CommitTrans
      End If
   End If
End Sub

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.

Public Sub ReaggregateProductAndDatabase()
   Dim dsoServer As DSO.Server
   Dim dsoDatabase As DSO.MDStore
   Dim dsoDimension As DSO.Dimension
   
   On Error Resume Next
   
   Set dsoServer = New DSO.Server
   dsoServer.Connect "LocalHost"
   
   If dsoServer.State = stateConnected Then
      Set dsoDatabase = dsoServer.MDStores("FoodMart 2000")
      If Not (dsoDatabase Is Nothing) Then
         ' Begin the transaction.
         dsoDatabase.BeginTrans
         
         ' First, process the Product changing dimension.
         Set dsoDimension = dsoDatabase.Dimensions("Product")
         dsoDimension.Process processRefreshData
         
         ' Commit the transaction.
         dsoDatabase.CommitTransEx processFullReaggregate
      End If
   End If
End Sub

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:

Value	Name	Description
0	DSOCommitModeDefault	The CommitTrans method functions regularly, and the CommitTransEx method functions as described earlier in this document.
1	DSOCommitModeRTM	The CommitTrans and CommitTransEx methods function like the original CommitTrans method.
2	DSOCommitModeNonLazyIndexing	The CommitTrans and CommitTransEx methods function like the CommitTransEx method when it is called with the processFullAggregate value.

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.

File	Description
Msmddo80.dll	The DSO library, version 8.0. This file should be included for redistribution, however it is not required on the client if the client will not be using Analysis Manager or a custom DSO application.
Msmddo.dll	The Microsoft SQL Server version 7.0 OLAP Services compatibility file. This file is not required if the application uses only features that are available in SQL Server 2000 Analysis Services. This file should be included for redistribution, however it is not required on the client if the client will not be using Analysis Manager or a custom DSO application.
Msolap80.dll	Microsoft OLE DB provider for Analysis Services.
Msmdgd80.dll	Microsoft SQL Server Analysis Services driver.
Msmdsrv.exe	The executable file for the Analysis Services (MSSQLServerOLAPService) service.
Sqldata.dll	SQL Sorting Data library.
Sqlsort.dll	SQL Sorting library.

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, transaction times, and data change visibility.

Use of the CommitTransEx, ProcessFullReaggregate, or DSOCommitMode=2 registry setting to force recalculation of aggregates for affected cubes in the transaction can increase memory and disk usage on the Analysis server. Memory usage is increased 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 considerably longer than with 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, CommitTransEx or DSOCommitMode processing methods, the aggregates for all affected cubes are rebuilt inside the DSO transaction.

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.