Using custom attributes to filter data by user

Contents[Hide]

1. Overview

This walkthrough shows you how to filter data depending on the user by using custom attributes.

A custom attribute is a name-value pair of information that is created by an administrator and assigned to users or groups. Custom attributes 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.

2. Data preparation

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

Drag the AdventureWorks [Sales].[SalesTerritory] 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 Name column, you'll see a number of countries and territory names such as Canada, France, Germany, etc.

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

3. Create a new dashboard

Create a new dashboard using the Blank template.

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

Then drag the Name 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

In the Name column, you'll see a number of countries and territory names just like the data preview for the data cube.

4. Add a custom attribute

Go to the main menu and click Admin to see the Administration screen.

Click Account Service to expand its items and then click Custom Attributes.

In the Custom Attributes dialog, click Add custom attribute.

Click Add custom attribute
Click Add custom attribute

In the Add Custom Attribute dialog, set the name of the custom attribute to Country, and check that the currently selected data type is String.

Enter a name for the custom attribute
Enter a name for the custom attribute

Click Submit. You'll see a message indicating the custom attribute has been saved, and it will be listed in the Custom Attributes dialog.

You can view the newly added custom attribute by clicking its Edit button.

Click Edit to view the custom attribute
Click Edit to view the custom attribute

5. 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 Country custom attribute.

Select the Country custom attribute token
Select the Country custom attribute token

Returning to the View custom attributes dialog, enter a value for the COUNTRY custom attribute (i.e., Canada) and then click Submit.

Set the Country custom attribute value to Canada
Set the Country custom attribute value to Canada

Log out of Dundas BI and log back in as the admin user in order for the custom attribute assignment to take effect.

6. Filter the data cube

This step shows you how to configure a filter for the data cube that references the custom attribute.

First, edit the data cube again and insert a Filter transform.

Right click on the Filter transform and click Configure.

In the Filter dialog, edit the Name column. 

In the Transform Element dialog, set the Element Operator to Equals. Then open the token menu for the Value dropdown and select the Country custom attribute token.

Filter transform configuration
Filter transform configuration

Click Submit and then close the Filter dialog. The filter transform is now configured to filter based on the Country custom attribute value.

Click the Process Result transform and then click the Data Preview window. You'll see that the data has been filtered to show only records where the Territory Name is Canada. Recall that this is the value of the custom attribute that is assigned to the admin user.

Preview the filtered data cube
Preview the filtered data cube

7. 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 Name is Canada.

View the final dashboard
View the final dashboard

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 Country 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

Multi-value option is selected
Multi-value option is selected

Next, edit the administrator account, scroll down, and click Custom attributes. Click Add value to add another value to the Country custom attribute. Then log out and log back on for the changes to take effect.

Add France as another value to the custom attribute
Add France as another value to the custom attribute

Edit the data cube and delete the Filter transform. Filtering will be added directly to the SQL Select transform instead as follows.

Double-click the SQL Select transform to open its configuration dialog. Scroll down and click Define parameters. In the Transform Parameters dialog, click Add parameter.

Configure the SQL Select transform
Configure the SQL Select transform

Select the Output Element Filter option and set the Connector Element dropdown to the Name column. Then click Create parameter.

Set the output element filter
Set the output element filter

In the Define Transform Parameter dialog, set the Value Type to Collection. Then open the Value token menu and select the Country custom attribute token.

Select the Country custom attribute token
Select the Country custom attribute token

View the dashboard and verify you can see two rows corresponding to the custom attribute values assigned earlier.

Dashboard is filtered according to custom attribute multi-values
Dashboard is filtered according to custom attribute multi-values

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