Using a security hierarchy to filter SSAS data by user
1. Overview
This walkthrough shows you how to filter data from SQL Server Analysis Services (SSAS) or other OLAP databases differently for each user by using a Dundas BI security hierarchy with custom attributes.
Security hierarchies are typically used to implement user-dependent filtering or row-level security, 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 can be set up within Dundas BI and do not require additional actions to be taken within the database itself.
2. Example preparation
To follow along with this walkthrough, log on as a member of the System Administrators group and prepare the following:
- A data connector to a SQL Server Analysis Services database. The following examples use the Adventure Works DW sample database.
- A custom attribute. The following examples use a Country custom attribute like the one created in Using custom attributes to filter data by user.
To view the data before it's filtered, find the data connector to your OLAP database in the Explore window when you have another file open such as a dashboard. Expand the data connector to list its cubes, then right-click one and choose View Cube.
In our example shown below, we can expand Dimensions, Customer, and Location, and then drag the Country hierarchy to Rows on the Data Analysis Panel, then view the result.
3. Set the security hierarchy
Access the View Cube option as described above, then you can choose Set Security Hierarchies in the toolbar. (Alternatively, find and expand the data connector in an Explore window, right-click one of its cubes, and choose Set Security Hierarchies.)
In the Set Security Hierarchies dialog, select which hierarchy's values should be filtered differently for each user. In this example, expand Customer and Location, and select Country.
At the bottom of the dialog, choose which Custom Attribute will be populated with a value for each user (Country in this example).
The Country hierarchy now has a green checkmark indicating a security hierarchy has been set.
4. Assign custom attribute values
Typically, an administrator will assign the custom attribute to other users or groups. You should assign an appropriate value to each account or group that needs to access this cube's data.
In the Admin screen of Dundas BI, expand Account Service and then click Accounts to see a list of users in the system. (To assign a custom attribute value to a group of users, click Groups instead.)
Click the Edit button for an account or group to assign it a custom attribute value. To simplify this walkthrough, we will edit the System Administrator user.
In the Account Details dialog, 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 custom attribute (e.g., Country) and click the submit button at the bottom.
Click the Edit button for the new custom attribute. In the Edit Custom Attribute dialog, click Select an OLAP or data cube, select your cube, then click Submit.
In the Edit Custom Attribute dialog, select a security hierarchy value from the dropdown to populate the Value field above it.
Click Submit on both dialogs.
Log off and then log back on for the changes to take effect.
5. Preview the metric set
After logging back on, find and expand the data connector again and choose View Cube.
For our example, drag the Country hierarchy to Rows in the Data Analysis Panel. You will see data filtered by the custom attribute value for the current account in the preview.
