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

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:

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.