Databases Reference
In-Depth Information
("Access"),
USERNAME))
This expression filters the list of cities that can be accessed by the current
user. Member property Access for each city is checked to see if the current
user has access using the VBA function Instr. The expression Loca-
tion.City.currentmember.properties ("Access") returns a string that contains
the login names of all the users who have access to the current city. The user
name function returns the string containing the login name of the current user.
The Instr VBA function searches for the occurrence of the current user in the
member property. If there is a match, the Instr function returns a positive num-
ber and the Filter expression uses that as an indication the condition is true,
and therefore the current city can be accessed by the current user. Thus the
Filter function is used to retrieve a set of cities that can be viewed by the cur-
rent user. The Analysis Services project modeling the business problem using
the Member property approach is provided with
Chapter 19
and is called Di-
mensionSecuritywithMemberProperties. You can test the preceding expres-
sion by deploying the current role to the Analysis Services instance and then
browsing the cube using a specific user as shown in
Figure 19-23
.
The member property approach is one of the three dynamic security ap-
proaches recommended in this chapter. This is easy to implement and the
cost of maintenance (updates of permissions to users in the relational table)
is typically low because only a single column gets updated for security
changes. One of the advantages of the Member Property approach over the
previous approaches is that you have a single role to mainatain. However, the
important trade-off in this approach is that whenever you change the security
restrictions for the users, the dimension needs to be processed to reflect the
changes in the database, thereby restricting the right dimension members.
Based on your business requirements you can enable proactive caching on
the dimension so that the dimension is processed automatically without inter-
vention from an admin. If your dimension has a large number of members
and if you need security changes to be in effect immediately, you might have
performance implications because Analysis Services would have to use the
dimension in ROLAP mode till the time MOLAP cache gets updated. Based
on the size of the dimension members and your business need, you can
choose to implement this approach.
Search WWH ::
Custom Search