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

This article was previously published under Q289804
This article has been archived. It is offered "as is" and will no longer be updated.
BUG #: 11617 (plato7x)
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 functionAn error occurred during attempt to get a cell value.
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 theMicrosoft Knowledge Base:
290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack
Manually remove the string "~New Cube^" from the Custom Rollup Formula.
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.

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.

Article ID: 289804 - Last Review: 10/23/2013 16:36:34 - Revision: 1.2

Microsoft SQL Server 2000 Analysis Services

  • kbnosurvey kbarchive kbbug kbfix kbssas800sp1fix KB289804