Using security hierarchy to filter SSAS data by user
This walkthrough shows you how to filter SSAS data depending on the user by using security hierarchy.
Security hierarchies are typically used to implement user-dependent filtering, similar to using custom data and custom attributes to filter SSAS data and using SSAS roles impersonation. For example, when a user is viewing a dashboard, the data appearing in charts and tables can be filtered so that the user only sees data corresponding to the custom attribute value associated with the user's account.
Unlike the other two methods for user-dependent filtering of SSAS data, security hierarchies do not require any additional actions on the SSAS side. This method allows you to manage data security using just the Dundas BI admin interface.
2. Data preparation
To follow along with this walkthrough, log on as an admin user and prepare the following:
- An SSAS data connector. The following examples use the Adventure Works 2014 OLAP database.
- A custom attribute. The following examples use a secure Country custom attribute, similar to the one in Section 4 of using custom attributes to filter data by user but with the Secure option selected.
3. Set a security hierarchy
From the Home page, click Explore data.
In the Explore panel, expand your SSAS data connector, right-click the Adventure Works OLAP cube, and select View Cube.
You can preview the metric set before applying a security hierarchy. Expand Dimensions, Customer, and Location, and then drag the Country hierarchy to the Rows field of the Data Analysis Panel.
From the toolbar, click Set Security Hierarchies.
In the Set Security Hierarchies dialog, expand Customer, expand Location, and select Country. Then, scroll down and select Country from the Custom Attribute dropdown.
The Country hierarchy now has a green checkmark indicating a security hierarchy has been set.
4. Assign custom attribute to a user
Typically, an administrator will assign the custom attribute to specific users or groups. To simplify this walkthrough, the custom attribute is assigned to the admin account.
In the Administration screen, click Account Service and then click Accounts to see a list of users in the system.
Click the Edit button for the admin (System Administrator) user. In the Account Details dialog for the System Administrator, scroll to the bottom and click the Custom attributes button.
In the View Custom Attributes dialog, click Add custom attribute.
In the Select Custom Attributes dialog, select the checkbox for the Country custom attribute.
Country has been added to the list of custom attributes. Click Edit.
In the Edit Custom Attribute dialog, click Select an OLAP or data cube, select the Adventure Works OLAP cube, and click Submit.
In the Edit Custom Attribute dialog, select Canada from the Country dropdown. The Value field will be automatically populated.
Click Submit on both dialogs.
Log off and then log back on for the changes to take effect.
5. Preview the metric set
From the Home page, click the Adventure Works OLAP cube from the Recently Viewed panel to go back to the View OLAP Cube area.
Drag the Country hierarchy to Rows in the Data Analysis Panel. You will see only Canada in the preview.