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 (884395)

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 "#ERR" is displayed in the result set.

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.

MORE INFORMATION

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.
Set the following properties of Command1 as follows:
- Caption: Create Cube
- (Name): CreateCube
In Project Explorer, right-click the Form1 form, and then click View Code.

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 = "PROVIDER=MSOLAP.2"
strDataSource = "DATA SOURCE=c:\budgetb.cub"
strSourceDSN = "SOURCE_DSN=FoodMart 2000"
strCreateCube = "CREATECUBE=CREATE CUBE Budget (DIMENSION [Account] DIMENSION_STRUCTURE PARENT_CHILD,LEVEL [All] TYPE ALL,LEVEL [Account],MEASURE [Amount] Function SUM Format 'Currency')"
strInsertInto = strInsertInto & "INSERTINTO=INSERT INTO BUDGET ([account].Key,[Account].Parent,[Account].Name,[Measures].[Amount]) OPTIONS DEFER_DATA"
strInsertInto = strInsertInto & " 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"

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

Screen.MousePointer = vbHourglass
cnCube.Open s
MsgBox "Cube Created", vbOKOnly, "Success"

End Sub

On the Run menu, click Start.
In Form1, click Create Cube.
In the Success message box, click OK.

Run an MDX query. To do this, follow these steps:

Start the MDX Sample Application.
In the Connect dialog box, type C:\Budgetb.cub in the Server box, type MSOLAP in the Provider box, and then click OK.

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])

On the Query menu, click Run.

You notice the behavior that is mentioned in the "Symptoms" section.

REFERENCES

For more information, see the following topics in SQL Server Books Online:

Building Local Cubes
Parent-Child Dimensions
Write Useful and Complex Queries With MDX

For additional information, click the following article numbers to view the articles in the Microsoft Knowledge Base:

313269 FIX: When you run an MDX query on a write-enabled cube, the query returns #Err, or the query fails and you receive an "Unable to open cellset..." error message in SQL Server 2000 Analysis Services

315970 FIX: #ERR occurs when you browse a cube that is write-enabled and has a virtual dimension and an Oracle data source

810440 FIX: MDX queries return "#Err" as the result set when you enable "Visual Totals" on private parent-child dimension