Microsoft KB Archive/912396

= Error message when you try to run an MDX query on a ROLAP-based dimension in SQL Server 2005 Analysis Services: &quot;Server: The operation has been cancelled&quot; =

Article ID: 912396

Article Last Modified on 2/14/2006

-

APPLIES TO


 * Microsoft SQL Server 2005 Analysis Services

-





SYMPTOMS
Consider the following scenario. You are running Microsoft SQL Server 2005 Analysis Services. You try to run a Multidimensional Expressions (MDX) query on a relational online analytical processing (ROLAP)-based dimension. You do this by using the MDX Sample application or SQL Server Management Studio. In this scenario, you may receive one of the following error messages:

MDX Sample application
Unable to open cellset

Server: The operation has been cancelled.

Errors in the OLAP storage engine: The attribute key is a duplicate: Table:, Column:  , Value:

SQL Server Management Studio
Executing the query ...

Server: The operation has been cancelled.

Execution complete

Note,  , and   represent the table name, the column name, and the value where the error is raised.



CAUSE
The behavior occurs when the MDX query sends different requests for the same member of the ROLAP-based dimension. In this scenario, the member key is accessed repeatedly. In SQL Server 2005 Analysis Services, the second access to the same member key is treated as a duplicate. For example, you may experience this behavior when you run an MDX query that contains the following requests:
 * Children of [USA]
 * Descendants of [USA]

Both of the requests will access the [CA] member, and an error is raised for the second access.



WORKAROUND
To work around this behavior, follow these steps:
 * 1) Open SQL Server Management Studio, and then connect to the instance of SQL Server 2005 Analysis Services.
 * 2) In Object Explorer, right-click the ROLAP-based dimension, and then click Properties. The Dimension Properties dialog box appears.
 * 3) In the Select a page pane, click Error Configuration.
 * 4) Click to clear the Use default error configuration check box if the check box is selected.
 * 5) In the Duplicate Key list, click Ignore Error, and then click OK.
 * 6) Right-click the ROLAP-based dimension again, and then click Process.
 * 7) In the Process Options list, click Process Full, and then click OK.
 * 8) After you complete processing the ROLAP-based dimension, right-click the cube that is defined on the ROLAP-based dimension, and then click Process.
 * 9) In the Process Options list, click Process Full, and then click OK.



STATUS
This behavior is by design.



MORE INFORMATION
In SQL Server 2005 Analysis Services, you can directly run MDX queries in SQL Server Management Studio. Additionally, you can also use the MDX Sample application to query data from SQL Server 2005 Analysis Services. The tool is included in SQL Server 2000 Analysis Services components.

Keywords: kbtshoot kbprb kbexpertiseadvanced kbsql2005as kbsql2005bi KB912396

-

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

© Microsoft Corporation. All rights reserved.