- Dimensions with Ragged Hierarchies
- Virtual Cubes
- Dimension Level Security
- Advanced Dimension Properties
- Optimizing Cube Schemas
- Summary
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Dimension Level Security
Analysis Services implements security through cube roles, which allow you to group Windows 2000/2003 users according to their data access needs. As long as you have a few roles, managing security is fairly simple; security is implemented the same way for regular or virtual cubes. Let’s see how you would go about securing certain levels within a dimension.
Suppose you want to create a role within the sales cube that cannot view the sales of alcoholic beverages—all other products are fine. Navigate to sales cube within the foodmart 2000 database that comes with MSAS 2000, right-click Cube Roles, choose Manage Roles, and click the New button within the Cube Role Manager dialog box. This opens another dialog box that lets you specify the new roles properties. For this example, let’s call the role No Alcoholic Beverages and click the Dimensions tab which looks like what appears in Figure 4.
)
Figure 4 Creating a new role called No Alcohol" in Analysis Manager.
Notice that "measures" is treated as just like any other dimension, so you could restrict your role from viewing certain measures (for instance, unit sales or store profit) much like you would restrict members of another dimension.
You can see that the Rule column shows all dimensions unrestricted; this means that users who belong to the No Alcohol role can view all members within each dimension. Because we want to secure the product dimension, click the Rule column next to Product and change the setting to Custom. The Custom Settings column displays an ellipsis as shown in Figure 5.
)
Figure 5 Custom rule for Product dimension.
Clicking the ellipsis button opens the Custom Dimension Security dialog box, which has three tabs: Basic, Advanced, and Common. The Basic tab let’s you pick the top and bottom levels within the dimension that will be displayed to the members of the current role. In addition, you can pick and choose individual members within the dimension who will be available to the role. By default, all dimension members on all levels are visible. Let’s expand the all level, expand the Drink product family, and uncheck Alcoholic Beverages, as shown in Figure 6.
)
Figure 6 Uncheck Alcoholic Beverages under the Drink category.
Press OK and add a member to this role. As I mentioned, MSAS roles can only have Windows users as members. For this example, you can make default group of "everyone" as the only member of the role. At this point, you are taken back to the Manage Roles dialog box where you can click "the Test Role" button to check out the role permissions you just defined. You’ll notice that Drink family won’t contain alcoholic beverages (see Figure 7).
)
Figure 7 Alcoholic beverage sales are hidden from the current user's view.
So far so good; but take a look at the Drink Total line: Beverage sales equal to 13573, dairy sales are 4186, and the total is...24597. Not exactly the number we expected. What’s worse, if you leave the cube like this, your users will be sure to know you’re hiding something from them and might not be very happy. Fortunately, you can easily fix this problem by going to the Common tab of the Custom Dimension Security dialog box and changing the Visual Totals setting. As the dialog informs you, the visual totals determine whether displayed aggregate values are calculated based on only the viewable members. Check the Enable—Show Visual Totals option as shown in Figure 8.
)
Figure 8 Visual Totals option available on the Common tab.
After saving the visual totals setting, the calculations appear to be valid, even while alcoholic beverages are hidden from the "No Alcohol" role (see Figure 9).
)
Figure 9 Correct total displayed for Drink category.
If you’re curious, the Advanced tab of the Dimension Security dialog box let’s you write MDX formulas for allowed/denied members as well as top/bottom levels of the dimension you want to allow your role to see. For example, the following settings would make Product Family the top level and Brand Name the bottom level, and disallow the viewing of certain brands under the Non-consumable department (see Figure 10).
)
Figure 10 The Advanced tab of the Dimension Security dialog box.
With these settings, the role would see the data within the cube shown in Figure 11.
)
Figure 11 Certain brands are hidden from users.
Notice that the All Products level is no longer visible; neither are Household and Health and Hygiene members under the Non-Consumable department.