Using security hierarchy to filter SSAS data by user

Contents[Hide]

1. Overview

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.

Note
This walkthrough creates an OLAP security hierarchy. An alternative would be to create a data cube using an MDX select and follow the steps for using security hierarchy to filter data by user.

2. Data preparation

To follow along with this walkthrough, log on as an admin user and prepare the following:

  1. An SSAS data connector. The following examples use the Adventure Works 2014 OLAP database.
  2. 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.

    Create a secure custom attribute
    Create a secure custom attribute

3. Set a security hierarchy

From the Home page, click Explore data.

Click Explore data
Click Explore data

In the Explore panel, expand your SSAS data connector, right-click the Adventure Works OLAP cube, and select View Cube.

View the OLAP cube
View the OLAP 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.

Preview the metric set
Preview the metric set

From the toolbar, click Set Security Hierarchies.

Click Set Security Hierarchies
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.

Set the security hierarchy
Set the security hierarchy

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.

Click Custom attributes
Click Custom attributes

In the View Custom Attributes dialog, click Add custom attribute.

In the Select Custom Attributes dialog, select the checkbox for the Country custom attribute.

Select the custom attribute
Select the custom attribute

Click Submit.

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.

Select the OLAP cube
Select the OLAP cube

In the Edit Custom Attribute dialog, select Canada from the Country dropdown. The Value field will be automatically populated.

Select the value
Select the value

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.

Preview the metric set
Preview the metric set

6. See also

Dundas Data Visualization, Inc.
500-250 Ferrand Drive
Toronto, ON, Canada
M3C 3G8

North America: 1.800.463.1492
International: 1.416.467.5100

Dundas Support Hours: 7am-6pm, ET, Mon-Fri