Using custom attributes to filter data by user

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.

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 Analysis Panel (or to the Row Header drop region of the table). 

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.

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.

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.

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

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.

Returning to the View custom attributes dialog, edit the Country attribute and enter a value (e.g. Canada) and then click Submit.

Log off Dundas BI and log back on as the admin user 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.

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.

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.

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.

Next, edit the administrator account, scroll down, and click Custom attributes. Edit the Country custom attribute. 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.

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.

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

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.

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

9. Notes

• Select the Secure option to prevent the custom attribute from being sent over the network. This will keep the information on the server and the REST API will not return it.
• The custom attribute value set for the user overrides the value inherited from a group. If there is no value for the user, multiple values inherited from groups will result in a conflict error.

10. See also

• Add an account
• Add a new group
• Using a data cube to join tables
• Using dockable windows in the dashboard designer
• Using SSAS Roles Impersonation
• Using custom data and custom attributes to filter SSAS data
• Using security hierarchy to filter data by user
• Using tenant data connector overrides