Error message when you try to run an MDX query on a ROLAP-based dimension in SQL Server 2005 Analysis Services: "Server: The operation has been cancelled" (912396)

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: TableID, Column: KeyColumns, Value: KeyValue

SQL Server Management Studio

Executing the query ...
Server: The operation has been cancelled.
Execution complete

Note TableID, KeyColumns, and KeyValue 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:

Open SQL Server Management Studio, and then connect to the instance of SQL Server 2005 Analysis Services.
In Object Explorer, right-click the ROLAP-based dimension, and then click Properties. The Dimension Properties dialog box appears.
In the Select a page pane, click Error Configuration.
Click to clear the Use default error configuration check box if the check box is selected.
In the Duplicate Key list, click Ignore Error, and then click OK.
Right-click the ROLAP-based dimension again, and then click Process.
In the Process Options list, click Process Full, and then click OK.
After you complete processing the ROLAP-based dimension, right-click the cube that is defined on the ROLAP-based dimension, and then click Process.
In the Process Options list, click Process Full, and then click OK.

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.