Microsoft KB Archive/819541

= BUG: MDX Query Does Not Return Correct Results When Writeback Data Is Not Committed =

Article ID: 819541

Article Last Modified on 11/21/2006

-

APPLIES TO


 * Microsoft SQL Server 2000 Analysis Services

-



BUG #: 13979 (Plato7x)



SYMPTOMS
When you run a Multidimensional Expression (MDX ) query before you commit a writeback transaction, the MDX query might return the original values instead of the updated values of the writeback.

This behavior occurs when you query a virtual dimension.



CAUSE
When you query a virtual dimension, Analysis Services resolves the value of the virtual dimension on the server. Because the server has the original value before you commit the writeback, the MDX query returns the original values.



STATUS
Microsoft has confirmed that this is a problem in Microsoft SQL Server 2000 Analysis Services.



WORKAROUND
To work around this problem, perform one of the following tasks:
 * Before you run the MDX query, commit the transaction of the writeback.
 * Change the virtual dimension to a regular dimension.



MORE INFORMATION
When you commit the writeback, Analysis Services updates the value on the server. Hence, when you query the virtual dimension after the commit, you receive the updated values.

For a regular dimension, Analysis Services resolves the value on the client. Hence, when you query a regular dimension, the MDX query returns the updated values.

Steps to Reproduce the Behavior
To reproduce the behavior, follow these steps:  Start the MDX Sample application, and then select the Foodmart 2000 database.  Run the following UPDATE statement in the MDX Sample application: UPDATE CUBE [Warehouse] SET ([Measures].[Units Ordered],[Time].[1997],[Warehouse].[All Warehouses].[USA].[CA], [Store Size in SQFT],[Store].[All Stores].[USA].[CA].[Los Angeles].[Store 7], [Product].[All Products].[Drink].[Dairy].[Dairy].[Milk].[Even Better].[Even Better 2% Milk], [Store Type].[All Store Type])=200 USE_WEIGHTED_ALLOCATION   Run the following MDX statement to show that a query with a regular dimension returns the updated value: SELECT FROM [Warehouse] WHERE ([Measures].[Units Ordered],[Time].[1997],[Warehouse].[All Warehouses].[USA].[CA], [Store Size in SQFT],[Store].[All Stores].[USA].[CA].[Los Angeles].[Store 7], [Product].[All Products].[Drink].[Dairy].[Dairy].[Milk].[Even Better].[Even Better 2% Milk], [Store Type].[All Store Type])   Run the following MDX statement to show that a query with virtual dimensions returns the original value: SELECT FROM [Warehouse] WHERE ([Measures].[Units Ordered],[Time].[1997],[Warehouse].[All Warehouses].[USA].[CA], [Store Size in SQFT].[All Store Size in SQFT].[23598],[Store].[All Stores], [Product].[All Products].[Drink].[Dairy].[Dairy].[Milk].[Even Better].[Even Better 2% Milk], [Store Type].[All Store Type].[Supermarket]) 

Keywords: kbbug KB819541

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.