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 based on a particular data cube, the data that appears can be automatically filtered so that the user only sees the data corresponding to the custom attribute value associated with their account.

Note
Data cubes with parameter values that vary by user cannot use warehouse or in-memory storage. You can use security hierarchies instead if you want to use cube storage options.

2. Example preparation

Log in as a member of System Administrators and create a new data cube using the Blank option.

For this example, drag the [Sales].[SalesTerritory] table from an Adventure Works data connector in 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

Create a new dashboard using the Blank template.

In the Explore window, locate the data cube and drag its SalesYTD measure to the canvas. It will appear as a table visualization.

Then drag the Name dimension onto the area labeled Row Header over 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.

3. Add a custom attribute

Go to the main menu and click Admin to see the Administration screen. Expand Account Service and click Custom Attributes.

In the Custom Attributes dialog, click Add custom attribute.

Add a custom attribute
Add a custom attribute

In the Add Custom Attribute dialog, set the Name (e.g., Country) and check that the Currently Selected Data Type matches the type of values you will be filtering by. This is String in our example.

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

Click Submit.

4. Assign custom attribute values

Typically, an administrator will assign the custom attribute values for other users and/or groups. You should assign a value for each user or group that needs to access the data from the data cube that will be filtered.

In the Administration screen, expand Account Services and click Accounts. (To assign a value to a group of users, click Groups instead.)

Click the Edit button for the user or group. For this example, we will edit System Administrator. Click the Custom attributes button.

Edit custom attribute values
Edit custom attribute values

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

In the Select Custom Attributes dialog, select the checkbox for the custom attribute that will be used for filtering (e.g., Country).

Select the custom attribute
Select the custom attribute

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

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

Log off Dundas BI and log back on as this user for the custom attribute assignment to take effect.

5. Filter the data cube

A transform such as a SQL Select or Filter transform can be configured to filter the cube's data by the custom attribute values.

For example, edit the data cube, click to select a link between two transforms and insert a Filter transform from the toolbar.

Right-click the Filter transform and click Configure.

In the Filter dialog, click the Edit button next to the column to filter by, which is Name in our example. 

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 custom attribute values.

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

Preview the filtered data cube
Preview the filtered data cube

6. View the dashboard

View the dashboard created earlier to see that the table visualization only shows records where the Name is Canada.

View the final dashboard
View the final dashboard

7. Multi-value custom attributes

The example above can be modified to use multi-value custom attributes instead, which allow you to choose multiple filter values per user.

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

Edit the Country custom attribute and select the Multi-Value checkbox option. (This option cannot be de-selected later.)

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. Edit the Country custom attribute, and click Add value to add another value to the Country custom attribute. 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 to define a parameter on the SQL Select transform instead. (Alternatively, configure the Filter transform to remove the filtering applied earlier, and then define a parameter on it using the steps below.)

In the transform's configuration dialog, scroll down and click Define parameters. In the Transform Parameters dialog, click Add parameter.

Define a parameter
Define a parameter

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

Filter the Name values
Filter the Name values

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 multiple values
Dashboard is filtered according to multiple values

8. Notes

  • Select the Server Only option to prevent the custom attribute value from being sent to the user's web browser, in case it contains sensitive information or large amounts of data.
  • 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.

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