Example 6—Using the Exists and Except Functions to Specify Allowed and Denied Sets

In this example, a database role has the dimension security settings that are shown in the following table.

Attribute	IsAllowed	AllowedSet	DeniedSet	ApplyDenied	VisualTotals
State	True		EXCEPT (State..Members, {California})	False	True
City	True	EXISTS (City..Members, {California} + {Seattle})		False	False
Gender	False			True	False

The results of these dimension security settings are the following:

• The denied set for the State attribute specifies that only the California member can be viewed by the database role. All other existing members and any newly added members of the State attribute will not be visible.

• Members in attribute hierarchies that include a member of the State attribute remain visible, even though the State attribute has a denied set. The members in other attribute hierarchies remain visible because the ApplyDenied property for the State and City attributes are set to False.

• The city of Seattle is visible because it is in the allowed set for the City attribute.

• The aggregated totals for the All Level and State attributes include only the values for members that are visible because the VisualTotals property for both these attributes is set to True.

Reviewing the Result Set

Based on these dimension security settings for the role (and based on cube access to all cells), the following result set is returned from the cube when all members are queried.

All Level	State	City	Gender	Sales Amount
All Offices				17400
	California			12900
		San Jose		4200
		San Francisco		4400
		Los Angeles		4300
	Washington			4500
		Seattle		4500

See Also

Tasks

Granting Custom Access to Dimension Data

Concepts

Example 1—Explicitly Specifying an Allowed Set

Example 2—Explicitly Specifying a Denied Set

Example 3—Using the Except Function to Exempt Members from a Denied Set

Example 4—Using the Exists Function to Exempt Members from a Denied Set

Example 5—Using the Exists Function to Specify an Allowed Set