Securing Your Data in Analysis Services - Professional SQL Server Analysis Services 2005 with MDX - page 992

whose data access permissions change periodically is called dynamic dimen-
sion security.
The Member Property Approach
One of the ways to provide access to locations for employees is to have a
column in the relational data source that contains the list of employees who
have access to that location. When you need to modify user access to a loca-
tion, you can either restrict them or provide access by updating the list of
users who have access in the relational column. You might be wondering how
this translates into defining the security in Analysis Services dynamically —
when a list in the relational data source must be maintained. Actually, it is
quite simple with the help of an MDX expression.
First, you need to make sure the relational column in the dimension table is
added as an attribute hierarchy in the dimension. You do not necessarily need
to browse this attribute, but you need to make this attribute a member prop-
erty for the attribute hierarchies for which you need to apply dimension secur-
ity. Create or open an existing relational table called "Location" from within
SQL Server Management Studio. Just as you changed domain names in the
Employee table, replace all instances of the name domain\username in the
column Access to your local machine name\username. If you have created
your own Analysis Services project from the relational data source, you will
have the Access attribute in the Location dimension. If you are working with
the sample project Dimension Security, you will notice that the column access
does not exist in the DSV. Go to the DSV designer, right-click and select Re-
fresh. You will now see the prompt that a new column has been added to the
table. Click OK to add the column to the DSV. In the dimension editor for
Location dimension, drag and drop the column access to the attributes pane
to create an attribute hierarchy. Specify the access attribute as a member
property for the remaining attribute hierarchies Area, City, State, and Country
as shown in Figure 19-24 . By default the access attribute is a member prop-
erty of the key attribute "Location." If you do not want your users to browse
this attribute, you can disable the attribute hierarchy Access by changing its
AttributeHierarchyEnabled property to false.