Share via


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