Article ID: 325289 - View products that this article applies to.
This article was previously published under Q325289
This article has been archived. It is offered "as is" and will no longer be updated.
BUG #: 12841 (Plato7x)
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 informationTo 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:
(http://support.microsoft.com/kb/290211/ )How to obtain the latest SQL Server 2000 service pack
Hotfix informationA 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;CNTACTMSNOTE: 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 220.127.116.117 2,957,312 Msmddo.dll 03-Jun-2002 22:05 18.104.22.1687 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:
Implications of using the new functionalityThe 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: processFullReaggregateThe 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:
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: CommitTransExYou 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:
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: DSOCommitModeThe 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:
Collapse this tableExpand this table
Installation and redistribution considerationsThe 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.
Collapse this tableExpand this table
ExampleAssume 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.
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:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ansvcspg.mspxIn the “Microsoft SQL Server 2000 Analysis Services Performance Guide” white paper, see the following sections: