Using security hierarchy to filter data by user

Contents[Hide]

1. Overview

This walkthrough shows you how to filter data depending on the user by using security hierarchy.

Similar to the custom attributes, security hierarchies are typically used to implement user-dependent filtering (e.g., row-level security). 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 the data corresponding to the custom attribute value associated with the user's account.

The main difference between a security hierarchy-based data cube and a custom attribute-based data cube is that you can warehouse or memory-cache data when using security hierarchy.

Tip
Dundas BI provides built-in support for SaaS (software-as-a-service) and multi-tenant deployment scenarios. If this scenario applies to you, refer to the Using tenant data connector overrides article.

2. Data preparation

Log in as the admin user and create a new data cube using the Blank option.

Drag the AdventureWorks [Sales].[SalesOrderDetail] table from the Explore window to the canvas. A SQL Select and Process Result transform appear on the canvas.

Click the Process Result transform and then click the Data Preview window. In the ProductID column, you'll see a number of product ids such as:

Previewing data from the initial data cube
Previewing data from the initial data cube

3. Set the Security Hierarchy

This step shows you how to use a security hierarchy that references the custom attribute.

First, edit the data cube and on the Process Result dialog, click the Edit icon on the ProductID hierarchy.

Edit the ProductID hierarchy
Edit the ProductID hierarchy

Select the option Use As A Security Hierarchy option and select Product from the Custom Attribute dropdown.

Use the Security Hierarchy option and select the custom attribute
Use the Security Hierarchy option and select the custom attribute

Click Submit. The security hierarchy is now configured to filter based on the user's Product custom attribute value.

The security hierarchy is configured
The security hierarchy is configured

4. Assign custom attribute to a user

Typically, an administrator will assign the custom attribute to specific users and/or groups. But for the purposes of this walkthrough, the custom attribute will be added to the admin account for simplicity.

In the Administration screen, click Account Services to expand its items. Then click View Accounts to see the 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. 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, you'll see a list of available custom attributes that can be assigned to this user. Select the checkbox for the Product custom attribute.

Select the Product custom attribute token
Select the Product custom attribute token

Returning to the View custom attributes dialog, enter a value for the Product custom attribute and then click Submit. For example, if you're just using implicit hierarchy and you want to limit user to see only data for ProductID=707, then use a custom attribute value of 707.ProductID

Set the Product custom attribute value to 707.ProductID
Set the Product custom attribute value to 707.ProductID

5. Create a new dashboard

Create a new dashboard using the Blank template.

Go to the Explore window and locate the data cube. Drag the OrderQty measure to the canvas. It will appear as a table visualization.

Then drag the ProductID dimension to the ROWS field of the Data Binding Panel (or to the Row Header drop region of the table). 

Create a dashboard to show columns from the data cube
Create a dashboard to show columns from the data cube

Since you're logged on using the 'admin' account, and the custom attribute is assigned to the 'admin' account, you'll only see data specific to the custom attribute value.

Note: The Hide Hierarchy option in the previous step will prevent users from using the hierarchy in the Data Binding Panel. The data will still be filtered as per the custom attribute value, however, it will not be possible to drag and drop the hierarchy and be used in the 'ROWS', 'SLICERS', and 'COLUMNS' section of the Data Binding Panel.

6. View the dashboard

Edit the dashboard created earlier and switch to View mode. You'll see that the table visualization only shows records where the 2nd Level hierarchy is Bottles and Cages.

View the dashboard. Note that the filter will also show hierarchy values based on the Custom Attribute.
View the dashboard. Note that the filter will also show hierarchy values based on the Custom Attribute.

7. Using explicit hierarchy

You can use an explicit (or user-defined) hierarchy to work with a Security Hierarchy. The following are steps on how this can be done:

  1. Create a user-defined hierarchy. Using the hierarchy designer, drag and drop the Product table to the canvas. Since this table has relationship properly setup with the ProductCategory and ProductSubCategory tables, a 3-level hierarchy will be automatically created. If relationship between the tables is not configured, you need to create a custom user hierarchy.
  2. On the data cube, promote the ProductID dimension to a hierarchy.
  3. On the account's custom attributes, assign a custom attribute value of 28.B.ProductID.

    Set the Product custom attribute value to 28.B.ProductID
    Set the Product custom attribute value to 28.B.ProductID
    Log out of Dundas BI and log back in as the admin user in order for the custom attribute assignment to take effect.

    Custom attribute value of 28.B.ProductID is equivalent to the unique name of the hierarchy selection below:

    Equivalent hierarchy selection.
    Equivalent hierarchy selection.

    Tip: To get the member's unique name, you can refer to this article. You can also use the new security hierarchy filter instead of explicitly specifying the unique names.

  4. View the dashboard.

    Edit the dashboard created earlier and switch to View mode. You'll see that the table visualization only shows records where the 2nd Level hierarchy is Bottles and Cages.

    View the dashboard. Note that the filter will also show hierarchy values based on the <em>Custom Attribute.</em>
    View the dashboard. Note that the filter will also show hierarchy values based on the Custom Attribute.

8. Multi-value custom attributes

It is easy to modify the above example to use multi-value custom attributes instead.

First, go to the Admin screen and click Custom Attributes under Account Service.

Edit the Product custom attribute and select the Multi-Value checkbox option.

Edit the custom attribute and change it to multi-value
Edit the custom attribute and change it to multi-value

Next, edit the administrator account, scroll down, and click Custom attributes. Click Add value to add another value to the Product custom attribute. Then log out and log back on for the changes to take effect. For example, if you want to add access to the whole Bikes Product Category, add the value 1.A.ProductID.

Add 1.A.ProductID as another value to the custom attribute
Add 1.A.ProductID as another value to the custom attribute

Custom attribute values of 28.B.ProductID and 1.A.ProductID are equivalent to a hierarchy selection below:

Equivalent hierarchy selection.
Equivalent hierarchy selection.

View the dashboard and verify you can see both hierarchy levels assigned earlier.

Dashboard is filtered according to custom attribute's multi-values selection
Dashboard is filtered according to custom attribute's multi-values selection

9. 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