How Data Dimension Security Affects Data
Recall that we configured the allowed set on the Country-Region attribute hierarchy,
but we used the Reseller Name hierarchy on the report (note that both hierarchies
belong to the Reseller dimension). Dimension data security has filtered the
resellers in Australia even though we haven't set up a filter on the Reseller
Name attribute hierarchy. It turns out that behind the scenes, the server applies
a special behavior called Auto-exists that cross-joins attribute hierarchies.
Understanding Autoexists. Thanks to Autoexists, when attribute
hierarchies from the same dimension are requested side by side, the server automatically
cross-joins their members and returns only the members that exist in both hierarchies
(i.e., the intersecting members). Because the Basic role can see only the Australia
member in the Country-Region attribute hierarchy, the Reseller Name column shows
only the Australian resellers. Autoexists is applied to all attribute hierarchies
within the same dimension. For example, if you request the Bank Name attribute
instead of Reseller Name on the report, only banks for resellers in Australia
will be returned.
Autoexists shouldn't be confused with the MDX NON EMPTY behavior. Autoexists
is applied at the attribute level for all attribute hierarchies within the same
dimension and can't be turned off. NON EMPTY simply filters out members that
have empty cells from the query results and is entirely optional. For example,
you can turn off NON EMPTY in Cube Browser by clicking Show Empty Cells on the
toolbar. To see the difference between NON EMPTY and Autoexists in your report,
click the drop-down arrow in the Reseller Name column header and note that only
the Australian resellers are shown.
Data security. As I mentioned at the beginning of this article,
dimension data security secures dimension members and the data associated with
them. From an end-user perspective, members that the user isn't authorized to
see and their data simply don't exist in the cube. Imagine that a global WHERE
clause is applied that finds only the data that's associated with allowed members
of all secured dimensions. For this reason, when using dimension data security
on a cube, avoid hard-coding dimension members in any cube scripts or MDX queries.
If you hard-code a member that a user isn't allowed to see, the user will get
an error when she runs the script or query.
An interesting question for your business users is what totals should the user
see when he browses the cube by another dimension? Should the totals exclude
the data contributed by the members the user isn't authorized to see, or not?
SSAS supports both scenarios. For better performance, the server includes the
disallowed members when calculating the aggregated totals (i.e., the All
member totals are used). For example, the report in Figure
4 shows the same results irrespective of the user's role rights.
If your business requirements dictate that the totals should reflect the contributions
by the allowed members only, you need to enable a special server behavior called
Visual Total. To do so, go back to the Advanced tab on the Dimension Data tab
in Role Designer, and select the Enable Visual Total check box for the Country-Region
attribute hierarchy you secured and deploy. After you reconnect in Cube Browser,
the totals in Figure 4 will decrease
to show Australian sales only.
Cross-dimension security. In fact, the report in Figure
4 will show only fiscal year 2004 (because apparently Australians bought
AdventureWorks bikes in 2004 only). That's because (as noted above), the default
NON EMPTY behavior filters out the empty members in the Time dimension (click
Show Empty Cells on the toolbar to see all the years).
But shouldn't Autoexists propagate to all dimensions? Certainly there could
be scenarios in which cross-dimension security would be desirable. For example,
if you have Customer and Account dimensions that have a logical one-to-many
relationship (i.e., one customer can have many accounts), it's reasonable to
expect that if a user is allowed to see only a subset of customers, she should
see only the accounts that belong to that subset of customers; she shouldn't
be able to see other customers' accounts. Cross-dimension security could also
yield performance benefits (e.g., an OLAP browser wouldn't have to load all
the accounts of a large Account dimension).
(Remember that by cross-dimension security, I mean preventing access
to members, not their associated data. If a user doesn't have access to a dimension
member, dimension data security prevents access to the data associated with
a member without any extra work.)
As it turns out, Autoexists is not applied across dimensions, and there's nothing
you can do to enable it. You might be tempted to try a workaround that simply
cross-joins dimensions together to flow the security context from one to the
other, as in:
Exists ([Date].[Fiscal Year].
[Fiscal Year].Members,
[Reseller].[Reseller].
[Reseller].Members)
Here, the MDX Exists function cross-joins all members of the Fiscal Year and
Reseller attribute hierarchies. Because you've already defined an allowed set
on the Reseller dimension, you might expect that this statement would return
only years in which Australian resellers have sales (2004, in this case). Unfortunately,
the statement doesn't work as expected, because when the dimension security
expressions are evaluated, they're evaluated before the security filters are
applied. For more information about the event execution order, see "Default
members, MDX Scripts, Security, KPIs and Perspectives" (listed in the Related
Resources box).
The easy workaround for cross-dimension Autoexists is to apply a separate
security filter on the Account dimension. It would be nice if a future release
of Analysis Services would support cross-dimension Autoexists to simplify cross-dimension
security.