Customizing Dimension Security

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 DOMAINUSER. The names in the Users dimension include only the second half of this string, so I removed the DOMAIN from this formula.

You might also notice that the MDX filters only the leaf-level members of the Customers dimension. Although filtering all members in the dimension would seem appropriate, I couldn't get that kind of expression to work properly. The Microsoft representative I spoke to suggested that I filter only the leaf-level members because the end result would be the same: Only the intermediate members that lead to the remaining leaf-level members would be available.

After you type your MDX expression into the Custom Dimension Security window, click OK to save the security role. You might receive an error message from Analysis Manager that says it doesn't recognize the measure. Click OK and continue—the role will be saved correctly. In the Database Role Manager, click the ellipses button that's below the heading Cubes & Mining Models. You can then select which cube in the database to apply this security role to.

You can also enter security roles by right-clicking a cube and selecting Manage Roles, but sometimes the role isn't correctly updated at the database level if you do this. When you're working with custom security roles, I recommend that you change the roles in the Database Role Manager, then apply them to the appropriate cubes.

Implementing a security system such as the one I describe here can be frustrating because making small mistakes in a formula or in the configuration can cause non-intuitive or misleading error messages. Worse, you might get no error message—the security just doesn't work as you expect. I highly recommend that you verify your MDX expressions by using the MDX sample program or another OLAP front-end tool before you apply them to a security role in Analysis Manager. You'll get more informative error messages, and you'll have the added benefit of being able to easily experiment with parts of the formula until you get it to work correctly.

In my eyes, security is a necessary evil. Security always adds administration complexity and frequently makes your application harder for end users to work with. The member-property and virtual-cube solutions I've offered can help you reduce that complexity without degrading ease of use. These schemes aren't without headaches, but they do let you work around the maintainability and scalability limitations of Analysis Services 2000.