Security and Parameterization

Analysis applications frequently contain sensitive, confidential information that should be available only to appropriate decision makers. And often, you must limit the information available to a particular decision maker to the area of the business that that person is responsible for. For example, if an analysis application contains company sales data about all sales representatives, customers, and products, you might limit sales representatives to viewing only their own sales data. Regional sales managers might be limited to seeing only sales data for their regions and possibly summarized information for other regions.

In SQL Server 2000 Analysis Services, Microsoft introduced dimension-level security, which can limit the members of a cube dimension that a user can view. The most straightforward way to use this feature is to create a security role for each unique set of permissions in the application. But in a sales application, every user might need a unique set of permissions for the sales data. This requirement could introduce hundreds—if not thousands—of security roles. However, even if you could create an administrative application to manage this number of security roles, Analysis Services couldn't handle it. You can generally assume that a server using Analysis Services can handle 30 to 50 roles in concurrent use. Server memory and the size of your cubes limit the maximum number of roles, but this range gives you an idea of how limited you are with Analysis Services.

After you recover from your shock at the limited number of roles, please continue reading. I have a way to work around this limitation and create a scalable solution. The scalable approach is to build a list of users and their privileges into the cube, then use a custom MDX security role to determine which users can access each dimension. You have two ways to build the username information into the cube. One approach is to add a username member property to each level of the dimension you want to secure. The second approach is to create a virtual cube that has one underlying cube containing the user security information. The member-property approach is easier to configure, but you're restricted by a limit on the number of users to whom you can grant access to any given dimension member. The virtual-cube approach is more complicated to configure but has the advantage of greater flexibility. What makes both solutions work is their use of a custom MDX expression in a dimension-security role. Let's look at the member-property approach and walk through the steps you use to implement it. In next month's column, I'll describe the virtual-cube approach.

When you use the member-property approach to build username information into a cube, you create a member property on each dimension member in the dimension you want to restrict. The member property contains a list of usernames that have access to that member. The member-property approach is limited—you can include a maximum of only 256 characters in a member property. This means you can include a limited number of unique users who can access a dimension member, particularly if your usernames are long. However, in some applications, this limitation isn't serious because the application includes a one-to-one relationship between the dimension members and the users who have access. So in the company sales data example, you probably want the sales representatives to have access to only their own data, and regional sales representatives can have access to the data of regional groups of sales representatives. The hierarchy of the sales representative dimension matches the hierarchy of the sales organization.

To implement the member-property approach to dimension security, you use the following steps to modify the FoodMart 2000 database. These steps add dimension security to the Customers dimension.

Step 1. Add a username column to the customer dimension table for each level that exists in the Customers dimension (Country, State Province, City, and Name). You can find the customer dimension table in the foodmart 2000.mdb file, which is usually installed in the C:Program FilesMicrosoft Analysis ServicesSamples folder. Use Microsoft Access to open foodmart 2000.mdb, select the customer table, and enter design mode. Then, add four new text fields: username, city username, state username, and country username. Figure 1 shows how your screen looks as you enter the first of these four fields.

Step 2. Type the username values for all the cubes into the customer table. For FoodMart 2000, you can copy the lname (last name) field to the new username field. You use the other username fields to determine which users can access the intermediate levels of the dimension. For example, the Country level of the Customers dimension has just three values—Canada, Mexico, and USA—and for each value, you need a username in the country username field. You set the country username field for all Canadian records to a specific username, and set a different username for each of the other two countries. So, only one user can access each country's data.

Step 3. Open Analysis Manager and expand the tree view in the left pane to reveal the shared dimensions in the FoodMart 2000 database. Right-click the Customers dimension, and select Edit to enter the Dimension Editor. Expand the tree view in the left pane to show the Member properties folder for each dimension level. Then right-click each Member properties folder, and select New Member Property. In the next dialog box, you select the corresponding field in the customer dimension table. For example, you should get the member property for the Name level in the Customers dimension from the username field in the customer table. Be sure to give all the new member properties the same name: Uname.

Step 4. Reprocess the FoodMart 2000 Sales cube to incorporate the new member properties into the cube. Right-click the FoodMart 2000 database and select Process the Database.

Step 5. Finally, create a security role that uses the new member properties. Right-click the Database Roles folder in Analysis Manager, and select Manage Roles. In the Database Role Manager dialog box, click New, and select the Dimensions tab. Change the rule for the Customers dimension to Custom, and click the ellipses (...) to access the corresponding custom rule. In the Custom Dimension Security dialog box that Figure 2 shows, select the Advanced tab. Finally, in the Allowed Members box, type an MDX expression that determines which members a user can view. Note that to test the security role, you need to make sure that the username you're giving permissions to isn't in the OLAP Administrators group. If it is, all security rules are ignored.

I purposely included a simple MDX expression in Figure 2 to make the example easier to read. But you might notice a flaw in the way I set up this security. If you give a user access at an intermediate level in a dimension such as City or State Province, she might not have access at a lower level. You can solve this problem in one of two ways: You can add the usernames of the intermediate-level member properties to descendant member properties, or you can change the MDX so that it checks access to dimension members above the current level. The MDX code for the first approach is less complicated. But I recommend the second approach because of the limited space in a member property; changing the MDX doesn't add usernames to the leaf-level members that have access to intermediate levels. The code in Listing 1 shows how you can write the MDX expression for the second approach. This MDX checks the Uname member property of the current customer member and the Uname member property of each ancestor as many as four levels up the tree to determine whether any member property contains the current username. If so, the MDX expression evaluates to true. Note that after you click OK in the Custom Dimension Security dialog box, you might receive the error message Formula Error—cannot convert expression to Set—in an function; Microsoft says not to worry about this error, but be sure to test the security after you create the new role.

Next time, I'll show you how to create a virtual cube to determine access rights. With a virtual cube, you have more than one fact table, so you can use one fact table to store information about which usernames have access to which dimension members. This approach is flexible because you can give any username access to any combination of member names.

Financial analysis, human resources, anti-fraud, health care, and education applications are just a few examples of analytic applications in which dimension-level security is crucial. On the surface, Analysis Services' limited number of roles might seem daunting, but you can easily work around the problem. I expect that future releases of Analysis Services will address this limitation, but in the meantime, I hope you find this approach useful. If you've created your own workaround for the problem, please send it to me. I'd love to share it with other SQL Server Magazine readers.