FIX: Virtual Cube Returns Empty Cells if Custom Rule Is Applied

Article translations Article translations
Article ID: 295706 - View products that this article applies to.
This article was previously published under Q295706
This article has been archived. It is offered "as is" and will no longer be updated.
BUG #: 12147 (Plato7.x)
Expand all | Collapse all

On This Page


A custom rule is used to control access to dimension members by users in a role. If you use a custom rule for dimension security in a virtual cube, the virtual cube may return empty cell values in the lower level of a dimension.


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


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. Create a role named "TestRole".
  2. On the Membership tab, add a user.
  3. On the Dimensions tab, select the Store dimension, change the rule from Unrestricted to Custom, and then click the ellipsis (...) button on the Custom Settings column.
  4. In the Custom Dimension Security: Store dialog box, select Deselect all members, expand All Store -> USA, select CA, and then click OK.
  5. Create a virtual cube using the Sales cube.
  6. Select Unit Sales for the virtual cube measure.
  7. Use the >> button to select all dimensions, and then use the < button to clear everyone except Customer Pattern, Store, and Customers.
  8. Save the virtual cube as Test1 and then process it.
  9. Expand Test1, right-click Cube Roles and select Manage Roles.
  10. Enable TestRole, and then click Test Role.
  11. In the Cube Browser, drag the Store dimension to the data window, and expand All Store -> USA -> CA. Note that the values for the members "Alameda", "Beverly Hills", "Los Angeles", "San Diego", and "San Francisco" are all empty. Except for "Alameda", all members should have some values.
In step 7, if you use the > button to select the dimensions for the virtual cube, you do not see the described problem.


Article ID: 295706 - Last Review: October 23, 2013 - Revision: 1.2
  • Microsoft SQL Server 2000 Analysis Services
kbnosurvey kbarchive kbbug kbfix kbssas800sp1fix KB295706

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from