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:
How to obtain the latest SQL Server 2000 service pack
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.
Date Time Version Size File name -------------------------------------------------------------- 27-Jun-2002 22:38 54,272 Changesinprocessing.doc 03-Jun-2002 21:56 18.104.22.1687 2,957,312 Msmddo.dll 03-Jun-2002 22:05 22.214.171.1247 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.
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
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
- 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 IfEnd 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.
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.
uses an optional parameter from the ProcessTypes
enumeration to control processing behavior. Only two values are permitted: processDefault
. 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
- 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
The new registry value DSOCommitMode
influences the behavior of the CommitTrans
methods when you use it with clsDatabase
objects. For more information about DSOCommitMode
and its effect on CommitTrans
, 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 IfEnd Sub
New registry value: DSOCommitMode
registry value permits the behavior of CommitTrans
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:
|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.|
- 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.
|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.|
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
, 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.
The purpose of this fix is only to change the behavior of the lazy aggregation process. However, there are some other solutions that you can use to avoid a decrease in performance for the users. For more information about other methods to improve the availability of the cube, visit the following Microsoft Web site:
In the “Microsoft SQL Server 2000 Analysis Services Performance Guide” white paper, see the following sections:
- Use Incremental Processing and Changing Dimensions
- Guidelines for Changing Dimensions