Advanced Data Permissions Use Cases

Data Permissions (Also known as Mandatory User Filters) allow workspace developers to filter content that is delivered to individual users. For more information, read Data Permissions.

As a user, you can also use advanced OVER … TO clause to specify the data access by data itself. You can limit the data that each user can see from a single mapping dataset. If the mapping changes, you do not have to update existing data permissions; you just add new data permission objects for new users. In addition, the OVER…TO syntax can reduce the number and complexity of data permissions in your logical data model.

Use Case: data permissions in M:N relationship models

Suppose you have a workspace that tracks performance of athletes across events. Since an athlete can appear in multiple events and a single event can have multiple athletes, it is a typical M:N mapping.

The simplified data model contains four fact tables, one of which is a many-to-many relation:

Suppose that you want to control data access based on the unique row identifiers in the Athletes Events M:N dataset.

There are two paths that end at the Athletes M:N dataset:

• First path: Athlete > Athletes Events M:N
• Second path: Sponsor > Event > Athletes Events M:N

Here’s how you can filter data on the three datasets along the second path by using only one data permission object per user.

Configure data mappings

We will add a mapping dataset called User Mapping to our example data model. This dataset allows a single user to be mapped to any number of records in the Athletes Events M:N dataset. This is possible because each record in the Athletes Events M:N contains a unique identifier to the combination of identifiers from the Athlete dataset and from the Event dataset:

The User Mapping dataset must be connected to the other datasets through the Athletes Events M:N dataset, so that all other datasets in the model are parents of the new dataset and are therefore on the same path.

The following table shows the content of the User Mapping dataset:

Create the data permission expression

We will use the User Mapping dataset to filter all parent datasets along the same path:

Sponsor → Event → Athletes Events M:N → User Mapping

This can be done by the following data permission expression:

([URI_1] = [URI_2]) OVER [URI_3] TO [URI_4]

To be more specific:

([User_Attribute_URI] = [User_Attribute_Value]) OVER [Mapping_ID_Column] TO [Parent_Attribute_In_Hierarchy]

• URI_1 is `User ID` field (connection point of User dataset)
• URI_2 is specific user (attribute element id)
• URI_3 is the connection point of the User Access dataset
• URI_4 is the connection point of Sponsor (top parent in the hierarchy)

Here’s an example of the entire data permission expression in our test workspace (the parentheses are required):

([/gdc/md/vcs6tmt5prilun25l5chz9q0qc5rpd2x/obj/368475]=[/gdc/md/vcs6tmt5prilun25l5chz9q0qc5rpd2x/obj/368475/elements?id=3004]) OVER [/gdc/md/vcs6tmt5prilun25l5chz9q0qc5rpd2x/obj/368479] TO [/gdc/md/vcs6tmt5prilun25l5chz9q0qc5rpd2x/obj/7061]

To demonstrate what will happen, let’s populate the Athletes Events M:N dataset with some records:

As a result, based on the mapping defined in the User Mapping dataset, User 1 (one@users.com) can only see data associated with records acceaafcabbe4959bb13e1d73b2f99a9 and 4a52e256a68b4c3893c10c2961ba6ccd. This means that a report with an attribute from the Athletes Events M:N dataset will show only data for those 2 records.

It works in the same way for both the Event and the Sponsor datasets. In the first case, only records A1 and A2 will be shown. In the latter case, only records associated with A1 and A2 will be shown. In other words, the values are propagated from child dataset to parent datasets (from the User Mapping dataset to the Sponsor dataset).

Assign the data permissions to user

The last step is to assign the data permissions to the user. For details, see Data Permissions.

Other considerations

It is important to note that this data permission does not filter data in the Athlete dataset because it is not on the data path defined in the data permissions. If you created a report with an attribute (from the Athlete dataset) and without any facts, all the data would be viewable to all users. To filter the Athlete dataset as well, you may use one of the following options:

• Create a new data permission with the OVER … TO clause (for the path Athlete → Athletes Events M:N → User Mapping)
• Include the data from the Athlete dataset in the Athletes Events M:N dataset