Microsoft KB Archive/884395

= You may notice a “#ERR” in the result set when you run an MDX query on a local ROLAP cube that contains a parent-child dimension in SQL Server 2000 Analysis Services =

Article ID: 884395

Article Last Modified on 3/23/2007

-

APPLIES TO


 * Microsoft SQL Server 2000 Analysis Services

-





SYMPTOMS
When you run a Multidimensional Expressions (MDX) query on a local Relational Online Analytical Processing (ROLAP) cube that contains one or more parent-child dimensions, you may notice that the query returns &quot;#ERR&quot; in the result set. Additionally, if you double-click &quot;#ERR&quot; in the result set, you receive the following error message:

Unable to display cell properties.

An error occurred during query execution – required entity missing (“”)

An error occurred during attempt to get a cell value.



CAUSE
To preserve the backward compatibility between Microsoft SQL Server OLAP Services 7.0 and Microsoft SQL Server 2000 Analysis Services, the local cubes do not store the dimensions with a parent-child structure. The dimensions with a parent-child structure are converted to regular dimensions with one column for each level in the dimension. If you run an MDX query on the local ROLAP cube that references the parent-child dimensions, the hierarchy information of the dimension cannot be retrieved. Therefore, the MDX query does not run successfully, and &quot;#ERR&quot; is displayed in the result set.



STATUS
This behavior is by design.



WORKAROUND
To work around this problem, you must run a simple MDX query that drills down to the leaf level of the parent-child dimension, and then run your problem query.

For example, if the following query is your original problem query:

SELECT NON EMPTY HIERARCHIZE(Except({AddCalculatedMembers(Except({AddCalculatedMembers(DrillDownMember({DrillDownLevel({[Account].[All]})}, {[Account].[All].[Net Income]}))}, {[Account].[Account].&[2000], [Account].[Account].&[1000]}))}, {[Account].[Account].&[2000], [Account].[Account].&[1000]})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS FROM [Budget] WHERE ([Measures].[Amount])

You must run the following query by using the MDX Sample Application, and then run your problem query:

SELECT measures.members on COLUMNS, Account.members on ROWS from budget

Now, the problem MDX query runs successfully, and the result set of the query is displayed as expected.



Steps to reproduce the behavior
 Create a local ROLAP cube with a parent-child dimension. To do this, follow these steps:  Start Microsoft Visual Basic 6.0. In the New Project dialog box, select Standard EXE, and then click Open. By default, a project named Project1 and a form named Form1 are created. Add a command button control to the Form1 form, and name it Command1. Right-click Command1, and then click Properties.</li> Set the following properties of Command1 as follows: <ul> Caption: Create Cube </li> (Name): CreateCube </li></ul> </li> In Project Explorer, right-click the Form1 form, and then click View Code.</li>  Copy and paste the following code in the Code Editor of the Form1 form: Dim cnCube Dim s As String Dim strProvider As String Dim strDataSource As String Dim strSourceDSN As String Dim strCreateCube As String Dim strInsertInto As String

Private Sub CreateCube_Click

strProvider = &quot;PROVIDER=MSOLAP.2&quot; strDataSource = &quot;DATA SOURCE=c:\budgetb.cub&quot; strSourceDSN = &quot;SOURCE_DSN=FoodMart 2000&quot; strCreateCube = &quot;CREATECUBE=CREATE CUBE Budget (DIMENSION [Account] DIMENSION_STRUCTURE PARENT_CHILD,LEVEL [All] TYPE ALL,LEVEL [Account],MEASURE [Amount] Function SUM Format 'Currency')&quot; strInsertInto = strInsertInto & &quot;INSERTINTO=INSERT INTO BUDGET ([account].Key,[Account].Parent,[Account].Name,[Measures].[Amount]) OPTIONS DEFER_DATA&quot; strInsertInto = strInsertInto & &quot; SELECT account.account_id,account.account_parent,account.account_Description,expense_fact.Amount From expense_fact, account where expense_fact.account_id =account.account_id&quot;

Set cnCube = CreateObject(&quot;ADODB.Connection&quot;) s = strProvider & &quot;;&quot; & strDataSource & &quot;;&quot; & strSourceDSN & &quot;;&quot; & strCreateCube & &quot;;&quot; & strInsertInto & &quot;;&quot;

Screen.MousePointer = vbHourglass cnCube.Open s MsgBox &quot;Cube Created&quot;, vbOKOnly, &quot;Success&quot;

End Sub </li> On the Run menu, click Start.</li> In Form1, click Create Cube.</li> In the Success message box, click OK.</li></ol> </li> Run an MDX query. To do this, follow these steps: <ol> Start the MDX Sample Application.</li> In the Connect dialog box, type C:\Budgetb.cub in the Server box, type MSOLAP in the Provider box, and then click OK.</li>  Copy the following query: SELECT NON EMPTY HIERARCHIZE(Except({AddCalculatedMembers(Except({AddCalculatedMembers(DrillDownMember({DrillDownLevel({[Account].[All]})}, {[Account].[All].[Net Income]}))}, {[Account].[Account].&[2000], [Account].[Account].&[1000]}))}, {[Account].[Account].&[2000], [Account].[Account].&[1000]})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS FROM [Budget] WHERE ([Measures].[Amount]) </li> On the Query menu, click Run.

You notice the behavior that is mentioned in the &quot;Symptoms&quot; section.</li></ol> </li></ol>

<div class="references_section">