FIX: Dimension Wizard Generates Unusable Custom Rollup Formula with Private Parent-Child Dimension (289804)


The information in this article applies to:

  • Microsoft SQL Server 2000 Analysis Services
This article was previously published under Q289804
BUG #: 11617 (plato7x)

SYMPTOMS

If you use the Dimension Wizard to create a private Parent-Child dimension with the Data members are hidden option selected, the Custom Rollup Formula generated by the wizard is: 
CalculationPassValue([~New Cube^DimensionName].CurrentMember.DataMember, 0)
If you attempt to browse the data after processing the cube, #ERR is seen. If you click #ERR, the following error message displays:
Unable to display cell properties. Formula error - cannot find dimension member ("[~New Cube^DimensionName]") - in a name binding function An error occurred during attempt to get a cell value.

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

Manually remove the string "~New Cube^" from the Custom Rollup Formula.

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

Steps to Reproduce Behavior

  1. Open the Analysis Manager.
  2. Open the Foodmart 2000 database.
  3. Create a new cube by using the Cube wizard.
  4. Select Sales_fact_1997 as the fact table, and then select Unit Sales as the measure.
  5. Click New Dimension to create a new private parent-child dimension.
  6. In the Select The Dimension Table dialog box, click Employee, and then click Next.
  7. To define the child column, next to the "Member" key, select employee_id. To define the parent column, next to the "Parent" key, select supervisor_id. To define the "Member name" column, next to "Member" name, select full_name. Click Next.
  8. Select Members with data in the Select Advanced Options dialog box. Click Next.
  9. In the Set Members with Data Property dialog box, select Nonleaf members have associated data, and then select Data members are hidden. Click Next.
  10. Type Private Employee Dim for the dimension name. Clear the Share this dimension with other cubes check box, and then click Finish.
  11. Type Parent_child_cube for the cube name. Click Finish to advance to the Cube editor.
  12. Highlight the dimension level Employee_ID under Private Employee Dim.
  13. Click the Advance tab under Properties to see the Custom Rollup Formula.
  14. Build the cube into a MOLAP cube, and then browse the data.
Modification Type:MinorLast Reviewed:4/24/2003
Keywords:kbBug kbfix kbssas800sp1fix KB289804 kbAudDeveloper
©2002 Microsoft Corporation. All rights reserved.