FIX: Cube Editor Reports Named Set Name Not Valid but Named Set Displays Correctly (295463)

SYMPTOMS

When a named set has been created for a cube and calculated members are then built using that named set, the Cube Editor may report errors when browsing, deleting, or saving the cube and the calculated members or named sets that it contains.

The following error message may be seen:

Unable to process one or more commands
Named set 'myset' creation failed.
Set name myset is not valid.

This error may be displayed even though the named set is valid. If the named set is valid, the calculated member that uses the named set will display correctly in the data view, and there is no need to redefine the member. If the named set is invalid, the calculated member that depends on the named set will not be displayed and the error must be corrected in the calculated member definition.

CAUSE

Named sets and calculated members are implemented as cube commands in Analysis Services. The Cube Editor attempts to drop and re-create any cube commands when the cube is first being displayed in the Cube Editor data view or when an update is made to the cube. The Cube Editor is unable to drop the named set commands when calculated member commands exist which depend on the named set.

RESOLUTION

To resolve this problem, obtain the latest service pack for the Microsoft SQL Server 2000 (the Analysis Services Components - Sql2kasp1.exe). For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack

WORKAROUND

One workaround for this problem is to place the MDX statement that defines the set directly in the calculated member definition, instead of implementing it as a named set.

NOTE: This may have an adverse effect on performance, because the named set must be created/evaluated for each calculated member which contains the MDX logic for the set.

STATUS

Microsoft has confirmed that this is a problem in SQL Server 2000 Analysis Services version 8.0. This problem was first corrected in the Analysis Services Components of Microsoft SQL Server 2000 Service Pack 1.

MORE INFORMATION

To reproduce this problem, follow these steps:

Edit the Warehouse cube in the Foodmart 2000 database.
Right-click the Named Sets folder and click Create New Set. Name this set "MySet1", and place the following in the set expression:
```
Descendants([Store].[Store State].&amp;[WA],0,self_and_after)
					
```
Save the named set.
Create a new calculated member on the Measures dimension and name it "MyCalc". The value expression should be as follows:
```
Sum([MySet1],[Measures].[Units Shipped])
					
```
Close the Calculated Member Builder and click the Data View tab to browse the cube data.