You often need to give OLAP cube users access to different kinds of data and restrict their access to other data. For example, in a sales organization, you might want each sales person to be able to access only the parts of the OLAP cube that correspond to his or her area of responsibility. Sales organizations aren't the only businesses that need this capability: Decision makers in various fields need access to database information to make informed decisions, but only part of that information might be appropriate for a particular user. For example, managers need to see the salaries of their employees to make budgeting decisions, but they don't need to see the salaries of their peers or their managers.
In "Security and Parameterization," December 2002, InstantDoc ID 27040, I introduced the idea of using dimension-level security in Analysis Services to create a separate flexible, scalable security solution that has only one security role. You might wonder why you need a special solution for security when Analysis Services has built-in dimension-level security and cell-level security. Why can't you just create a security role for each user, grant each user different access rights, and let Analysis Services take care of it? Two factors make this idea impractical. First, such a solution isn't maintainable. Analysis Manager isn't designed to handle hundreds or thousands of roles. Second, Analysis Services security isn't scalable. The whole system can bog down or fail altogether when you have more than about 30 concurrent roles.
In "Security and Parameterization," I described the member-property solution, for which you add a username member property to each dimension level you want to secure. You then create a custom dimension-level security role that checks this member property to determine whether the user has access to that member. The second approach, which I describe in this article, is to create a virtual cube with one underlying cube that contains the user-security information. This virtual-cube approach is more complicated to configure but has the advantage of greater flexibility. The member-property approach is easier to configure but is limited by the number of member combinations you can grant access to. Both approaches require you to create only one Analysis Services role.
Both approaches build the security information into the cube so that you can use a custom MDX security role to determine the current user's access rights. With the member-property approach, you can store a maximum of only 256 characters in a member property. The virtual-cube approach uses a separate fact table to store all the allowed combinations of usernames and dimension members. Because the number of records in a fact table is unlimited, you have the flexibility you need to define specific privileges for your users.
Figure 1 shows how to set up your relational tables to create the two underlying cubes for the virtual cube. You build the Security cube from the Security Facts table and build the Primary cube from the Primary Facts table. Both cubes share the dimension that you'll secure. In Figure 1, the secure dimension is the Customers dimension. The Primary cube can have many dimensions, but I use only one in this example—the Other dimension.
The Security cube has one measure, SFAccess. Each record in the Security Facts table contains one combination of a user and a customer. If the user is allowed to view the associated customer, the SFAccess field contains a 1. When you create the Security cube, you need to configure the SFAccess measure to be aggregated as a Max aggregation type. This configuration means that Analysis Services can verify a user's permissions to access any dimension member at an intermediate level of the Customers dimension; just as in a leaf level, if SFAccess is 1, the user has access. Because the SFAccess measure is aggregated, only the intermediate dimension members that have descendants with non-zero values will be non-zero themselves.
I tested this solution by using the FoodMart 2000 database. I created a new cube called Security, which included data from the FoodMart 2000 Customers dimension and my new dimension, Users. After I processed the Security cube in Analysis Manager, I created a virtual cube called Secure Sales. This virtual cube included the original FoodMart Sales cube and my new Security cube. The virtual cube included all the dimensions and measures of the Sales cube, the new Users dimension, and the Security cube's SFAccess measure. Before saving the virtual cube in the Analysis Manager Cube Editor, I changed the properties of the Users dimension and the SFAccess measure so that, in both, the Visibility property was FALSE. This value prevents users from seeing the security information when they browse the Secure Sales cube.
After I processed the Secure Sales cube, I used Analysis Manager to create a new security role. You can create a new role by right-clicking the database that contains the cube you want to secure and selecting Manage Roles. In the Manage Roles window, I created a new role called Secure Customer. On the Membership tab of the Create a Cube Role window, I added the usernames I wanted to have access. In my test, I included the test username RussWhitney and my usual login, rwhitney. Then, on the Dimensions tab, I found the Customers dimension and changed the Rule field from Unrestricted to Custom. This change let me click the ellipses (...) button to open the Custom Dimension Security window, where I selected the Advanced tab to enter an MDX expression for the Allowed Members. I used the expression
FILTER( [Customers]
.[NAME].Members,
( STRTOTUPLE( MID( USERNAME, INSTR(1,
USERNAME, "\") + 1, 128 ) ), [SFAccess] ) = 1 )
which filters out the customers for whom SFAccess isn't 1 for the currently logged-in user. I used the STRTOTUPLE() function to convert a string expression to a member—in this case, the member in the Users dimension that corresponds to the current USERNAME. Notice that I used a couple of VBScript functions—MID() and INSTR()—to determine the latter half of the USERNAME. Remember that USERNAME typically returns a string in the form DOMAIN\USER. The names in the Users dimension include only the second half of this string, so I removed the DOMAIN\ from this formula.