Example 5—Using the Exists Function to Specify an Allowed Set
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 |
|
|
True |
True |
City |
True |
{San Jose, San Francisco} |
|
True |
True |
Gender |
False |
|
|
True |
False |
The results of these dimension security settings are the following:
- The allowed set that is specified for the City attribute limits the visibility of newly added members of the City attribute.
- Only the California member of the State attribute will be visible. (The allowed set for the City attribute only includes San Jose and San Francisco—both cities in California. This means that the allowed set not only limits the City attribute, but also limits the visibility of the State attribute to only California.)
- The Gender attribute is not visible because the IsAllowed property for this attribute is set to False.
- The aggregated totals for the State and All Level attributes include only those attribute members that are viewable by the database role (in this case, the totals for San Jose and San Francisco) because the VisualTotals property for both the City and State attributes are 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 |
|
|
|
8600 |
|
California |
|
|
8600 |
|
|
San Jose |
|
4200 |
|
|
San Francisco |
|
4400 |
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 6—Using the Exists and Except Functions to Specify Allowed and Denied Sets