Databases Reference
In-Depth Information
The Security Measure Group Approach
In this approach dimension security is modeled using a fact table. A relational
table will hold the access permissions of users for the dimension members. If
a user has permission for a specific location, that is indicated by a row con-
taining the username, the location, and another column containing a value 1,
which indicates the user has permissions to the location. A value of 0 indic-
ates that the user does not have permissions. Ah, something simple! Now
you really want to learn this approach, right?
The fact table containing the dimension security restrictions is added as a
measure group to the existing cube. The relational column that contains the
value of 0 or 1 is the measure that will be used for modeling dimension secur-
ity. An MDX expression using the measure from the security measure group
is used to restrict the dimension members to authorized users. Follow the
steps below to model the measure group approach for restricting access to
users.
1. Use the Analysis Services project you used in any of the approaches
discussed earlier and delete all existing roles. In the DSV designer,
right-click and select Add/Remove Tables. Select Table Security and
click OK. Mark the employeeid and locationid as key for the security
table and make appropriate joins to the dimension and employee
tables in the DSV.
2. Open the cube Dimension Security and click the Cube Structure tab.
Right-click the cube name in the Measures pane and select New
Measure Group. Select the security table from the DSV.
The Analysis Services cube designer automatically adds a new
measure group called security and creates two new measures, as
shown in
Figure 19-25
. The Analysis Services tools automatically
define the right relationships between the existing dimensions based
on the joins specified in the DSV. If you click on the Dimension
Usage tab you will see the details of the dimension types and the
granularity attributes. Deploy the new cube structure to your Analysis
Services instance.
Search WWH ::
Custom Search