Microsoft KB Archive/942240

= Error message when you run an MDX query against a dimension cube in SQL Server 2005 Analysis Services: &quot;The $ cube either does not exist or has not been processed&quot; =

Article ID: 942240

Article Last Modified on 10/5/2007

-

APPLIES TO


 * Microsoft SQL Server 2005 Analysis Services

-



SYMPTOMS
When you run a Multidimensional Expressions (MDX) query against a dimension cube in Microsoft SQL Server 2005 Analysis Services, you receive the following error message:

The $ cube either does not exist or has not been processed.

Note The placeholder  represents the name of the dimension cube.

This behavior occurs when one of the following conditions is true:
 * You do not have full control permissions in the Analysis Services database that contains the dimension.
 * You are not a member of the Administrators group on the server that is running the instance of Analysis Services.



CAUSE
This behavior occurs because a user who queries a dimension cube in Analysis Services must meet one of the following requirements:
 * The user must have full control permissions in the Analysis Services database that contains the dimension.
 * The user must be a member of the Administrators group on the server that is running the instance of Analysis Services.



WORKAROUND
To work around this behavior, create a cube that contains only the dimension. Then, you can query the dimension in this cube instead of querying the dimension cube itself.

To create a cube that contains only a dimension, use one of the following methods.

Method 1

 * 1) In the relational database, create a view that is defined as the data source of the Analysis Services database. The view contains two columns. One column corresponds to the dimension key of the dimension, and the other column is a dummy fact measure column.
 * 2) Build a cube that uses the view as a dimension.

To work around this behavior by applying Method 1 to the steps that are mentioned in the &quot;Steps to reproduce the problem&quot; section, follow these steps:  Create a view in the relational database. To do this, follow these steps:  Open SQL Server Management Studio, and then connect to the instance of SQL Server 2005 that hosts the AdventureWorks DW database. Click New Query to open a new query window.  In the new query window, run the following Transact-SQL statement: USE AdventureWorks DW GO

CREATE VIEW [MyProduct] AS SELECT ProductKey, NULL as DummyFact FROM DimProduct GO   In SQL Server Business Intelligence Development Studio, open the AdventureWorks DW Standard Edition sample project.</li> In Solution Explorer, expand Data Source Views, and then double-click AdventureWorks.dsv.</li> On the Data Source View menu, click Add/Remove Tables.</li> Under Available object in the Add/Remove Tables dialog box, click dbo.MyProduct, click >, and then click OK.</li> On the Data Source View menu, click New Relationship.</li> In the Source (foreign key) table list in the Create relationship dialog box, select dbo.MyProduct, and then select dbo.DimProduct in the Destination (primary key) table list.</li> Under Source Columns, select ProductKey for the first row.</li> Under Destination Columns, select ProductKey for the first row.</li> Click OK.</li> Build a cube that is based on the dbo.MyProduct table. To do this, follow these steps:  In Solution Explorer, right-click Cubes, and then click New Cube.</li> In the Cube Wizard dialog box, click Next.</li> On the Select Build Method page, click Build the cube using a data source, click to select the Auto build check box, and then click Next.</li> Under Available data source views on the Select Data Source View page, click AdventureWorks DW, and then click Next.</li> On the &quot;Detecting Fact and Dimension Tables&quot; page, click Next.</li> On the &quot;Identify Fact and Dimension Tables&quot; page, click the Tables tab.</li> <li>In the Name column, locate dbo.MyProduct, click to select the Fact check box, and then click to clear the Dimension check box.</li> <li>In the Name column, locate dbo.DimProduct, click to select the Dimension check box, and then click to clear the Fact check box.</li> <li>For the other tables in the Name column, click to clear the Fact check box and the Dimension check box, and then click Next.</li> <li>On the Review Shared Dimensions page, click >>, and then click Next.</li> <li>In the Measure Group/Measures column on the Select Measures page, click to clear the My Product Count check box, and then click Finish.</li> <li>In the Cube name text box on the &quot;Completing the Wizard&quot; page, type ProductsCube, and then click Finish.</li></ol> </li> <li>Deploy the AdventureWorks DW Standard Edition sample project.</li> <li>Specify access permissions for the user who is a member of the Users group. To do this, follow these steps: <ol style="list-style-type: lower-alpha;"> <li>Open Management Studio by using the user who is a member of the Administrators group, and then connect to the instance of Analysis Services.</li> <li>In Object Explorer, expand Databases, expand AdventureWorks DW Standard Edition, and then expand Roles.</li> <li>Right-click Roles, and then click New Role.</li> <li>In the Create Role dialog box, click to select the Read definition check box, and then click Membership under Select a page.</li> <li>Click Add.</li> <li>Under Enter the object name to select in the Select Users or Groups dialog box, type Everyone .</li> <li>Click Check Names, and then click OK.</li> <li>Under Select a page, click Cubes.</li> <li>In the Access column, select Read for the ProductsCube cube, and then click OK.</li></ol> </li> <li>Test the result. To do this, follow these steps: <ol style="list-style-type: lower-alpha;"> <li>Open a new instance of Management Studio by using the user who is a member of the Users group, and then connect to the instance of Analysis Services.</li> <li>Click New Query to open a new query window.</li> <li> In the new query window, run the following MDX statement: SELECT [Product]. [Product]. [Product].Members ON 0 FROM [ProductsCube] </li></ol> </li></ol>

Method 2

 * 1) In the data source view, create a named query.
 * 2) Build a cube that uses the view as a dimension.

To work around this behavior by applying Method 2 to the steps that are mentioned in the &quot;Steps to reproduce the problem&quot; section, follow these steps: <ol> <li>In Business Intelligence Development Studio, open the AdventureWorks DW Standard Edition sample project.</li> <li>In Solution Explorer, expand Data Source Views, and then double-click AdventureWorks.dsv.</li> <li>On the Data Source View menu, click New Named Query.</li> <li>In the Name box in the New Named Query dialog box, type MyProduct .</li> <li> In the SQL pane, enter the following Transact-SQL statement, and then click OK: SELECT ProductKey, NULL as DummyFact FROM DimProduct </li> <li>Follow steps 6 through 14 of Method 1.</li></ol>

<div class="status_section">

STATUS
This behavior is by design.

<div class="moreinformation_section">

Steps to reproduce the problem
Assume that you have deployed the AdventureWorks DW Standard Edition sample project.

Note For more information about how to obtain this sample project, visit the following Microsoft SQL Server Product Samples Database Web site:

http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=4004

<ol> <li>Open Management Studio, and then connect to the instance of Analysis Services.

Note Make sure that the user who opens Management Studio is a member of the Administrators group.</li> <li>In Object Explorer, expand Databases, expand AdventureWorks DW Standard Edition, and then expand Roles.</li> <li>Right-click Roles, and then click New Role.</li> <li>In the Create Role dialog box, click to select the Read definition check box, and then click Membership under Select a page.</li> <li>Click Add.</li> <li>Under Enter the object name to select in the Select Users or Groups dialog box, type Everyone .</li> <li>Click Check Names, and then click OK.</li> <li>Under Select a page, click Cubes.</li> <li>In the Access column, select Read for the AdventureWorks cube, and then click OK.</li> <li>Open a new instance of Management Studio, and then connect to the instance of Analysis Services.

Note Make sure that the user who opens Management Studio is a member of the Users group.</li> <li>Click New Query to open a new query window.</li> <li> In the new query window, run the following MDX statement: SELECT [Product]. [Product]. [Product].Members ON 0 FROM [$Product] </li></ol>

You receive the following error message:

The $Product cube either does not exist or has not been processed.

Keywords: kbsql2005bi kbsql2005as kbprb kbexpertiseadvanced kbtshoot KB942240

-

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

© Microsoft Corporation. All rights reserved.